• Extract SQL Plan from AWR


    之前有用户在AWR中定位到问题SQL语句后想要了解该SQL statement的具体执行计划,于是就用AWR报告中得到的SQL ID去V$SQL等几个动态性能视图中查询,但发现V$SQL或V$SQL_PLAN视图都已经找不到对应SQL ID的记录,一般来说这些语句已经从shared pool共享池中被替换出去了。 这个时候我们可以尝试使用DBMS_XPLAN.DISPLAY_AWR存储过程来将Oracle Automatic Workload Repository自动负载仓库中记录的SQL语句相关信息抽取出来,如:
    SQL> set linesize 200 pagesize 2000;
    
    SQL> select * from global_name;
    
    GLOBAL_NAME
    --------------------------------------------------------------------------------
    oracledatabase12g.com
    
    SQL> select /* extract_me */ count(*) from maclean;
    
      COUNT(*)
    ----------
          9564
    
    SQL> select sql_id from v$sql where sql_text like '%extract_me%' and sql_text not like '%like%';
    
    SQL_ID
    -------------
    8vff23q8qp9fj
    
    SQL> exec dbms_workload_repository.create_snapshot;
    PL/SQL procedure successfully completed.
    
    QL> select * from table(dbms_xplan.display_cursor('8vff23q8qp9fj',0));
    
    PLAN_TABLE_OUTPUT
    -------------------------------------
    SQL_ID  8vff23q8qp9fj, child number 0
    -------------------------------------
    select /* extract_me */ count(*) from maclean
    
    Plan hash value: 1679547536
    
    ----------------------------------------------------------------------
    | Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |         |       |    34 (100)|          |
    |   1 |  SORT AGGREGATE    |         |     1 |            |          |
    |   2 |   TABLE ACCESS FULL| MACLEAN | 10438 |    34   (0)| 00:00:01 |
    ----------------------------------------------------------------------
    
    Note
    -----
       - dynamic sampling used for this statement
    
    18 rows selected.
    
    /* 冲刷共享池后v$SQL/V$SQL_PLAN等动态视图内的记录会消失,
         需要注意的是如果在AWR快照自动生成之前冲刷了共享池,那么可能丢失SQL的执行统计信息
     */
    
    SQL> alter system flush shared_pool;
    System altered.
    
    SQL> select * from table(dbms_xplan.display_cursor('8vff23q8qp9fj',0));
    
    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------
    SQL_ID: 8vff23q8qp9fj, child number: 0 cannot be found
    
    /* 此时就可以利用dbms_xplan.display_awr存储过程来抓取该SQL_ID对应的执行计划 */
    
    SQL>  select * from table(dbms_xplan.display_awr('8vff23q8qp9fj'));
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------
    SQL_ID 8vff23q8qp9fj
    --------------------
    select /* extract_me */ count(*) from maclean
    
    Plan hash value: 1679547536
    
    ----------------------------------------------------------------------
    | Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |         |       |    34 (100)|          |
    |   1 |  SORT AGGREGATE    |         |     1 |            |          |
    |   2 |   TABLE ACCESS FULL| MACLEAN | 10438 |    34   (0)| 00:00:01 |
    ----------------------------------------------------------------------
    
    Note
    -----
       - dynamic sampling used for this statement
    
    18 rows selected.
    
    /* 这里可以代入'all'选项获取更细致的计划信息 */
    
    SQL> select * from table(dbms_xplan.display_awr('8vff23q8qp9fj',null,null,'all'));
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------
    SQL_ID 8vff23q8qp9fj
    --------------------
    select /* extract_me */ count(*) from maclean
    
    Plan hash value: 1679547536
    
    ----------------------------------------------------------------------
    | Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |         |       |    34 (100)|          |
    |   1 |  SORT AGGREGATE    |         |     1 |            |          |
    |   2 |   TABLE ACCESS FULL| MACLEAN | 10438 |    34   (0)| 00:00:01 |
    ----------------------------------------------------------------------
    
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
    
       1 - SEL$1
       2 - SEL$1 / MACLEAN@SEL$1
    
    Note
    -----
       - dynamic sampling used for this statement
    
    24 rows selected.
    
    /* 也可以从 dba_hist_sql_plan/dba_hist_sqltext等awr历史视图中直接观察该SQL ID对应的语句 */
    
    SQL> select OPERATION,OPTIONS,OBJECT_NAME from dba_hist_sql_plan where sql_id='8vff23q8qp9fj';
    
    OPERATION                      OPTIONS                        OBJECT_NAME
    ------------------------------ ------------------------------ -------------------------------
    SELECT STATEMENT
    SORT                           AGGREGATE
    TABLE ACCESS                   FULL                           MACLEAN
  • 相关阅读:
    Node.js:events事件模块
    Node.js:console模块
    Node.js:DNS模块的使用
    CSS3 Notes: -webkit-box-reflect实现倒影
    H5 Notes:PostMessage Cross-Origin Communication
    H5 Notes:Navigator Geolocation
    Notes:SVG(4)基于stroke-dasharray和stroke-dashoffset圆形进度条
    Notes:SVG(3)---滤镜和渐变
    如何写出优美的 C 代码 面向对象的 C
    Source Insight快捷键
  • 原文地址:https://www.cnblogs.com/macleanoracle/p/2967740.html
Copyright © 2020-2023  润新知