-
Title
Can an explain plan table be specified for use with ‘Auto Optimize SQL’? "ORA 00905: missing keyword" given when using Auto Optimizer. -
Description
How do you specify the explain plan table when using the embedded ‘Auto Optimize SQL’ feature in Toad's Editor?
When trying to run ‘Auto Optimize SQL’, an error is given:
"ORA 00905: missing keyword"
and/or
"ORA-00904: Invalid Identifier"
The SQL statement being optimized is valid and can run. Explain plan can be run on this statement also. It just does not run with ‘Auto Optimize SQL’.
-
Cause
Currently the explain plan table cannot be specified. It will attempt to create a temporary plan table based off the current schema. If the user does not have permissions to create this temp table, this error could occur.
-
Resolution
RESOLUTION 1:SQL Optimizer creates a temporary plan table based off the current schema. If the user does not have permissions to create this temp table, this error could occur. Please ensure your schema has the privilege to create temp tables.RESOLUTION 2:Verify that the schema has access to 'DBMS_LOB' and 'DBMS_XPLAN' objects.RESOLUTION 3:In Toad for Oracle, this is located in Toad Options | Oracle | General | “Explain Plan”. Choose a Plan Table when Toad prompts for one OR click the ellipses button to search/browse for a Plan Table. Select a Plan Table that exists in the database and ensure your user have access to it.The ‘Auto Optimize SQL’ will work after that.In SQL Optimizer, specify a plan table via Options | General, and ensure your schema has access to that plan table.
WORKAROUND:
A Toad Xpert license includes Quest SQL Optimizer for Oracle. Please launch the full Optimizer and optimize the SQL statement there.
Within Optimizer, go to Options | General and specify the explain plan there. Optimize a single SQL statement under the "Optimize SQL" tab.
STATUS:
Enhancement request ST75713 has been submitted to Development for consideration in future release of Toad for Oracle.
-
Defect ID
ST75713