‘PLAN_TABLE’ is old version

When using explain plan and dbms_xplan.display() or @?/rdbms/admin/utlxpls.sql, you may find these valuable information are missing:

  1. estimated execution time of each step
  2. estimated temp space needed
  3. predication section of execution plan
  4. at the end, appearing with a warning message: “PLAN_TABLE is old version”

e.g instead of showing


guang@db11r2>  @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 103157781

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    14 |    69   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| QUEUE_T |     1 |    14 |    69   (2)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - filter("REC_ID"=100)

it shows


idle> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     4 |    12 |     2 |
|   1 |  TABLE ACCESS FULL   | QUEUE_T      |     4 |    12 |     2 |
--------------------------------------------------------------------

'PLAN_TABLE' is old version

Since 9i, Oracle create a global temporary table SYS.PLAN_TABLE$, and a public synonym PLAN_TABLE and grant necessary privileges to PUBLIC.

This problem can be caused by a user still using a old version of PLAN_TABLE in a local schema (e.g. the database has been migrated from previous release). Using this query to check which plan_table is using by users


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
SH                              TABLE               PLAN_TABLE

This problem can be overcome by

log on as user who has a local plan_table, drop the plan_table and run @?/rdbms/admin/utlxplan.sql as sysdba.

Or

explain plan into SYS.PLAN_TABLE$ for <your_stmt>

set lines 120 pages 100

select * from table(dbms_xplan.display(‘SYS.PLAN_TABLE$’));

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: