[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... |
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 |
Plan 1(PHV: 4274056747)
Back to TopPlan 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 |
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。