Use dbms_xplan.display_awr to Pull Full SQL and Execution Plan for a Query in AWR



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)|          |
------------------------------------------------------------------------------------------------------
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google