Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

  • Prashant Dixit is the 'FatDBA' ...
  • Follow me on Twitter

Do you really require physical PLAN_TABLE in your database ?

Posted by FatDBA on May 9, 2022

Recently during one of the performance taskforce on a newly migrated system, customer DBA asked me to use one of their legacy tool to get more idea about database’s performance, that one of their expert DBA written to collect performance metrics. I’d seen their previous reports collected through the same tool for other systems, and it was good. But, got a runtime exception with an error while calling the script/tool which says ‘PLAN_TABLE physical table present in user schema SYS‘. The error means the user executing it (SYS) owns the table PLAN_TABLE that is the not the Oracle seeded GTT (Global Temporary Table) plan table owned by SYS (PLAN_TABLE$ table with a PUBLIC synonym PLAN_TABLE).

This was little odd to the customer DBAs as they had never experienced this error with the tool, and now when its there, question was Shall we drop the PLAN_TABLE ? Is it risky to do that ? If we drop it, will it impact the execution plan generation or not ? Any other associated risk with drop of plan_table ?

Next when I’d queried DBA_OBJECTS, I saw the table is there in SYS schema, though this system was migrated from 12.2 to 19c, but the table should not be there as the table only by default existed in older versions of Oracle. The object creation date was coming for the time when database was upgraded. It had appeared that someone after upgrade/migration, called the utlrp.sql explicitly (maybe any old 8i/9i DBA) and that’d created the table. Now the question is – It’s safe to drop this table ?

SQL> select owner, object_name, object_type, created from dba_objects where object_name like '%PLAN_TABLE%' 
and owner not in ('SQLTXPLAIN','SQLTXADMIN') ORDER BY 1;

OWNER      OBJECT_NAME          OBJECT_TYPE             CREATED
---------- -------------------- ----------------------- ---------
PUBLIC     PLAN_TABLE           SYNONYM                 17-APR-19
PUBLIC     SQL_PLAN_TABLE_TYPE  SYNONYM                 17-APR-19
PUBLIC     PLAN_TABLE_OBJECT    SYNONYM                 17-APR-19
SYS        SQL_PLAN_TABLE_TYPE  TYPE                    17-APR-19
SYS        PLAN_TABLE           TABLE                   13-MAR-22     ----->>>>> OLD PLAN_TABLE created during the UPGRADE 
SYS        SQL_PLAN_TABLE_TYPE  TYPE                    17-APR-19
SYS        PLAN_TABLE_OBJECT    TYPE                    17-APR-19
SYS        PLAN_TABLE$          TABLE                   17-APR-19
SYS        PLAN_TABLE_OBJECT    TYPE BODY               17-APR-19

9 rows selected.





-- Look at the difference between the two, PLAN_TABLE$ is a GLOBAL TEMP TABLE and old PLAN_TABLE is not.
SQL> SELECT TABLE_NAME, owner, temporary from dba_tables where table_name like '%PLAN_TABLE%' 
AND owner not in ('SQLTXPLAIN','SQLTXADMIN') ORDER BY 1;

TABLE_NAME                     OWNER                T
------------------------------ -------------------- -
PLAN_TABLE                     SYS                  N
PLAN_TABLE$                    SYS                  Y     ---> Y represents GTT


Let’s first see what’s there inside the PLAN_TABLE and what’s its purpose. Will generate few SQL execution plans will observe changes that happens in PLAN_TABLE.

-- Table columns and details
SQL> desc plan_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATEMENT_ID                                       VARCHAR2(30)
 PLAN_ID                                            NUMBER
 TIMESTAMP                                          DATE
 REMARKS                                            VARCHAR2(4000)
 OPERATION                                          VARCHAR2(30)
 OPTIONS                                            VARCHAR2(255)
 OBJECT_NODE                                        VARCHAR2(128)
 OBJECT_OWNER                                       VARCHAR2(128)
 OBJECT_NAME                                        VARCHAR2(128)
 OBJECT_ALIAS                                       VARCHAR2(261)
 OBJECT_INSTANCE                                    NUMBER(38)
 OBJECT_TYPE                                        VARCHAR2(30)
 OPTIMIZER                                          VARCHAR2(255)
 SEARCH_COLUMNS                                     NUMBER
 ID                                                 NUMBER(38)
 PARENT_ID                                          NUMBER(38)
 DEPTH                                              NUMBER(38)
 POSITION                                           NUMBER(38)
 COST                                               NUMBER(38)
 CARDINALITY                                        NUMBER(38)
 BYTES                                              NUMBER(38)
 OTHER_TAG                                          VARCHAR2(255)
 PARTITION_START                                    VARCHAR2(255)
 PARTITION_STOP                                     VARCHAR2(255)
 PARTITION_ID                                       NUMBER(38)
 OTHER                                              LONG
 DISTRIBUTION                                       VARCHAR2(30)
 CPU_COST                                           NUMBER(38)
 IO_COST                                            NUMBER(38)
 TEMP_SPACE                                         NUMBER(38)
 ACCESS_PREDICATES                                  VARCHAR2(4000)
 FILTER_PREDICATES                                  VARCHAR2(4000)
 PROJECTION                                         VARCHAR2(4000)
 TIME                                               NUMBER(38)
 QBLOCK_NAME                                        VARCHAR2(128)
 OTHER_XML                                          CLOB



