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.

Figure 3.41. TOAD explain plans.

Ctrl+E also runs and displays an explain plan.

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).

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.

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.