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