ORACLE SQL Execution Plan

How ORACLE Execution Plan helps in Optimizing SQL Queries?

When SQL statements is passed to ORACLE, the Cost Based Optimizer(CBO) uses Database Statistics and information about Table to create an execution plan which it uses to retrieve the data in the most efficient manner.

EXPLAIN PLAN statement reveal the execution plan for the SQL statement and the DBMS_XPLAN package is used to format the output of an explain plan. Explain Plan generates the Execution Plan for a Query without executing the Query itself and without impacting the database.

SETUP:

To use EXPLAIN PLAN utility, first create a Plan table. ORACLE provides a script in $ORACLE_HOME/RDBMS/ADMIN called utlxplan.sql. Execute the script and create a Public Synonym for PLAN_TABLE;

SQL> Show user

USER is "SYS"

SQL> @E:\app\Admin\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql

Table created.

SQL> Grant ALL ON Sys. PLAN_TABLE TO Public;

Grant succeeded.

SQL> CREATE PUBLIC SYNONYM PLAN_TABLE FOR Sys.plan_table;

Synonym created.

Explain Plan takes the SQL statements as input, runs the SQL Optimizer and outputs the access path information into a PLAN_TABLE which can then be interrogated to see the access methods.

SQL> EXPLAIN PLAN FOR

           SELECT * From EMP_TEST E, DEPT_TEST D

           Where E.DEPTNO=D.DEPTNO AND E.ENAME='Ankit';

Explained.

SQL> EXPLAIN PLAN FOR
  2             SELECT * From EMP_TEST E, DEPT_TEST D
  3             Where E.DEPTNO=D.DEPTNO AND E.ENAME='Ankit';

Explained.

SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 2564510465

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    32 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |     1 |    32 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP_TEST     |     1 |    21 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | INDEX1       |     1 |       |     0   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT_TEST    |     5 |    55 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | SYS_C0014301 |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   3 - access("E"."ENAME"='Ankit')
   5 - access("E"."DEPTNO"="D"."DEPTNO")

18 rows selected.

Next, we use the DBMS_XPLAN.DISPLAY function to display the execution plan.

The DBMS_XPLAN.DISPLAY function can accept 3 optional parameters:

• Table_Name - Name of the PLAN_TABLE, default value 'PLAN_TABLE'.
• Statement_id - Statement id of the plan to be displayed. The default value is NULL, which displays the most recent execution plan in the PLAN_TABLE.
• Format - Controls the level of detail displayed, default value 'TYPICAL'. Other values include 'BASIC', 'ALL', 'SERIAL'. There is also an undocumented 'ADVANCED' setting.

SQL> EXPLAIN PLAN SET STATEMENT_ID='SID' FOR
  2      SELECT * FROM EMP_TEST E, DEPT_TEST D
  3      Where E.DEPTNO=D.DEPTNO  AND ENAME='Manu';

Explained.

SQL> SET Linesize 130
SQL> SELECT * From
  2  TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','SID','BASIC'));
Plan hash value: 2564510465

-----------------------------------------------------
| Id  | Operation                    | Name         |
-----------------------------------------------------
|   0 | SELECT STATEMENT             |              |
|   1 |  NESTED LOOPS                |              |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP_TEST     |
|   3 |    INDEX UNIQUE SCAN         | INDEX1       |
|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT_TEST    |
|   5 |    INDEX UNIQUE SCAN         | SYS_C0014301 |
-----------------------------------------------------

12 rows selected.

Using the SQL *Plus Autotrace Utility

The simplest way to get an Execution Plan is to use the Autotrace feature of SQL *Plus. It offers statement tracing and feedback on any successful Select, Insert, Update or Delete statement. IT requires a Plan Table under the current user’s schema. It requires DBA role for the user executing the trace can be found in $ORACLE_HOME/Sqlplus/Admin/plustrce.sql

Tracing is enabled with following statement:

SQL> SET Autotrace on

SQL> SET Autotrace on
SQL> SELECT * From EMP_TEST E, DEPT_TEST D
         Where E.DEPTNO=D.DEPTNO And E.ENAME='Rajan';

    EMP_NO ENAME          DEPTNO HIREDATE      DEPTNO DEPTNAME
---------- ---------- ---------- --------- ---------- --------------------
      1006 Rajan              20 16-AUG-10         20 Retail

Execution Plan
----------------------------------------------------------
Plan hash value: 2564510465
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    32 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |     1 |    32 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP_TEST     |     1 |    21 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | INDEX1       |     1 |       |     0   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT_TEST    |     5 |    55 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | SYS_C0014301 |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("E"."ENAME"='Rajan')
   5 - access("E"."DEPTNO"="D"."DEPTNO")

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

Autotrace provides instantaneous feedback including the returned rows, execution plan and statistics. This is important data that can be used to tune your Queries.

In the Autotrace statistics, Compare the “rows processed” with the “consistent gets”. This will give you an idea how many Disk block reads were required to fetch returned rows.

Note: AUTOTRACE requires that the statement run to the completion. Unlike explain plan you will need to wait for the Statistics in Autotrace.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.