Old plan_table

When using “explain plan” with “dbms_xplan.display()”, have you ever seen the following note at the end of the output:

Note
-----
   - 'PLAN_TABLE' is old version

It’s something I see surprisingly frequently, travelling as I do to many different sites, but it’s usually easy to deal with.

In 10g [corrected from 9i, see comment #1] Oracle got rid of the need for users to create a plan_table in their own schema by creating a global temporary table in the SYS  schema, creating a public synonym for it, and then granting all necessary privileges to PUBLIC. One of the nice side effects of this strategy is that every session gets its own private copy of the plan_table in its temporary tablespace, so concurrent sessions don’t interfere with each other and the content automatically disappears when the session ends. This table is created in the script catplan.sql – even though the old script utlxplan.sql still does things the old way.

Unfortunately, some systems probably had private versions of the plan_table lurking in separate schema when they upgraded to 10g; other systems may have had an implementation standard that included a directive to create a schema-level plan_table (using the old utlxplan.sql script) Then there’s a really irritating issue that is the one I see most frequently.

Some third party GUI tools have their own interface for generating and displaying execution plans. But all they’re doing (usually) is making calls to Oracle’s own “explain plan” and then querying the plan_table. Unfortunately some of these tools seem to query  user_tables or all_tables to see if the appropriate table exists – and then create one in the current schema if they can’t find one. But if it’s an older version of the GUI it’s likely to create an older version of the plan_table … hence the warning the next time an older version of a DBA comes in to investigate some execution plans the GUI-free way.

All you need to do (probably) is execute the statement:  drop table plan_table;

If you want to check that this is the solution, you could run a simple query:

select
        owner, object_type, object_name
from
        all_objects
where
        object_name like 'PLAN_TABLE%'
/

OWNER        OBJECT_TYPE              OBJECT_NAME
------------ ------------------------ ------------------------
SYS          TABLE                    PLAN_TABLE$
PUBLIC       SYNONYM                  PLAN_TABLE
SYSTEM       TABLE                    PLAN_TABLE
SH           TABLE                    PLAN_TABLE$

The things you’re looking for are sys.plan_table$ and the public synonym plan_table.

The example above comes from a 9i database that had been upgraded from 8i, so it still has a copy of an older version of plan_table in the SYSTEM schema, and it looks like someone may have run the script $ORACLE_HOME/rdbms/admin/utlxplan.sql from the SH schema – so I ’ll want to check that the public synonym does point to sys.plan_table$, rather than sh.plan_table$ before taking further action.

Of course, you don’t drop an object from a production database without getting the appropriate approval – and if that’s going to take some time you can always work around the problem by employing a rarely used feature of “explain plan”:

explain plan into sys.plan_table$
for
{your SQL statement}
;

select  *
from    table(dbms_xplan.display('sys.plan_table$'))
;

I’ve lost count of the number of times I’ve told people that the predicate section of an execution plan is vital to understanding what’s really going on, and it’s usually the predicate section that’s missing if you have an old plan_table. So it’s worth fixing the problem – and making sure it’s a permanent fix.