How do I generate an explain plan for a long running SQL process?
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.
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.
Note: You can also compare Explain Plans. Review Toad Help for to Compare Explain Plans for more information..
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
Tip: You can also select View | Toad Options.
Use the following options to...
Enter the schema name to use when writing out and fetching Explain Plan data.
Default: Window logon name
Enter the table name to use when saving Explain Plan results.
Save 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.