数据库日常学习案例20240117--oracle数据库性能缓慢分析过程

问题背景:

应用运维报障说最近两天业务数据入库和表查询都变得很慢，需要排查一下数据库的性能问题

问题分析：

登录到服务器上，通过TOP命令快速看了一下，服务器整体的CPU使用%usr不算特别高，但%wa IO等待很高，怀疑有可能是数据库存在大量的IO操作语句导致服务器IO负载升高

查询数据库的负载dbtime时间，可以看到数据库的负载明显变高，平常只有几百的dbtime值，从2024年1月1日13点左右之后开始飙升，最高达到7000+

DB_TIME可以作为监控数据库性能的重要参数

即DB TIME/ ELAPSED 所得结果与Core比较，当大于CPU核心数，则代表数据库比较繁忙，Loading较高
当小于CPU核心数，则代表数据库负荷正常

select to_char(end_interval_time,'yyyy-mm-dd hh24:mi') v_date,a.instance_number inst_id,round((b.value-a.value)/1000000/60/4,2) v_dbtime,
round((b.value-a.value)/1000000/60/4/(64*15/100),2)||'%' db_cpu_pct
from dba_hist_sys_time_model a,dba_hist_sys_time_model b ,dba_hist_snapshot c
where a.stat_name='DB time' and b.stat_name='DB time' 
      and a.dbid=b.dbid and a.dbid=(select dbid from v$database)
      and a.instance_number= b.instance_number and c.instance_number=b.instance_number
      and a.snap_id=c.snap_id
      and b.snap_id=c.snap_id+1 
      and C.END_INTERVAL_TIME>=sysdate-7
      and a.instance_number=&inst_id
      order by v_dbtime desc; 

对比1月1日13点前后的等待事件情况，发现13点之后的IO等待事件db file sequential read以及direct path read等待事件明显多了几个数量级，等待事件的类型与看到的服务器IO等待负载升高匹配，可以确认数据库肯定存在大量的IO操作

select event,count(*)from DBA_HIST_ACTIVE_SESS_HISTORY awhere  to_char(sample_time,'yyyymmdd hh24:mi:ss')>='20240101 13:00:00'and to_char(sample_time,'yyyymmdd hh24:mi:ss')<='20240102 16:00:00'group by event order by 2;

正常时间段

性能缓慢，IO等待高时间段

select to_char(sample_time,'yyyymmdd hh24'),event,count(*)from DBA_HIST_ACTIVE_SESS_HISTORY awhere   event in('direct path read','db file sequential read')group by to_char(sample_time,'yyyymmdd hh24'),eventorder by 1;

分析IO等待事件引发的sql语句,可以看到TOP主要有4条语句sql:8aq5bt0k6jb4d、3s559a2uc2xk0、3nd18t4tmv0mz、25dy6q436ch3k

大部分为统计信息过久，全表扫描导致大量的IO行为的等待事件。

查看统计信息job的执行记录，可以看到2024年1月1日的统计信息收集在晚上22点有正常的开始执行，但是最后统计信息收集的job由于4个小时的执行窗口时间已到，job被迫暂停了（REASON="Stop job called because associated window was closed"），也就是任务有跑，但没收集完成

注:周一到周五默认统计信息收集窗口4个小时，周六周日默认统计信息收集窗口20个小时

通常统计信息没有在4个小时窗口执行完成的可能原因有1 数据库要收集的表数据量过大 2 数据库的性能出现问题，导致收集缓慢 3 统计信息收集的并行度不合理，导致收集速度过慢 4 Oracle的bug，结合统计信息收集的历史完成时间都在2小时以内以及收集时间段存在IO负载高的问题，判断统计信息收集还是受到数据库的性能下降所影响