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