-- Let me check other stats or details about the PLAN_TABLE
SQL> select index_name, table_name from dba_indexes where table_name='PLAN_TABLE' 
 And owner not in ('SQLTXPLAIN','SQLTXADMIN') ORDER BY 1;

INDEX_NAME                                         TABLE_NAME
-------------------------------------------------- ------------------------------
SYS_IL0000078251C00036$$                           PLAN_TABLE


SQL> select table_name, owner, TABLESPACE_NAME from dba_tables where table_name like '%PLAN_TABLE%' 
and owner not in ('SQLTXPLAIN','SQLTXADMIN') ORDER BY 1;

TABLE_NAME                     OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
PLAN_TABLE                     SYS                            SYSTEM
PLAN_TABLE$                    SYS






SQL>

-- The OLD PLAN_TABLE is empty at the moment
SQL> select count(*) from plan_table;

  COUNT(*)
----------
         0


-- Lets explain a test SQL to see what happens to the OLD PLAN_TABLE
SQL> explain plan for select count(*) from bigtab;

Explained.

-- And immediately 3 rows related to the plan line ids added to it
SQL> select count(*) from plan_table;

  COUNT(*)
----------
         3



-- Three entries for below 3 IDs.
SQL> select * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2140185107

---------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    69   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |        |     1 |            |          |
|   2 |   TABLE ACCESS FULL| BIGTAB | 72358 |    69   (0)| 00:00:01 |
---------------------------------------------------------------------

9 rows selected.


-- But the new PLAN_TABLE$ is still empty

SQL> select count(*) from PLAN_TABLE$ ;

  COUNT(*)
----------
         0

So, the question is – Is it safe to drop this table PLAN_TABLE ?

SQL> drop table PLAN_TABLE;

Table dropped.

SQL>

-- And the table is gone
SQL> select owner, object_name, object_type, created from dba_objects where object_name like '%PLAN_TABLE%' 
and owner not in ('SQLTXPLAIN','SQLTXADMIN') ORDER BY 1;

OWNER                          OBJECT_NAME          OBJECT_TYPE             CREATED
------------------------------ -------------------- ----------------------- ---------
PUBLIC                         PLAN_TABLE           SYNONYM                 17-APR-19
PUBLIC                         SQL_PLAN_TABLE_TYPE  SYNONYM                 17-APR-19
PUBLIC                         PLAN_TABLE_OBJECT    SYNONYM                 17-APR-19
SYS                            PLAN_TABLE_OBJECT    TYPE BODY               17-APR-19
SYS                            SQL_PLAN_TABLE_TYPE  TYPE                    17-APR-19
SYS                            PLAN_TABLE_OBJECT    TYPE                    17-APR-19
SYS                            PLAN_TABLE$          TABLE                   17-APR-19
SYS                            SQL_PLAN_TABLE_TYPE  TYPE                    17-APR-19

8 rows selected.

Now when the table is gone, lets check if we are still able to generate the execution plan.

SQL>
SQL> explain plan for select count(*) from bigtab;

Explained.

SQL> select * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2140185107

---------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    69   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |        |     1 |            |          |
|   2 |   TABLE ACCESS FULL| BIGTAB | 72358 |    69   (0)| 00:00:01 |
---------------------------------------------------------------------

9 rows selected.


SQL> select count(*) from plan_table$;

  COUNT(*)
----------
         3

And yes, no issues at all. The plan now started to sit inside PLAN_TABLE$ that has a PUBLIC SYNONYM called PLAN_TABLE. So, it’s totally safe to drop the PLAN_TABLE from your schema if it still exists and Oracle has now a public synonym for the same purpose.
WARNING: Don’t drop the PLAN_TABLE$ nor the PLAN_TABLE public synonym, these need to exist for the new PLAN_TABLE to work properly.

Hope It Helped!
Prashant Dixit

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 )

Connecting to %s

 
%d bloggers like this: