Obtaining Execution Plan in Oracle

Obtaining Execution Plan in Oracle

What is execution plan?
An execution plan describes the list of operations which are performed by SQL Engine for SQL statement. The order of operations and their implementation is decided by the oracle query optimizer. An execution plan also shows the estimated number of rows, cost require to perform the operation, how many bytes oracle will read the data and estimated time required for the operations.

Sample Execution Plan:

1. Access Method & Join type of data access.

2. Name of the objects on which operation is going to perform.

3. Cardinality: Estimated number of rows.

4. Bytes: How much byte of data needs to process for each operation.

5. Cost: Cost require to perform operation.

6. Time: Estimated time require to complete the operation.

Displaying Execution plan:

Below are the two mostly used methods to display an execution plan.

1.      EXPLAIN PLAN command: This displays an execution plan for SQL statement without actually executing it.

2.      V$SQL_PLAN: This dynamic performance view shows execution plan for statement that has been compiled into cursor and stored in the cursor cache.

 DBMS_XPLAN Package: This package provides the different PL/SQL interfaces to display the plan from different sources.

• Obtaining Execution Plan from EXPLAIN PLAN Command and DBMS_XPLAN.DISPLAY function:

Below example shows how to use EXLAIN_PLAN command and DBMS_XPLAN.DISPLAY function to display the plan.

SQL> explain plan for select * from test1 a,test2 b where a.emp_no=b.emp_no;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 497311279

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

| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |       |     1 |    40 |     5  (20)| 00:00:01 |

|*  1 |  HASH JOIN         |       |     1 |    40 |     5  (20)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| TEST1 |     1 |    20 |     2   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| TEST2 |     1 |    20 |     2   (0)| 00:00:01 |

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

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

   1 - access("A"."EMP_NO"="B"."EMP_NO")

Note

-----

   - dynamic sampling used for this statement (level=2)

19 rows selected.

• Obtaining Plan from DBMS_XPLAN.DISPLAY_CURSOR function:

Execution plan for executed statement can be displayed by using DBMS_XPLAN.DISPLAY_CURSOR function. It takes input as SQL_ID of the statement.

SQL> select * from table(dbms_xplan.display_cursor('0kkhhb2w93cx0'));

PLAN_TABLE_OUTPUT

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

SQL_ID  0kkhhb2w93cx0, child number 0

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

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,exts

ize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL,

:13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16,

spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where

ts#=:1 and file#=:2 and block#=:3

Plan hash value: 2170058777

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

PLAN_TABLE_OUTPUT

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

| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | UPDATE STATEMENT      |                |       |       |     3 (100)|          |

|   1 |  UPDATE               | SEG$           |       |       |            |          |

|   2 |   TABLE ACCESS CLUSTER| SEG$           |     1 |    72 |     3   (0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN  | I_FILE#_BLOCK# |     1 |       |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

   3 - access("TS#"=:1 AND "FILE#"=:2 AND "BLOCK#"=:3)

• Obtaining History Plan from DBMS_XPLAN.DISPLAY_AWR function

Sometimes you will not get plan from DISPLAY_CURSOR function as it might have flushed from the cursor cache. In this case we can obtain the plan using DISPLAY_AWR function. This function gives us all the execution plans that the statement had used in the past. It takes input as SQL_ID of the statement.

SQL> select * from table(dbms_xplan.display_awr('0kkhhb2w93cx0'));

PLAN_TABLE_OUTPUT

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

SQL_ID 0kkhhb2w93cx0

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

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,exts

ize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL,

:13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16,

spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where

ts#=:1 and file#=:2 and block#=:3

Plan hash value: 2170058777

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

 PLAN_TABLE_OUTPUT

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

| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | UPDATE STATEMENT      |                |       |       |     2 (100)|          |

|   1 |  UPDATE               | SEG$           |       |       |            |          |

|   2 |   TABLE ACCESS CLUSTER| SEG$           |     1 |    71 |     2   (0)| 00:00:01 |

|   3 |    INDEX UNIQUE SCAN  | I_FILE#_BLOCK# |     1 |       |     1   (0)| 00:00:01 |

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

19 rows selected.

Conclusion: In this article we have seen the methods to obtain an execution plan from the cursor cache as well as from AWR. I will write an article on interpreting these execution plans very soon.