Tags

, , , , , ,

This post is to demonstrate the ways to create and display explain plan. But before we proceed with the steps of creating and displaying explain, the schema must have the PLAN_TABLE.

A lot of times developers encounters plan table not found error “ORA-02404: specified plan table not found”. What does it mean? It means the schema doesn’t have PLAN_TABLE. The best and easiest way is to run the following script to create the PLAN_TABLE :


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

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(30)
 OBJECT_NAME                                        VARCHAR2(30)
 OBJECT_ALIAS                                       VARCHAR2(65)
 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
 OTHER_XML                                          CLOB
 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(30)

The syntax for generating an explain plan for a query :


SQL> EXPLAIN PLAN FOR select * from emp where deptno = 10;

Explained.

You could also assign a statement_id for the generated explain plan :



SQL> EXPLAIN PLAN SET STATEMENT_ID = 'test' FOR select * from emp where deptno =
 10;

Explained.

At this stage, the PLAN_TABLE is populated with the required data. However, in order to understand it, we need to display it in a readable format. If you just try to look into the PLAN_TABLE, you wont be able to easily interpret the explain plan. Execute the below query :

SELECT * FROM PLAN_TABLE WHERE STATEMENT_ID = 'test';

Difficult to interpret, isn’t it? You want it to be properly formatted to be readable. DBMS_XPLAN does it for you.


SQL> set linesize 132;
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------
Plan hash value: 2872589290

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |   261 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     3 |   261 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

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

   1 - filter("DEPTNO"=10)

Note
-----
   - dynamic sampling used for this statement (level=2)

17 rows selected.