在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
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 |