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.
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.
