-
Title
How do I generate an explain plan for a long running SQL process? -
Description
How do I generate an explain plan for a long running SQL process?
-
Resolution
- Place the cursor in a statement and click Explain Plan icon on the Execute toolbar (CTRL+E). Results display in the Explain Plan tab below the Editor.
Note: Toad prompts you to create the plan table, if you have not already. The Explain Plan table should be created in the Toad schema or an individual user schema, not both.
- To change the content and format of the Explain Plan:
If you want to...
Complete the following:
Select a different display format (tree, text, graphical)
Right-click the Explain Plan and select Display Mode. Select the display format you want to view.
Note: The Explain Plan tree display lists the contents of the plan table for the given statement ID. The query used to retrieve information for this tree is based on the one given by Oracle in utlxplp.sql. See Oracle's documentation for more information.
Add or remove information
Right-click the Explain Plan and select Adjust Content. For example, you can hide or display information (such as the cardinality) or select information to display as a column. Columns display to the left of the plan.
This option is only available in the tree display.
View the plan's object usage
Right-click the Explain Plan and select Object Usage.
View single record
Right-click the Explain Plan and then select Single Record View. Review Toad Help to View a Single Record for more information.
- Save the Explain Plan, if necessary. Review Toad Help for the following for additional information:
- View Previous Explain Plans
- Save Explain Plans
Note: You can also compare Explain Plans. Review Toad Help for to Compare Explain Plans for more information..
Additional Information:
Troubleshooting
Toad expects to find an Explain Plan table with columns to match the most recent specification from Oracle. If you get "Invalid Column" errors when executing Explain Plan, you can drop the Explain Plan table in the Schema Browser and then recreate it the next time you generate an Explain Plan (Toad prompts you to create the table if it does not exist).
Toad uses the Windows user name plus the date and time to generate a unique statement ID for the Explain Plan. If the user has a longer than normal user name, you may need to expand the Statement_ID column of the plan table.
General Oracle Options
To set general Oracle options
- Click Toad Options icon (Double checks) on the standard toolbar.
Tip: You can also select View | Toad Options.
- Select Oracle | General.
- Set the options. Review the following for additional information:
Explain Plan
Use the following options to...
Schema
Enter the schema name to use when writing out and fetching Explain Plan data.
Default: Window logon name
Table
Enter the table name to use when saving Explain Plan results.
Default: TOAD_PLAN_TABLESave previous Explain Plan results
Select to save the Explain Plan results in the Toad tables. Toad tables are required for this option. To view the results, select Database | Optimize | Explain Plan.
Default: Cleared