Oracle性能优化工具AWRSQL

时间：2017-07-29 来源：

　　在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] view plain copy print?

　　@?/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

　　(以上内容摘于网络，如有侵权，请告之，将第一时间删除)