快速查找ORACLE 数据库性能瓶颈

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';
点赞

发表评论

电子邮件地址不会被公开。必填项已用 * 标注