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

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.

 

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

 

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()

 

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.

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.