Quest SQL Optimizer gives me the error message "Problematic SQL." When I try to tune my statement or even after I have already tuned the statement. What does the error mean?
Quest SQL Optimizer will take your SQL statement and analyze it before it optimizes it. When it analyzes it, it looks to see if the statement has common issues and according to what issues it finds, it'll classify the statement as either Simple, Complex, or Problematic.
These classifications are just labels and does not reflect the quality of the statement. In other words, it is entirely possible for the best possible SQL statement to be a problematic one. It is possible there are no better SQL statements than the problematic one.
According to the help file, under default settings:
Problematic SQL:
- Number of table scan operations greater than (Default = 3)
- Read-only field indicating the number of table scan operations referenced in the execution plan. If the total number of table scan operations is greater than this value then this SQL statement is classified as Problematic. This value is the same as the upper limit of the complex table scan operations range.
- With full table scan
Simple SQL:
- Number of table scan operations less than (Default = 2)
- Read-only field indicating the number of table scan operations references in the execution plan. If the total number of table scan operation is less than this value, then this SQL statement is classified as Simple. This value is the same as the lower limit of the Complex table scan operations range.
Complex SQL:
- Number of table scan operations (Default = 2 /3, Range 2 to 99) - Specify the number of table scan operations referenced in the execution plan for Complex SQL statements.
- Include SYS.DUAL table - Specify to include the SYS.DUAL table when counting the number of tables referenced by the SQL statement.
- With Full Index Scan - Specify whether SQL statements with full index scans are classified as Complex SQL statements.
