What is the Execution Plan and How to View Execution Plan in Oracle | Oracle Database Performance Tuning Tutorial -4

Mehmet Salih Deveci November 6, 2019 Leave a comment

Hi,

I will explain What is the Execution Plan and How to Generate Execution Plan in this article.

What Is an Execution Plan ?

Each Database management system like Oracle generates an execution plan for SQL Statements to present to the execution engine for implementation which is the output of the optimizer.

If you want to learn more details about Optimizer, You should read the following article before this.

What is the Oracle Optimizer and How Cost-Based Optimizer Works ? | Oracle Database Performance Tuning Tutorial -3

Oracle Execution Plan

Execution plan consists of the instructions for the execution engine about the operations it must perform for retrieving the data most efficiently.

For Example; You want to go to B point ( London) from A point ( Istanbul ). There are several ways for this travel as follows.

Airplane
Car
Train
Ship
Bicycle
Walking

You have several ways for going to London, but each of these vehicles’ cost is different. You should decide which one to use.

Database management system is running like this example.

Execution plan steps are performed in a parent-child relationship between steps, they are not performed in the order in which they are numbered. You can see the following information in the Execution plan steps.

An ordering of the tables referenced by the SQL statement
An access method for each table
A join method for tables
Data operations, such as filter, sort, or aggregation

Execution plan is a vital point for Performance tuning of Oracle database, then Where to access or Find Execution Plans?

PLAN_TABLE (SQL Developer or SQL*Plus)
V$SQL_PLAN (Library Cache)
V$SQL_PLAN_MONITOR
DBA_HIST_SQL_PLAN (AWR)
SQL tuning set
STATS$SQL_PLAN (Statspack)
SQL management base (SQL plan baselines)
Event 10053 trace file
Trace files generated by DBMS_MONITOR
Process state dump trace file

View Execution Plan in Oracle

There are lots of ways to view Execution plan in Oracle as follows.

The EXPLAIN PLAN command
a.SELECT from PLAN_TABLE
b.DBMS_XPLAN.DISPLAY()
SQL*Plus Autotrace: SET AUTOTRACE ON
• DBMS_XPLAN.DISPLAY_CURSOR()
• DBMS_XPLAN.DISPLAY_AWR()
• DBMS_XPLAN.DISPLAY_SQLSET()
• DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE()

Oracle Explain Plan

EXPLAIN PLAN command generates the execution plan and stores the plan in PLAN_TABLE ( table ). EXPLAIN PLAN is just generates the plan, it doesn’t execute the SQL statement itself.

PLAN_TABLE is created automatically as a global temporary table to keep the output of an EXPLAIN PLAN statement for all users.

You can create your own PLAN_TABLE executing the $ORACLE_HOME/rdbms/admin/utlxplan.sql script, if you want to keep the execution plan information for a long term. You can query the PLAN_TABLE via DBMS_XPLAN.DISPLAY().

You can use EXPLAIN PLAN , DBMS_XPLAN.DISPLAY() as follows to see execution plan of any specific sql.

SQL> 
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'Deveci' FOR SELECT * FROM MEHMET.DEVECI;

Explained.

SQL> 
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> select * from table(DBMS_XPLAN.DISPLAY());
Plan hash value: 1925475714

-----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |        |    14 |   420 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS STORAGE FULL FIRST ROWS| DEVECI |    14 |   420 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

12 rows selected.

SQL>

EXPLAIN PLAN command example as in the above is Typical option. You can use ALL option which is used with the DISPLAY function allows you to output the maximum user level information (Includes TYPICAL level, with additional information such as PROJECTION, ALIAS, and information about REMOTE SQL)

You can use PLAN_TABLE: ALL option as follows and see detailed information about SQL.

SQL> 
SQL> select * from table(DBMS_XPLAN.DISPLAY(null,null,'ALL'));
Plan hash value: 1925475714

-----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |        |    14 |   420 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS STORAGE FULL FIRST ROWS| DEVECI |    14 |   420 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DEVECI@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "DEVECI"."ID"[NUMBER,22], "DEVECI"."NAME"[VARCHAR2,30]

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

22 rows selected.

