ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 第69期 自己手写Oracle AWR 近 3 小时 TOP5 非空闲等待事件采集脚本（二）

第69期 自己手写Oracle AWR 近 3 小时 TOP5 非空闲等待事件采集脚本（二）

原创 Oracle 作者：pxbibm 时间：2026-07-08 16:44:19 0 删除 编辑

在上一篇的文章中《第68期 自己手写Oracle AWR 近 3 小时 TOP5 非空闲等待事件采集脚本（一）》我们详细解释了脚本的编写过程。本次主要给大家分享一下使用的案例。

一、先给执行环境 + 输出样例

1、执行方式

复制整条脚本粘贴到 sqlplus / as sysdba 执行，必须开启输出：
[oracle@zbyl ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 8 16:27:13 2026
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL> set lines 400
set pages 1000
set feedback off
set serverout on
declare
vevent varchar2(100);  
vtime number;
vavgtime number;
vpctwt number;
vwaits number;
vwaitclass varchar2(100);
vbid number ;
veid number ;
vdbid number ;
vinid number ;
startid number;
endid number;
vstarttime varchar2(200);
vendtime varchar2(200);
vdbname varchar2(36);
v_inst_startup date;
begin 
select instance_number,STARTUP_TIME into vinid ,v_inst_startup from v$instance;
select dbid into vdbid from v$database;
--
     begin
         select min(snap_id),max(snap_id) into vbid,veid
         from dba_hist_snapshot
         where END_INTERVAL_TIME between  sysdate - 3/24 
                                     and  sysdate
           and END_INTERVAL_TIME > = v_inst_startup
           and instance_number = vinid
           and dbid = vdbid;
     exception when no_data_found then
         select min(snap_id),max(snap_id) into vbid,veid
         from dba_hist_snapshot
         where END_INTERVAL_TIME between  sysdate -1
                                     and  sysdate -1 - 3/24
           and END_INTERVAL_TIME > = v_inst_startup
           and instance_number = vinid
           and dbid = vdbid;
     end;
     
     select chr(34)||to_char(a.end_interval_time,'yyyy-mm-dd hh24:mi')||chr(34) into vstarttime from dba_hist_snapshot a where snap_id=vbid and instance_number=vinid;
     select chr(34)||to_char(a.end_interval_time,'yyyy-mm-dd hh24:mi')||chr(34) into vendtime from dba_hist_snapshot a where snap_id=veid and instance_number=vinid;
for c1 in (
SELECT EVENT,
       WAITS,
       round(TIME,0) tm,
       round(DECODE(WAITS,
              NULL,
              TO_NUMBER(NULL),
              0,
              TO_NUMBER(NULL),
              TIME / WAITS * 1000),0) AVGWT,
       round(PCTWTT,2) PCTWTT,
       WAIT_CLASS
  FROM (SELECT EVENT, WAITS, TIME, PCTWTT, WAIT_CLASS
          FROM (SELECT E.EVENT_NAME EVENT,
                       E.TOTAL_WAITS_FG - NVL(B.TOTAL_WAITS_FG, 0) WAITS,
                       (E.TIME_WAITED_MICRO_FG - NVL(B.TIME_WAITED_MICRO_FG, 0)) /
                       1000000 TIME,
                       100 *
                       (E.TIME_WAITED_MICRO_FG - NVL(B.TIME_WAITED_MICRO_FG, 0)) /
                       ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = veid
                            AND e.INSTANCE_NUMBER = vinid
                            AND e.STAT_NAME = 'DB time') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = vbid
                            AND b.INSTANCE_NUMBER = vinid
                            AND b.STAT_NAME = 'DB time')) PCTWTT,
                       E.WAIT_CLASS WAIT_CLASS
                  FROM DBA_HIST_SYSTEM_EVENT B, DBA_HIST_SYSTEM_EVENT E
                 WHERE B.SNAP_ID(+) = vbid
                   AND E.SNAP_ID = veid
                   AND B.INSTANCE_NUMBER(+) = vinid
                   AND E.INSTANCE_NUMBER = vinid
                   AND B.EVENT_ID(+) = E.EVENT_ID
                   AND E.TOTAL_WAITS > NVL(B.TOTAL_WAITS, 0)
                   AND E.WAIT_CLASS != 'Idle'
                UNION ALL
                SELECT 'CPU time' EVENT,
                       TO_NUMBER(NULL) WAITS,
                       ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = veid
                            AND e.INSTANCE_NUMBER = vinid
                            AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = vbid
                            AND b.INSTANCE_NUMBER = vinid
                            AND b.STAT_NAME = 'DB CPU')) / 1000000 TIME,
                       100 * ((SELECT sum(value)
                                 FROM DBA_HIST_SYS_TIME_MODEL e
                                WHERE e.SNAP_ID = veid
                                  AND e.INSTANCE_NUMBER = vinid
                                  AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                                 FROM DBA_HIST_SYS_TIME_MODEL b
                                WHERE b.SNAP_ID = vbid
                                  AND b.INSTANCE_NUMBER = vinid
                                  AND b.STAT_NAME = 'DB CPU')) /
                       ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = veid
                            AND e.INSTANCE_NUMBER = vinid
                            AND e.STAT_NAME = 'DB time') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = vbid
                            AND b.INSTANCE_NUMBER = vinid
                            AND b.STAT_NAME = 'DB time')) PCTWTT,
                       NULL WAIT_CLASS
                  from dual
                 WHERE ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = veid
                            AND e.INSTANCE_NUMBER = vinid
                            AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = vbid
                            AND b.INSTANCE_NUMBER = vinid
                            AND b.STAT_NAME = 'DB CPU')) > 0)
         ORDER BY TIME DESC, WAITS DESC)
 WHERE ROWNUM <= 5) loop
  dbms_output.put_line(vstarttime||','||vendtime||','||
                           chr(34)||c1.EVENT||chr(34)||','||
                           chr(34)|| nvl(to_char(c1.WAITS),'---')||chr(34)||','||
                           chr(34)|| nvl(to_char(c1.Tm),'---')||chr(34)||','||
                           chr(34)|| nvl(to_char(c1.AVGWT),'---')||chr(34)||','||
                           chr(34)|| nvl(to_char(c1.PCTWTT),'---')||chr(34)||','||
                           chr(34)|| nvl(to_char(c1.WAIT_CLASS),'---')||chr(34));
