Get explain plan without executing the SQL in Oracle
1. Create the explain plan for the SQL Query
use EXPLAIN PLAN FOR before the query write.
EXPLAIN PLAN FOR
Query
Example:
SQL> EXPLAIN PLAN FOR
SELECT *
FROM sales s, order1 o
WHERE s.orderno = o.orderno
AND s.orddate = sysdate;
Explained.
2. You can check the query plan by using UTLXPLS.SQL script present in RDBMS folder.
@?/rdbms/admin/utlxpls.sql;
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'));
SQL> 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 Second parameter in 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'));
