About
EXPLAIN PLAN is an Oracle SQL Command that tell you what the query plan for a given SQL would be :
- if executed it right now
- in the current session
- with the current settings
For this purpose, it will insert rows in a table. It's a DML statement
EXPLAIN PLAN cannot tell you what plan was actually used to run a given query in the past because the query could have taken place in a session with very different settings (Example : Sort area size).
Articles Related
Syntax
The format of the EXPLAIN PLAN command is :
explain plan
[set statement_id= 'text']
[into [owner.]table_name]
for statement;
- The text in bracket is optionnal.
- The statement_id allows you to store mutliple plans in the plan table
- The owner.table_name allow you to use another table than plan_table.
PLAN_TABLE
The PLAN_TABLE is a table owned by PUBLIC. The creation script is in this location Oracle_Home/rdbms/admin/utlxplan.sql. utlxplan stands for UTiLity eXplain PLAN table.
create table PLAN_TABLE sharing=none (
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 numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
depth numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000),
projection varchar2(4000),
time numeric,
qblock_name varchar2(128),
other_xml clob
);
Example
In the hr schema
explain plan
set statement_id= 'myStatementId'
into HR.MY_PLAN_TABLE
for
SELECT e.first_name, e.last_name, e.salary, d.department_name
FROM hr.employees e, hr.departments d
WHERE d.department_name IN ('Marketing', 'Sales')
AND e.department_id = d.department_id;
Explained
The EXPLAIN PLAN statement is a data manipulation language (DML) statement, rather than a data definition language (DDL) statement. It will insert rows in the plan_table Therefore a COMMIT to preserve the data.
COMMIT;
In SQL Plus, you can add the explain plan statement before the last query in the SQL buffer with the help of zero.
0 explain for
run
will explain your last statement
How to view the result
From the default table
Once the sql is explained, you can view the execution plan from the default table (ie without specifying the into clause):
- with the dbms_xplan.display function:
SELECT * FROM TABLE(dbms_xplan.display);
- or with the utlxpls.sql in SQL Plus. utlxpls stands for UTiLity eXplain PLan Serial. It displays normal, serial (non-parallel) plans.
@?rdms\admin\utlxpls.sql
- or with utlxplp.sql in SQL Plus. utlxplp stands for UTiLity eXplain PLan Parallel. It displays the contents of the plan table, including information specific to parallel-query plans.
@?rdms\admin\utlxplp.sql
They give the same answer.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 1021246405
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 722 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 19 | 722 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales')
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
Note
-----
- this is an adaptive plan
22 rows selected.
From the specified table or specified statement id
- With the DBMS_XPLAN.DISPLAY function
select * from TABLE(DBMS_XPLAN.DISPLAY('HR.MY_PLAN_TABLE','myStatementId','ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 1021246405
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 722 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 19 | 722 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / D@SEL$1
4 - SEL$1 / E@SEL$1
5 - SEL$1 / E@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales')
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30],
"E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25], "E"."SALARY"[NUMBER,22]
2 - (#keys=0) "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30],
"E".ROWID[ROWID,10]
3 - "D"."DEPARTMENT_ID"[NUMBER,22], "D"."DEPARTMENT_NAME"[VARCHAR2,30]
4 - "E".ROWID[ROWID,10]
5 - "E"."FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25],
"E"."SALARY"[NUMBER,22]
Note
-----
- this is an adaptive plan
42 rows selected.
- Or With a specific SQL
SELECT
id,
LPAD( ' ', 2 *( LEVEL - 1 ) )
||operation operation,
OPTIONS,
object_name,
object_alias,
qblock_name,
position
FROM
PLAN_TABLE
START WITH id = 0
AND statement_id = 'myStatementId'
CONNECT BY PRIOR id = parent_id
AND statement_id = 'myStatementId'
ORDER BY
id;
ID OPERATION OPTIONS OBJECT_NAME OBJECT_ALI QBLOCK_NAM POSITION
--- ------------------------------ --------------- ------------------------------ ---------- ---------- ----------
0 SELECT STATEMENT 4
1 HASH JOIN SEL$1 1
2 NESTED LOOPS SEL$1 1
3 NESTED LOOPS 1
4 STATISTICS COLLECTOR 1
5 TABLE ACCESS FULL DEPARTMENTS D@SEL$1 SEL$1 1
6 INDEX RANGE SCAN EMP_DEPARTMENT_IX E@SEL$1 SEL$1 2
7 TABLE ACCESS BY INDEX ROWID EMPLOYEES E@SEL$1 SEL$1 2
8 TABLE ACCESS FULL EMPLOYEES E@SEL$1 SEL$1 2
For info: SQL Plus formatting
set linesize 140
column id format 99
column operation format a30
column options format a15
column object_alias format a10
column QBLOCK_NAME format a10