end loop;    
     
end;
/
-- 粘贴你完整的那段PLSQL脚本/

2、典型输出 CSV 样例（真实业务案例）

输入的结果如下：
"2026-07-08 09:00","2026-07-08 12:00","CPU time","---",24680,---,72.35,"---"
"2026-07-08 09:00","2026-07-08 12:00","db file sequential read",1268450,6210,5,18.12,"User I/O"
"2026-07-08 09:00","2026-07-08 12:00","latch: cache buffers chains",365200,2140,6,6.24,"Concurrency"
"2026-07-08 09:00","2026-07-08 12:00","db file scattered read",102400,860,8,2.48,"User I/O"
"2026-07-08 09:00","2026-07-08 12:00","enq: TX - row lock contention",12560,120,9,0.81,"Application"

字段含义对照：

快照开始时间,快照结束时间,等待事件名,总等待次数,总等待秒,单次平均等待ms,占DBTime百分比,等待分类

二、4 类真实故障案例（结合脚本输出解读 + 优化方案）

案例 1：CPU time 占比 72%（CPU 瓶颈）

脚本输出片段

"2026-07-08 09:00","2026-07-08 12:00","CPU time","---",24680,---,72.35,"---"

现象解读
  1. 区间内 72.35% 业务时间都耗在 CPU 计算，IO、锁等待很低；
  2. 特征：大量逻辑读、全表扫描、函数运算、隐式转换、排序聚合。

现场场景

业务报表 SQL 未建索引，百万级表全表扫描，每秒上千次执行，数据库 CPU 持续 95%。

优化动作

  1. 用之前 AWR SQL 脚本抓高逻辑读 SQL_ID；
  2. gather_plan_statistics 看执行计划，补联合索引；
  3. 消除 where 条件列函数、隐式转换；
  4. 大分页改用分页键值分页，避免 offset 大偏移量排序。

案例 2：db file sequential read 占 18.12%（单行随机 IO，索引问题）

输出

"2026-07-08 09:00","2026-07-08 12:00","db file sequential read",1268450,6210,5,18.12,"User I/O"

解读

单次等待 5ms，大量索引单块读，物理 IO 高，缓存命中率差。

故障场景

订单表只建单字段索引，查询过滤条件多，索引选择性差，频繁回表；热数据不在 buffer cache，频繁读磁盘。

优化

  1. 创建覆盖复合索引，减少回表；
  2. 调大 db_cache_size，缓存热点数据；
  3. 分区表缩小扫描范围；
  4. 检查索引分裂，定期重建索引。

案例 3：latch: cache buffers chains 并发缓冲区争锁

输出

"2026-07-08 09:00","2026-07-08 12:00","latch: cache buffers chains",365200,2140,6,6.24,"Concurrency"

