[Oracle] - 性能优化工具(5) - AWRSQL

在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语句相关信息抽取出来,如:

@?/rdbms/admin/awrsqrpt.sql
以下是上诉语句生成的AWRSQL:

WORKLOAD REPOSITORY SQL Report

Snapshot Period Summary

DB Name DB Id Instance Inst num Startup Time Release RAC
TEST11G 977587123 test11g 1 23-2月 -14 07:02 11.2.0.1.0 NO
  Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 2039 23-2月 -14 15:56:23 28 2.0
End Snap: 2040 23-2月 -14 15:56:38 30 1.9
Elapsed:   0.24 (mins)    
DB Time:   0.25 (mins)    

SQL Summary

    SQL Id Elapsed Time (ms) Module Action SQL Text
    1rrtf60fmhxkj 13,564 SQL*Plus   SELECT COUNT(*) FROM T1, T2 WHERE T1.OBJECT_ID=T2.OBJECT_ID...


    Back to Top

    SQL ID: 1rrtf60fmhxkj

    # Plan Hash Value Total Elapsed Time(ms) Executions 1st Capture Snap ID Last Capture Snap ID
    1 4274056747 13,564 1,000 2040 2040


    Back to Top

    Plan 1(PHV: 4274056747)

    Back to Top

    Plan Statistics

    • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
    Stat Name Statement Total Per Execution % Snap Total
    Elapsed Time (ms) 13,564 13.56 92.27
    CPU Time (ms) 13,385 13.38 91.76
    Executions 1,000    
    Buffer Gets 1,051,075 1,051.08 99.48
    Disk Reads 1,044 1.04 99.90
    Parse Calls 1 0.00 0.36
    Rows 1,000 1.00  
    User I/O Wait Time (ms) 55    
    Cluster Wait Time (ms) 0    
    Application Wait Time (ms) 0    
    Concurrency Wait Time (ms) 0    
    Invalidations 0    
    Version Count 1    
    Sharable Mem(KB) 14    

    Back to Plan 1(PHV: 4274056747) 
    Back to Top

    Execution Plan

    Id Operation Name Rows Bytes Cost (%CPU) Time
    0 SELECT STATEMENT       296 (100)  
    1    SORT AGGREGATE   1 26    
    2      HASH JOIN   100 2600 296 (1) 00:00:04
    3        TABLE ACCESS FULL T2 100 1300 3 (0) 00:00:01
    4        TABLE ACCESS FULL T1 69217 878K 292 (1) 00:00:04

    • dynamic sampling used for this statement (level=2)

    Back to Plan 1(PHV: 4274056747) 
    Back to Top

    Full SQL Text

    SQL Id SQL Text
    1rrtf60fmhxkj SELECT COUNT(*) FROM T1, T2 WHERE T1.OBJECT_ID=T2.OBJECT_ID



    [Oracle] - 性能优化工具(5) - AWRSQL,布布扣,bubuko.com

    [Oracle] - 性能优化工具(5) - AWRSQL

    上一篇:工作中常用SQL 查询语句备忘


    下一篇:sqlserver、mysql如何获取连接字符串