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.
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 vignesh where id=1;
Explained.
Elapsed:
00:00:00.01
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan
hash value: 3079819651
-----------------------------------------------------------------------------------------------
|
Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 25 | 1
(0)| 00:00:01 |
| 1 |
TABLE ACCESS BY INDEX ROWID BATCHED| VIGNESH | 1 | 25 |
1 (0)| 00:00:01 |
|* 2 |
INDEX RANGE SCAN
| IDX | 1 |
| 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
PLAN_TABLE_OUTPUT
2 - access("ID"=1)
14
rows selected.
Elapsed:
00:00:00.12
SQL>
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'));
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.’
select * from table(dbms_xplan.display_awr('0kkhhb2w93cx0'));
SQL>
select sid,serial#,prev_sql_id from v$session where
audsid=userenv('sessionid');
SID
SERIAL# PREV_SQL_ID
----------
---------- -------------
34
6949 5kz06g5vx31fy
75
48107 88kyzk5sty9ff
2
rows selected.
Elapsed:
00:00:00.00
SQL>
DECLARE
l_sql_tune_task_id
VARCHAR2(100);
BEGIN
l_sql_tune_task_id
:= DBMS_SQLTUNE.create_tuning_task (
sql_id
=> '88kyzk5sty9ff',
scope
=> DBMS_SQLTUNE.scope_comprehensive,
time_limit
=> 500,
task_name
=> '88kyzk5sty9ff_tuning_task11',
description
=> 'Tuning task1 for statement 88kyzk5sty9ff');
DBMS_OUTPUT.put_line('l_sql_tune_task_id:
' || l_sql_tune_task_id);
END;
/
PL/SQL
procedure successfully completed.
Elapsed:
00:00:00.22
SQL>
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name =>
'88kyzk5sty9ff_tuning_task11');
PL/SQL
procedure successfully completed.
Elapsed:
00:00:00.43
SQL>
set long 65536
set
longchunksize 65536
set
linesize 100
select
dbms_sqltune.report_tuning_task('88kyzk5sty9ff_tuning_task11')from dual;
select
dbms_sqltune.rep('88kyzk5sty9ff_tuning_task11')from dual
*
ERROR
at line 1:
ORA-00904:
"DBMS_SQLTUNE"."REP": invalid identifier
No comments:
Post a Comment