解读

并发竞争缓冲区链锁，大量会话抢同一个数据块，高并发热点行 / 热点分区。

场景

库存表 update 库存数量，大量并发同一商品 ID 更新，热点块争 latch。

优化

  1. 业务层分库分表、拆分热点商品；
  2. 采用批量更新，减少单行高频 update；
  3. 调整 buffer chain latch 数量（_db_block_hash_latches）；
  4. 使用序列、分区打散热点数据块。

案例 4：enq: TX - row lock contention 行锁等待

输出

"2026-07-08 09:00","2026-07-08 12:00","enq: TX - row lock contention",12560,120,9,0.81,"Application"

解读

应用层锁等待，会话互相阻塞更新同一行。

场景

前端长事务，开启事务后查数据等待用户输入再提交，其他会话更新被阻塞。

优化

  1. 缩短事务，查询与更新拆分，避免长事务持有行锁；
  2. select for update 尽量加 skip locked；
  3. 业务层面乐观锁版本号控制，减少悲观锁竞争。

案例 5：db file scattered read 全表扫描多块读

输出

"2026-07-08 09:00","2026-07-08 12:00","db file scattered read",102400,860,8,2.48,"User I/O"

解读
多块离散读，典型全表扫描 / 大范围索引扫描，一次性读多个数据块。

场景

报表无过滤条件，直接全表查询大表，大量磁盘多块 IO。

优化

添加过滤字段索引，限制查询数据范围，开启分区裁剪。

三、脚本容错场景案例：3 小时内无快照自动切前一日窗口

场景

数据库 06:00 重启，06~09 点无 AWR 快照；执行脚本优先取 sysdate-3/24（09~12 点）查不到快照，触发 no_data_found异常。脚本自动切换窗口： sysdate-1-3/24 ~ sysdate-1，读取昨天同时段快照输出性能数据，不会直接报错中断巡检。输出时间会变成：
"2026-07-07 09:00","2026-07-07 12:00","CPU time","---",22150,---,68.11,"---"

四、落地使用场景案例（运维自动化）

定时巡检 shell 脚本案例

#!/bin/bashlog=/tmp/awr_wait_top5.log
sqlplus -S / as sysdba >> $log <<EOF
set lines 400
set pages 1000
set feedback off
set serverout on
declare
vevent varchar2(100);  
vtime number;
vavgtime number;
vpctwt number;
vwaits number;
vwaitclass varchar2(100);
vbid number ;
veid number ;
vdbid number ;
vinid number ;
startid number;
endid number;
vstarttime varchar2(200);
vendtime varchar2(200);
vdbname varchar2(36);
v_inst_startup date;
begin 
select instance_number,STARTUP_TIME into vinid ,v_inst_startup from v$instance;
select dbid into vdbid from v$database;
--
     begin
         select min(snap_id),max(snap_id) into vbid,veid
         from dba_hist_snapshot
         where END_INTERVAL_TIME between  sysdate - 3/24 
                                     and  sysdate
           and END_INTERVAL_TIME > = v_inst_startup
           and instance_number = vinid
           and dbid = vdbid;
     exception when no_data_found then
         select min(snap_id),max(snap_id) into vbid,veid
         from dba_hist_snapshot
         where END_INTERVAL_TIME between  sysdate -1
                                     and  sysdate -1 - 3/24
           and END_INTERVAL_TIME > = v_inst_startup
           and instance_number = vinid
           and dbid = vdbid;
     end;
     
     select chr(34)||to_char(a.end_interval_time,'yyyy-mm-dd hh24:mi')||chr(34) into vstarttime from dba_hist_snapshot a where snap_id=vbid and instance_number=vinid;
     select chr(34)||to_char(a.end_interval_time,'yyyy-mm-dd hh24:mi')||chr(34) into vendtime from dba_hist_snapshot a where snap_id=veid and instance_number=vinid;
