首页 > 代码库 > [Oracle] - 性能优化工具（5） - AWRSQL

[Oracle] - 性能优化工具（5） - AWRSQL

2024-07-08 14:57:44   218人阅读

在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 NameDB IdInstanceInst numStartup TimeReleaseRAC
TEST11G977587123test11g123-2月 -14 07:0211.2.0.1.0NO
 Snap IdSnap TimeSessionsCursors/Session
Begin Snap:203923-2月 -14 15:56:23282.0
End Snap:204023-2月 -14 15:56:38301.9
Elapsed: 0.24 (mins)  
DB Time: 0.25 (mins)  

SQL Summary

    SQL IdElapsed Time (ms)ModuleActionSQL Text
    1rrtf60fmhxkj13,564SQL*Plus SELECT COUNT(*) FROM T1, T2 WHERE T1.OBJECT_ID=T2.OBJECT_ID...


    Back to Top

    SQL ID: 1rrtf60fmhxkj

    • 1st Capture and Last Capture Snap IDs refer to Snapshot IDs witin the snapshot range
    • SELECT COUNT(*) FROM T1,T2 WHERE T1.OBJECT_ID=T2.OBJECT_ID
    #Plan Hash ValueTotal Elapsed Time(ms)Executions1st Capture Snap IDLast Capture Snap ID
    1427405674713,5641,00020402040


    Back to Top

    Plan 1(PHV: 4274056747)

    • Plan Statistics
    • Execution Plan
    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 NameStatement TotalPer Execution% Snap Total
    Elapsed Time (ms)13,56413.5692.27
    CPU Time (ms)13,38513.3891.76
    Executions1,000  
    Buffer Gets1,051,0751,051.0899.48
    Disk Reads1,0441.0499.90
    Parse Calls10.000.36
    Rows1,0001.00 
    User I/O Wait Time (ms)55  
    Cluster Wait Time (ms)0  
    Application Wait Time (ms)0  
    Concurrency Wait Time (ms)0  
    Invalidations0  
    Version Count1  
    Sharable Mem(KB)14  

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

    Execution Plan

    IdOperationNameRowsBytesCost (%CPU)Time
    0SELECT STATEMENT   296 (100) 
    1   SORT AGGREGATE 126  
    2     HASH JOIN 1002600296 (1)00:00:04
    3       TABLE ACCESS FULLT210013003 (0)00:00:01
    4       TABLE ACCESS FULLT169217878K292 (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 IdSQL Text
    1rrtf60fmhxkjSELECT COUNT(*) FROM T1, T2 WHERE T1.OBJECT_ID=T2.OBJECT_ID


    联系
    我们