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.

Posted in Performance tuning
4 comments on “plan_table changes
  1. mdinh says:

    Another difference from 10g is that plan_table is a real table rather than a global temporary table.

    Don’t you mean that 10g plan_table is global temporary and not real table?

  2. Thanks, poor choice of wording on my part. Have now changed it to “Another difference from 10g: in 9i and below, plan_table is a real table rather than a global temporary table.” which I hope is clearer.

  3. Todd says:

    Thanks Andrew – I did not realize that 10g auto-created the plan table. I just went through and dropped all of our redundant copies across instances.

  4. SUNIL CHAUDHARI says:

    I want to see results in table so i m performing following steps
    after clicking on View–>Options–>data grids -visual it shows me message as follows

    ‘ The following files specified on the executables tab could not be found
    TNS PING
    Ping. ‘

    i m using toad 7.3.0.0

Leave a Reply

Your email address will not be published. Required fields are marked *

*