Get explain plan without executing the SQL in Oracle

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'));