1、查询AWR快照ID之间的快照事件是否是真正需要执行分析的时间段:
col min for a30
col max for a30
SELECT
MIN(begin_interval_time) min,
MAX(end_interval_time) max
FROM dba_hist_snapshot
WHERE snap_id BETWEEN 55355 AND 553556;
2、验证定义的AWR快照间隔确实包含了等待事件的类,其作为整体展示了 AWR 快照时间段内实例中等待次数最高的类。
COL WAIT_CLASS FOR A50
SELECT
wait_class_id,
wait_class,
COUNT(*) count
FROM dba_hist_active_sess_history
WHERE snap_id BETWEEN 55355 AND 55356
GROUP BY wait_class_id, wait_class
ORDER BY 3;
WAIT_CLASS_ID | WAIT_CLASS | COUNT
------------- | -------------------------------------------------- | ----------
2723168908 | Idle | 1
3290255840 | Configuration | 1
4217450380 | Application | 4
2000153315 | Network | 10
1893977003 | Other | 74
3875070507 | Concurrency | 90
3386400367 | Commit | 125
4108307767 | System I/O | 202
3871361733 | Cluster | 577
1740759767 | User I/O | 4632
| | 14325
Elapsed: 00:00:11.39
3、查找等待次数最多的等待类中的事件:
SELECT
event_id,
event,
COUNT(*) cnt
FROM dba_hist_active_sess_history
WHERE snap_id BETWEEN 55355 AND 55356
AND wait_class_id = 1740759767
GROUP BY event_id, event
ORDER BY 3;
EVENT_ID | EVENT | CNT
---------- | ---------------------------------------------------------------- | ----------
885859547 | direct path write | 12
38438084 | direct path write temp | 21
3056446529 | read by other session | 152
861319509 | direct path read temp | 153
506183215 | db file scattered read | 435
834992820 | db file parallel read | 588
3926164927 | direct path read | 1179
2652584166 | db file sequential read | 2092
Elapsed: 00:00:13.31
4、查找正在等待 db file sequential read 等待事件的会话中运行语句的 SQL_ID.
SELECT
sql_id,
COUNT(*) cnt
FROM dba_hist_active_sess_history
WHERE snap_id BETWEEN 55355 AND 55356
AND event_id = 2652584166
GROUP BY sql_id
HAVING COUNT(*) > 1
ORDER BY 2;
ax2nacqqyxhaf | 38
bj3b3hqzr2hc0 | 43
21cxk8fwqv039 | 60
2yz2qk1auggjr | 73
3khf08r7s2r98 | 74
2qhpbkfpz1ug2 | 75
cxgj1uyp0w7rm | 110
61qrb0av4xw9j | 143
176jsa064w28m | 211
5、通过简单的查询语句找出SQL_ID 对应的 SQL文本
SELECT
DISTINCT SQL_TEXT
FROM gv$sqltext
WHERE sql_id = '176jsa064w28m';
文章评论