advertisements
_____________________________________________________________________________________________________________________
Overview:
DBMS_XPLAN package gives an easy way to display the
output of EXPLAIN PLAN command. You can use this package to display the
execution plan of a statement stored in the AWR report, SQL tuning set, cached
SQL cursor based on the information stored in the v$sql_plan and
v$sql_plan_statistics_all views.
- DISPALY –
Is to format and display the content of the plan table
- DISPLAY_AWR
– Is to format and display the contents of the execution plan of a stored
SQL statement in the AWR.
- DISPLAY_CURSOR
– Is to format and display the contents of the execution plan of a loaded
cursor.
- DISPLAY_SQL_PLAN_BASELINE
– is to display one or more execution plan for the SQL statement
identified by SQL handle
- DISPLAY_SQLSET
– Is to format and display the contents of the execution plan of
statements stored in a SQL tuning set.
DBMS_XPLAN.DISPLAY_AWR:
Generally in AWR report you can see
top sql queries, but you cannot see the full sql statement and execution plan
for the sql statements showed in the AWR report. Using GUI options like
enterprise manager you can see the execution plan. In command prompt using DBMS_XPLAN.DISPLAY_AWR
you can pull out the full query and execution plan for the queries showed in
the AWR report. You have to input the SQL-ID for the sql statement which is
already present in the AWR report.
Example
AWR
report sql content:
Elapsed Elapsed Time
Time (s) Executions
per Exec (s) %Total %CPU
%IO SQL Id
----------------
-------------- ------------- ------ ------ ------ -------------
4.9 2 2.45 1.5
86.9 15.8 283hzna0nxpnq
Module:
siebdev.exe
SELECT
/*+ ALL_ROWS */ T7.CONFLICT_ID, T7.LAST_UPD, T7.CREATED
,
T7.LAST_UPD_BY, T7.CREATED_BY, T7.MODIFICATION_NUM, T7
.ROW_ID,
T7.ASCII_ONLY_FLG, T7.ALIAS, T7.COMPUTE_EXPR, T
7.DFLT_INSNSTVTY_CD,
T7.DENORM_PATH, T7.ENCRPKEY_IDX_COL, T5.M
If
you try to fetch the details from Current shared pool (display_cursor) you cannot see the sql statement.
SQL>
select * from table(dbms_xplan.display_cursor('283hzna0nxpnq'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------SQL_ID:
283hzna0nxpnq, child number: 0 cannot be found
You
have to use DISPLAY_AWR option to
get the SQL statement and execution plan from the history. There may be more
than one SQL statements with same SQL_ID. In below example I am not displaying
the full SQL statement as it is a lengthy one.
SQL>
select * from table(dbms_xplan.display_awr('283hzna0nxpnq'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID
283hzna0nxpnq
--------------------
SELECT
/*+ ALL_ROWS */
T7.CONFLICT_ID, T7.LAST_UPD,
T7.CREATED, T7.LAST_UPD_BY, T7.CREATED_BY,
T7.MODIFICATION_NUM, T7.ROW_ID, T7.ASCII_ONLY_FLG,
T7.ALIAS, T7.COMPUTE_EXPR, T7.DFLT_INSNSTVTY_CD,
T7.DENORM_PATH, T7.ENCRPKEY_IDX_COL, T5.MODULE_NAME,
T7.CASE_MODE, T7.FKEY_TBL_ID, T4.NAME,
…….
……
…..
SOME text
deleted
'CASCADE_MODE')) ORDER BY T7.REPOSITORY_ID, T7.TBL_ID,
T7.SEQUENCE
Plan
hash value: 791259754
------------------------------------------------------------------------------------------------------
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 207K(100)| |
| 1 |
SORT ORDER BY
| | 1 |
578 | 207K (1)| 00:41:36 |
| 2 |
NESTED LOOPS OUTER
| | 1 |
578 | 207K (1)| 00:41:36 |
| 3 |
NESTED LOOPS
| | 1 |
503 | 207K (1)| 00:41:35 |
| 4 |
NESTED LOOPS OUTER
| | 1 |
379 | 207K (1)| 00:41:35 |
| 5 |
NESTED LOOPS
| | 1 |
354 | 207K (1)| 00:41:35 |
| 6 |
NESTED LOOPS OUTER
| | 68 |
21896 | 207K (1)| 00:41:34 |
| SOME
text deleted
| 20 |
INDEX UNIQUE SCAN |
S_PROJECT_P1 | 1 |
| 0 (0)|
|
------------------------------------------------------------------------------------------------------
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment