Methods required to obtain a formatted explain plan differ depending on which version of Oracle you are using. The initial steps to create a plan table and make the optimizer populate the plan table with the execution plan are common to all versions of Oracle.
Note that there are limitations with explain plan when the SQL uses implicit type conversions or bind variables. Oracle does not support EXPLAIN PLAN for statements performing implicit type conversion of date bind variables. With bind variables in general, the EXPLAIN PLAN output might not represent the real execution plan.
The methods of obtaining the real execution plan in 10.2 that do not require use of the Explain Plan command or the plan table are more reliable.
Create a Plan Table
- On version up to and including 9i, use the utlxplan.sql script to create the plan table as instructed below.
SQL> @?/rdbms/admin/utlxplan
- On 10g and above there is a new script – catplan.sql – to create the plan table that creates a public plan table as a global temporary table accessible from any schema.
SQL> @?/rdbms/admin/catplan>
Note that the plan table format can change between versions so ensure that it is created using the script from the current version.
Populate the Plan Table
EXPLAIN PLAN FOR is used for populating the PLAN_TABLE
SQL> explain plan for select * from emp; Explained.
Displaying The Execution Plan
Versions 8.1.7 and 9.0.1
From version 8.1.5, Oracle has supplied 2 scripts to extract formatted explain plans from plan_tables. One is for serial plans and the other is for parallel plans. They can be found under $ORACLE_HOME/rdbms/admin. Examples of their usage are below.
- To obtain a formatted execution plan for serial plans:
SQL> set lines 150 SQL> set head off SQL> @?/rdbms/admin/utlxpls
- To obtain a formatted execution plan for parallel plans:
SQL> set lines 150 SQL> set head off SQL> @?/rdbms/admin/utlxplp
Version 9.2
With Oracle 9i version 9.2, Oracle supplies a utility called dbms_xplan. It is created by dbmsutil.sql which is called by catproc.sql. As such it should already be installed on most 9.2 databases.
- To generate a formatted explain plan of the query that has just been ‘explained‘:
SQL> set lines 150
SQL> set head off
SQL> set pagesize 0
SQL> select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 27 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 3 | 27 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22],
"EMP"."SALARY"[NUMBER,22]
Version 10.2 and above
In addition to the standard explain plan option, you can pull execution plans from the library cache if the SQL has already been executed.
- To get the plan of the last executed SQL issue the following:
SQL> select * from table(dbms_xplan.display_cursor(null,null, 'ALL'));
SQL_ID a2dk8bdn0ujx7, child number 0
-------------------------------------
select * from emp
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| EMP | 3 | 27 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22],
"EMP"."SALARY"[NUMBER,22]
- If you know the hash value of the SQL, you can use dbms_xplan.display_cursor as follows:
SQL> select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD));
Enter value for sql_id: a2dk8bdn0ujx7
Enter value for child: 0
old 1: select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD))
new 1: select * from TABLE(dbms_xplan.display_cursor('a2dk8bdn0ujx7', 0))
SQL_ID a2dk8bdn0ujx7, child number 0
-------------------------------------
select * from emp
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| EMP | 3 | 27 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
- We can also get run time statistics with some additional options and if we use the gather_plan_statistics hint.
SQL> select /*+ gather_plan_statistics */ * from emp;
SQL> set linesize 150
SQL> set pagesize 0
SQL> select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD,'ALL IOSTATS LAST'))
2 /
Enter value for sql_id: fjhbyrfs7gygq
Enter value for child: 1
old 1: select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD,'ALL IOSTATS LAST'))
new 1: select * from TABLE(dbms_xplan.display_cursor('fjhbyrfs7gygq', 1,'ALL IOSTATS LAST'))
SQL_ID fjhbyrfs7gygq, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ * from emp
Plan hash value: 3956160932
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS FULL| EMP | 1 | 3 | 27 | 2 (0)| 00:00:01 | 3 |00:00:00.01 | 4 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22], "EMP"."SALARY"[NUMBER,22]
SQL_ID: specifies the sql_id value for a specific SQL statement, as shown in V$SQL.SQL_ID, V$SESSION.SQL_ID, or V$SESSION.PREV_SQL_ID. If no sql_id is specified, the last executed statement of the current session is shown.
CHILD: specifies the child number for a specific sql cursor, as shown in V$SQL.CHILD_NUMBER or in V$SESSION.SQL_CHILD_NUMBER, V$SESSION.PREV_CHILD_NUMBER. If not specified, all child cursors for the specified sql_id are displayed.
Thanks, this description is simple and understable! Regards from Argentina.