for c1 in (
SELECT EVENT,
       WAITS,
       round(TIME,0) tm,
       round(DECODE(WAITS,
              NULL,
              TO_NUMBER(NULL),
              0,
              TO_NUMBER(NULL),
              TIME / WAITS * 1000),0) AVGWT,
       round(PCTWTT,2) PCTWTT,
       WAIT_CLASS
  FROM (SELECT EVENT, WAITS, TIME, PCTWTT, WAIT_CLASS
          FROM (SELECT E.EVENT_NAME EVENT,
                       E.TOTAL_WAITS_FG - NVL(B.TOTAL_WAITS_FG, 0) WAITS,
                       (E.TIME_WAITED_MICRO_FG - NVL(B.TIME_WAITED_MICRO_FG, 0)) /
                       1000000 TIME,
                       100 *
                       (E.TIME_WAITED_MICRO_FG - NVL(B.TIME_WAITED_MICRO_FG, 0)) /
                       ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = veid
                            AND e.INSTANCE_NUMBER = vinid
                            AND e.STAT_NAME = 'DB time') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = vbid
                            AND b.INSTANCE_NUMBER = vinid
                            AND b.STAT_NAME = 'DB time')) PCTWTT,
                       E.WAIT_CLASS WAIT_CLASS
                  FROM DBA_HIST_SYSTEM_EVENT B, DBA_HIST_SYSTEM_EVENT E
                 WHERE B.SNAP_ID(+) = vbid
                   AND E.SNAP_ID = veid
                   AND B.INSTANCE_NUMBER(+) = vinid
                   AND E.INSTANCE_NUMBER = vinid
                   AND B.EVENT_ID(+) = E.EVENT_ID
                   AND E.TOTAL_WAITS > NVL(B.TOTAL_WAITS, 0)
                   AND E.WAIT_CLASS != 'Idle'
                UNION ALL
                SELECT 'CPU time' EVENT,
                       TO_NUMBER(NULL) WAITS,
                       ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = veid
                            AND e.INSTANCE_NUMBER = vinid
                            AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = vbid
                            AND b.INSTANCE_NUMBER = vinid
                            AND b.STAT_NAME = 'DB CPU')) / 1000000 TIME,
                       100 * ((SELECT sum(value)
                                 FROM DBA_HIST_SYS_TIME_MODEL e
                                WHERE e.SNAP_ID = veid
                                  AND e.INSTANCE_NUMBER = vinid
                                  AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                                 FROM DBA_HIST_SYS_TIME_MODEL b
                                WHERE b.SNAP_ID = vbid
                                  AND b.INSTANCE_NUMBER = vinid
                                  AND b.STAT_NAME = 'DB CPU')) /
                       ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = veid
                            AND e.INSTANCE_NUMBER = vinid
                            AND e.STAT_NAME = 'DB time') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = vbid
                            AND b.INSTANCE_NUMBER = vinid
                            AND b.STAT_NAME = 'DB time')) PCTWTT,
                       NULL WAIT_CLASS
                  from dual
                 WHERE ((SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL e
                          WHERE e.SNAP_ID = veid
                            AND e.INSTANCE_NUMBER = vinid
                            AND e.STAT_NAME = 'DB CPU') -
                       (SELECT sum(value)
                           FROM DBA_HIST_SYS_TIME_MODEL b
                          WHERE b.SNAP_ID = vbid
                            AND b.INSTANCE_NUMBER = vinid
                            AND b.STAT_NAME = 'DB CPU')) > 0)
         ORDER BY TIME DESC, WAITS DESC)
 WHERE ROWNUM <= 5) loop
  dbms_output.put_line(vstarttime||','||vendtime||','||
                           chr(34)||c1.EVENT||chr(34)||','||
                           chr(34)|| nvl(to_char(c1.WAITS),'---')||chr(34)||','||
                           chr(34)|| nvl(to_char(c1.Tm),'---')||chr(34)||','||
                           chr(34)|| nvl(to_char(c1.AVGWT),'---')||chr(34)||','||
                           chr(34)|| nvl(to_char(c1.PCTWTT),'---')||chr(34)||','||
                           chr(34)|| nvl(to_char(c1.WAIT_CLASS),'---')||chr(34));
end loop;    
     
end;
/
EOF# 日志发送到监控平台/导出excel分析cat $log
每日定时执行，自动留存近 3 小时 TOP5 瓶颈等待事件，快速定位每日高峰期性能瓶颈。


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12798004/viewspace-3175467/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 第68期 自己手写Oracle AWR 近 3 小时 TOP5 非空闲等待事件采集脚本（一）
下一篇： 第70期 检查大名鼎鼎的SCN 急速增长漏洞的脚本
请登录后发表评论 登录
全部评论
pxbibm
专家姓名：彭小波
Oracle ACE。ACOUG核心成员，Oracle用户组年轻专家。熟悉Oracle的体系结构，擅长Oracle数据库架构规划、SQL优化、OWI方面性能的优化。曾服务于航天、航空、机车、大型制造行业数据库的维护以及系统开发工作,开发过基于Oracle数据库的大型ERP系统。
  • 博文量
    345
  • 访问量
    2477883

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593