- Overview
- Predefined Shortcuts
- User-Defined Shortcuts
- Using Variables
- Table and Column Name Select Lists
- Using Aliases and Autoreplacement Substitutions
- SQL Templates
- Code Completion Templates
- Executing SQL Statements
- Executing SQL Scripts
- Editing Result-Set Data
- Saving Result-Set Data
- Printing Result-Set Data
- Examining Explain Plans
- Examining Basic Performance Info
- Using Auto Trace
- SQL*Plus Compatibility
- Scripts That Write Scripts
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Examining Explain Plans
TOAD allows you to easily see the explain plan for the currently executed SQL statement. This is visualized on the Explain Plan tab in the results panel. Figure 3.41 illustrates a rather simple explain plan.
Ctrl+E also runs and displays an explain plan.
NOTE
It is beyond the scope of this book to provide a basic explanation of explain plans and the various features of the rule- and cost-based Oracle optimizers. TOAD does support changing the Optimizer Mode by right-clicking on the SQL statement and selecting the Optimizer Mode option. Cost-based hints can easily be added by using the SQL Templates option (discussed earlier in this chapter).
)
Figure
3.41 TOAD explain plans.
Problem Solving the Explain Plan Tab
If you get the error ORA-02404 Specified Plan Table Not Found as shown in Figure 3.42, you have two options.
){kind=small}
Figure
3.42 TOAD Explain Plan Not Found error.
The problem is that the TOADPREP.SQL script has not been run as described in Chapter 1, or the TOAD explain plan table has been renamed.
The solution is to run the script TOADPREP.SQL as defined in Chapter 1 and this, among other things, builds the TOAD_PLAN_TABLE. If you choose to use the Oracle RDBMS plan table (found in <Oracle Home>\RDBMS\admin) in file UTLXPLAN.SQL (this file is executed for each schema owner), click the TOAD Options button, and under the Oracle category, change the Explain Plan Table name to "PLAN_TABLE", removing the "TOAD_" from the beginning, as illustrated in Figure 3.43. This will allow TOAD to find the explain plan table for your schema.
)
Figure
3.43 TOAD Explain Plan Definition window.