[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,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。