oracle优化sql与awr,Oracle性能优化工具AWRSQL

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

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