In 10g, Oracle removed 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.
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).
Let's see an example of this.
conn schema1/schema1
SQL> set autotrace on
SQL> select * from dual;
D
-
X
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
-- We are connected as SCHEMA1 and we get the "plan_table is old" message. Let's query the objects that are named as
-- PLAN_TABLE%.
SQL> select
2 owner, object_type, object_name
3 from
4 all_objects
5 where
6 object_name like 'PLAN_TABLE%'
7 /
OWNER OBJECT_TYPE OBJECT_NAME
------------------------------ ------------------- ------------------------------
SYS TABLE PLAN_TABLE$
PUBLIC SYNONYM PLAN_TABLE
SCHEMA1 TABLE PLAN_TABLE
-- Notice above that SCHEMA1 schema owns a PLAN_TABLE. This is the older version of the plan_table and was created probably at
-- migration, import or created using utlxplan.sql. This table needs to removed so that the explain plan uses the new
-- temporary PLAN_TABLE$ via the public PLAN_TABLE synonym.
-- WARNING: Don't drop the PLAN_TABLE$ nor the PLAN_TABLE public synonym, these need to exist for the new PLAN_TABLE infrastructure
-- to work properly.
SQL> drop table fnitp.plan_table;
Table dropped.
SQL> set autotrace on
SQL> select * from dual;
D
-
X
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
-- Here you go, the explain plan is now using the new PLAN_TABLE and you no longuer see the "plan_table is old" message.
|