Toad World Blog

Configuring an Oracle Database to have a 'self-cleaning' PLAN_TABLE

Nov 1, 2012 12:00:00 AM by Steve Hilker

On the database server, go to $ORACLE_HOME/rdbms/admin

copy utlxplan.sql to utlxplan.sql.original - you don't want to lose the original Oracle script.

Edit utlxplan.sql and change it to the following :

...
CREATE GLOBAL TEMPORARY TABLE PLAN_TABLE (
  ...
  ...
) ON COMMIT PRESERVE ROWS;
...

Save the new version and run sqlplus as SYS or a SYSDBA user, then :

  • run the utlxplan.sql script as normal
  • create a public synonym
  • grant 'ALL' access to public, or a few chosen users.
SQL> start ?/rdbms/admin/utlxplan
Table created.

SQL> create public synonym PLAN_TABLE for sys.plan_table;
Synonym created.

SQL> grant all on sys.plan_table to public;
Grant succeeded.

Now when developers create explain plans, the rows will remain in the table until they logoff, whereupon they will automagically delete themselves. No more checking that PLAN_TABLE is clean and that you have enough free space in whichever tablespace - it all lives in the temporary tablespace now. You won't find a tablespace_name in XXX_SEGMENTS or XXX_TABLES for it, but the TEMPORARY flag is set to 'Y' in XXX_TABLES.

SQL> select table_name,tablespace_name,temporary
2  from user_tables
3  where table_name = 'PLAN_TABLE';

TABLE_NAME                     TABLESPACE_NAME                T
------------------------------ ------------------------------ -
PLAN_TABLE                                                    Y

1 row selected.

Note: you need to configure TOAD to use a plan table called PLAN_TABLE if you want to have the self-cleaning abilities from within TOAD as well as developers using SQL*Plus, for example, and running EXPLAIN PLAN FOR ... statements.


Cheers,   Norm [TeamT]

Tags: Toad for Oracle Wiki

Steve Hilker

Written by Steve Hilker

Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.

In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.