How to Make Sure You’re Using the Correct Oracle PLAN_TABLE

This article will discuss the importance of the PLAN_TABLE and how to make sure you are using the correct one.

The PLAN_TABLE, shown below, is used to produce SQL Explain Plans.  When SQL is submitted to Oracle and processed (details to follow in other articles), an execution plan is created and used to process the SQL.  This execution plan contains the tables/indexes and other objects being accessed by the SQL and the order in which they will be processed.

 

Figure 1

All tools, including SQL*Plus, populates the PLAN_TABLE table with explain plan information then uses this content to produce an explain plan.

The explain plan visualizes the execution plan.

Prior to Oracle10, the plan table was created via a script.  Everyone needed to have their own PLAN_TABLE table.  The problem with this method is when a newer Oracle was installed, most developers just did an export and import of their test objects to be moved to the new environment.  This method of moving also grabbed the existing PLAN_TABLE.

Using an older plan table than the current rev of the database really isn’t a big problem.  Oracle handles the syntax but Oracle frequently updates the PLAN_TABLE to contain more useful information.  When the PLAN_TABLE does not match the rev of the database it is to be associated with, you might be missing some useful information.

The script was found in the Oracle_Home/RDBMS_Admin folder and was named xplan.sql.  This had to be run to get the current PLAN_TABLE.

Oracle10 introduced the virtual table PLAN_TABLE$ owned by SYS.  This table eliminates the need for the xplan.sql script to be run and also eliminates the possibilities of the wrong plan table being used at SQL tuning time.

For Oracle10 users:

1. Make sure to drop the PLAN_TABLE in your schema
2. Ask the DBA to make a public synonym for PLAN_TABLE for the PLAN_TABLE$.  This will allow any tool to then use the correct plan table

For Oracle11+ users:

1. Just make sure you drop the PLAN_TABLE from your schema if it still exists
2. Oracle Corp now creates the public synonym.

Here is a simple SQL statement, using tables A, B, and C that I’ll use in these Logical Read articles.  I used the ‘auto trace’ feature of Oracle (using character-mode SQL*Plus).

Figure 2

This is an old version of SQL*Plus and I like to use it for demonstration purposes.

The above illustration shows how to set SQL*Plus to create an explain plan after executing a SQL statement and to make the lines wide enough to hold the output explain plan information.

Figure 3

This illustration shows the A, B, C example being executed, returning 1 row, then an explain plan is produced by the SQL*Plus autotrace feature.

In future articles, I’ll discuss how to read this explain plan.  Oracle9+ has been illustrating explain plans using the procedure DBMS_XPLAN.Display.  This syntax gives us the where clause statements and where they apply in the execution plan.  This information is key to SQL tuning and Oracle nicely displays it now.

Continue reading this article series to learn more about the hard parsing process.