一、环境介绍
Oracle Database for linux , 版本为： 11.2.0.4.0。 操作系统版本 Linux 2.6.32-754.12.1.el6.x86_64 #1 SMP Tue Apr 9 14:52:26 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux。 此节点为虚拟化部署的虚拟机环境，操作系统和存储系统均由运维人员划分控制。
二、现象
近一个月以来告警系统时而不时收到活动会数异常，但下一个采集周期(3分钟)后自动恢复。
三、ASH/AWR分析
3.1 查看告警时间点5分钟内活动会话数量情况
select dbid, instance_number, sample_id, sample_time, count(*) session_count from dba_hist_active_sess_history t where sample_time>=to_timestamp('2022-12-06 10:40:00','yyyy-mm-dd hh24:mi:ss') and sample_time< to_timestamp('2022-12-06 10:45:00','yyyy-mm-dd hh24:mi:ss') group by dbid, instance_number, sample_id, sample_time order by dbid, instance_number, sample_time;
3.2 通过上述 精确时间点查找TOP holder、wait事件
select t.dbid, t.sample_id, t.sample_time, t.instance_number, t.event, t.session_state, t.c session_count from (select t.*, rank() over(partition by dbid, instance_number, sample_time order by c desc) r from (select t.*, count(*) over(partition by dbid, instance_number, sample_time, event) c, row_number() over(partition by dbid, instance_number, sample_time, event order by 1) r1 from dba_hist_active_sess_history t where sample_time >to_timestamp('2022-12-06 10:41:50','yyyy-mm-dd hh24:mi:ss') and sample_time < to_timestamp('2022-12-06 10:41:55','yyyy-mm-dd hh24:mi:ss') ) t where r1 = 1) t where r < 5 order by dbid, instance_number, sample_time, r;
3.3 查看等待链路
select level lv, connect_by_isleaf isleaf, connect_by_iscycle iscycle, t.dbid, t.sample_id, t.sample_time, t.instance_number, t.session_id, t.sql_id, t.session_type, t.event, t.session_state, t.blocking_inst_id, t.blocking_session, t.blocking_session_status from (select * from dba_hist_active_sess_history where sample_time >to_timestamp('2022-12-06 10:41:50','yyyy-mm-dd hh24:mi:ss') and sample_time < to_timestamp('2022-12-06 10:41:55','yyyy-mm-dd hh24:mi:ss') ) t start with blocking_session is not null connect by nocycle prior dbid = dbid and prior sample_time = sample_time and prior blocking_session = session_id and prior blocking_session_serial# = session_serial# order siblings by dbid, sample_time;
3.4 堵塞的根源
select t.lv, t.iscycle, t.dbid, t.sample_id, t.sample_time, t.instance_number, t.session_id, t.sql_id, t.session_type, t.event, t.seq#, t.session_state, t.blocking_inst_id, t.blocking_session, t.blocking_session_status, t.c blocking_session_count from (select t.*, row_number() over(partition by dbid, instance_number, sample_time order by c desc) r from (select t.*, count(*) over(partition by dbid, instance_number, sample_time, session_id) c, row_number() over(partition by dbid, instance_number, sample_time, session_id order by 1) r1 from (select level lv, connect_by_isleaf isleaf, connect_by_iscycle iscycle, t.* from dba_hist_active_sess_history t where sample_time >to_timestamp('2022-12-06 10:41:50','yyyy-mm-dd hh24:mi:ss') and sample_time < to_timestamp('2022-12-06 10:41:55','yyyy-mm-dd hh24:mi:ss') start with blocking_session is not null connect by nocycle prior dbid = dbid and prior blocking_inst_id = instance_number and prior blocking_session = session_id and prior blocking_session_serial# = session_serial#) t where t.isleaf = 1) t where r1 = 1) t where r < 3 order by dbid, sample_time, r;
3.5 结果分析
主要是control file sequential read/log file parallel write两种事件，而且均为后台进程。
初步怀疑 与存储性能抖动相关。
3.6 AWR报告
由于snapshot的间隔默认为 60 分钟，所以利用 awrrpt.sql 和 awrddrpt.sql 获取的AWR 报告，没有特别可参考的内容。主要的等待事件为： log file sync 也没有特别消耗资源的业务SQL。
3.7 部署iostat
命令如下：#
nohup iostat -x 10 > /tmp/iostat_chensh.log
日志格式如下：
通过部署iostat 的日志进行定位，在8006781行， 定位到 %util 最大值为
100.03%，且当前的CPU 的%iowait 达
26%。
3.7.1 计算日志中行数对应的分钟数(每10秒采集一次，每次38行)
select 8006781/6/38 from dual; -- 35117
3.7.2 确认活动会话异常的时间点
根据部署iostat的初始时间为 2022-11-12 01:24:52，结合上一步骤获取的分钟数，准确定位异常的时间点： select date_add(date_format(' 2022-11-12 01:24:52','%Y-%m-%d %H:%i:%s'), interval 35117 minute); -- 2022-12-06 10:41:52 此时间点与Oracle数据字典 v$active_session_history 定位的并发会话数的高峰时间点完全吻合。
3.8 结论
经与虚拟机运维人员确认，虚拟化运维查看了记录，近一个月在Oracle所在vsan集群有多台虚机重装系统，重装后会有数据回写，数据回写会抢占IO。
4 部署建议
虚拟化虽然成为潮流， 但对于高并发的Oracle数据库业务应用，应该独立部署在独立而非共享的存储之上。因而，建议将此数据库虚拟节点关联的存储，升级为 独占型磁盘；此外，在迁移任务完成之前尽量避免涉及虚拟机存储池的过频繁的 I/O。