SQL>

You can use Enterprise manager, TOAD and SQL Developer to view Explain plan and Execution plan by clicking button marked yellow ( in TOAD ) as follows.

SQL*Plus Autotrace

You can automatically get a report for execution plan and the statement execution statistics by using Autotrace, if you use SQL*Plus or SQL Developer. You must have a PLAN_TABLE available in your schema for using this feature.

You can enable AUTOTRACE in various ways using the syntax as follows.

OFF: Disables autotracing SQL
ON: Enables autotracing SQL
TRACE or TRACE[ONLY]: Enables autotracing SQL and suppresses statement output
EXPLAIN: Displays execution plans, but does not display statistics
STATISTICS: Displays statistics, but does not display execution plans

You can use AUTOTRACE options as follows to see execution plan and its details.

To start tracing SQL using AUTOTRACE, execute the following script.

  SQL> set autotrace on

SQL> set autotrace on
SQL> SELECT * FROM MEHMET.DEVECI where id=2;

        ID NAME
---------- ------------------------------
         2 Salih
         2 Salih


Execution Plan
----------------------------------------------------------
Plan hash value: 1925475714

--------------------------------------------------------------------------------
---------------

| Id  | Operation                            | Name   | Rows  | Bytes | Cost (%C
PU)| Time     |

--------------------------------------------------------------------------------
---------------

|   0 | SELECT STATEMENT                     |        |     2 |    60 |     3
(0)| 00:00:01 |

|*  1 |  TABLE ACCESS STORAGE FULL FIRST ROWS| DEVECI |     2 |    60 |     3
(0)| 00:00:01 |

--------------------------------------------------------------------------------
---------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - storage("ID"=2)
       filter("ID"=2)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        668  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>

Display the execution plan only without execution, execute the following script.

  SQL> set autotrace traceonly explain

SQL> set autotrace traceonly explain
SQL> SELECT * FROM MEHMET.DEVECI where id=2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1925475714

--------------------------------------------------------------------------------
---------------

| Id  | Operation                            | Name   | Rows  | Bytes | Cost (%C
PU)| Time     |

--------------------------------------------------------------------------------
---------------

|   0 | SELECT STATEMENT                     |        |     2 |    60 |     3
(0)| 00:00:01 |

|*  1 |  TABLE ACCESS STORAGE FULL FIRST ROWS| DEVECI |     2 |    60 |     3
(0)| 00:00:01 |

--------------------------------------------------------------------------------
---------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - storage("ID"=2)
       filter("ID"=2)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL>

Display rows and statistics, execute the following script.

 SQL> set autotrace on statistics

SQL> set autotrace on statistics
SQL> SELECT * FROM MEHMET.DEVECI where id=2;

ID NAME
---------- ------------------------------
2 Salih
2 Salih


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
668 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

SQL>

Get the plan and the statistics only (suppress rows), execute the following script.

  SQL> set autotrace traceonly

SQL> set autotrace traceonly 
SQL> SELECT * FROM MEHMET.DEVECI where id=2;


Execution Plan
----------------------------------------------------------
Plan hash value: 1925475714

--------------------------------------------------------------------------------
---------------

| Id  | Operation                            | Name   | Rows  | Bytes | Cost (%C
PU)| Time     |

--------------------------------------------------------------------------------
---------------

|   0 | SELECT STATEMENT                     |        |     2 |    60 |     3
(0)| 00:00:01 |

|*  1 |  TABLE ACCESS STORAGE FULL FIRST ROWS| DEVECI |     2 |    60 |     3
(0)| 00:00:01 |

--------------------------------------------------------------------------------
---------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - storage("ID"=2)
       filter("ID"=2)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        668  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> 
SQL> 
SQL> 
SQL>

I will continue to explain how to Interpret Execution Plans in the next article.

Next post link about Performance Tuning Tutorial is as follows.

How to Read or Interpret an Execution Plan in Oracle | Oracle Database Performance Tuning Tutorial -5

Do you want to learn Top 30 SQL Tuning Tips and Tricks, then read the following articles.

SQL Tuning Tips and Tricks Tutorial in Oracle -3

IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial