Andrew Fraser DBA

plan_table changes

In versions 10g, plan_table exists as a public synonym of the global temporary table sys.plan_table$. That is all created automatically at database create time and when a database is upgraded to 10g (creation is in catplan.sql, which is called from catproc.sql and u*.sql scripts in $ORACLE_HOME/rdbms/admin).

In versions 9i and below, plan_table is not created automatically. It has to be created manually by running script utlxplan.sql in $ORACLE_HOME/rdbms/admin. That should be done both at creation time and after each upgrade (since plan_table structure is changed/improved with each new release).

Another difference from 10g: in 9i and below, plan_table is a real table rather than a global temporary table.

It is possible to allow each user to have their own personal plan_table, but I think it is better to mimic the 10g behaviour and have a single central plan_table used by all users. To do that, run SQL like:

conn / as sysdba
@?/rdbms/admin/utlxplan
grant select, insert, update, delete on sys.plan_table to public ;
create public synonym plan_table for sys.plan_table ;

One thing to watch is that copies of old plan_table’s can be left lying around in users schemas. That applies even to 10g, because a users’ table will be accessed in preference to a public synonym of the same name.

It’s not just upgrades that can leave those in place – export/import can bring users’ old plan_table’s across as well. Old versions of plan_table tend to work without reporting an error, but do not generate as much detail as new versions, so should be avoided.

Utlxplan.sql is still shipped with 10g, but there is no reason to continue using it at that version.

For extra confusion, TOAD by default will attempt to use its own version of a plan table. See previous post for details.