Check Explain Plan without executing the SQL in Oracle
1. Create the explained plan for the SQL Query:
Use EXPLAIN PLAN FOR before the query as follows:
Syntax:
EXPLAIN PLAN FOR Query
Example:
EXPLAIN PLAN FOR SELECT * FROM sales s, order1 o
WHERE s.orderno = o.orderno AND s.orddate = sysdate;
2. You can check the query plan by using UTLXPLS.SQL script present in the RDBMS folder or use the select statement with the advance option:
Option 1:
@?/rdbms/admin/utlxpls.sql;
Option 2:
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','','ADVANCED'));
Example of using both commands:
Example:
SQL>@?/rdbms/admin/utlxpls.sql;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1260318579
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 61 | 4 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 61 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| SALES | 1 | 35 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| ORDER1 | 1 | 26 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."ORDERNO"="O"."ORDERNO")
2 - filter("S"."ORDDATE"=SYSDATE@!)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
20 rows selected.
Advanced Execution plain has more detail
If you need more advanced plan then you can check the following command:
EXPLAIN PLAN FOR
SELECT * FROM sales s, order1 o
WHERE s.orderno = o.orderno
AND s.orddate = sysdate;
Explained.
--- Check advanced plan
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','','ADVANCED'));
select plan_table_output from table(dbms_xplan.display('plan_table','','ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1260318579
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 61 | 4 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 61 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| SALES | 1 | 35 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| ORDER1 | 1 | 26 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / S@SEL$1
3 - SEL$1 / O@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$1" "O"@"SEL$1")
LEADING(@"SEL$1" "S"@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "O"@"SEL$1")
FULL(@"SEL$1" "S"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('18.1.0')
OPTIMIZER_FEATURES_ENABLE('18.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("S"."ORDERNO"="O"."ORDERNO")
2 - filter("S"."ORDDATE"=SYSDATE@!)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - (#keys=1; rowset=256) "S"."ORDERNO"[NUMBER,22],
"O"."ORDERNO"[NUMBER,22], "S"."ORDERVALUE"[NUMBER,22],
"S"."ORDDATE"[DATE,7], "O"."TOTALVALUE"[NUMBER,22]
2 - (rowset=256) "S"."ORDERNO"[NUMBER,22], "S"."ORDDATE"[DATE,7],
"S"."ORDERVALUE"[NUMBER,22]
3 - (rowset=256) "O"."ORDERNO"[NUMBER,22],
"O"."TOTALVALUE"[NUMBER,22]
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
55 rows selected.
Use of the Second Parameter in the Advanced execution plan:
EXPLAIN PLAN SET STATEMENT_ID='ID1' FOR
SELECT * FROM sales s, order1 o WHERE s.orderno = o.orderno AND s.orddate = sysdate;
-- Fetch execution plan
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','ID1','ADVANCED'));