EXPLAIN PLAN is an Oracle SQL Command that tell you what the query plan for a given SQL would be :
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).
The format of the EXPLAIN PLAN command is :
explain plan
[set statement_id= 'text']
[into [owner.]table_name]
for statement;
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
);
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
Once the sql is explained, you can view the execution plan from the default table (ie without specifying the into clause):
SELECT * FROM TABLE(dbms_xplan.display);
@?rdms\admin\utlxpls.sql
@?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.
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.
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