场景:某个ETL任务正常情况下每天11:30~12:00之间结束，突然某天后延到13:50，次日15点，然后逐步每天稳定在17:00，同服务器下其他任务也相对变慢。
一、IO层面问题排查
1.查看操作系统资源管理器磁盘活动，确定排名靠前的磁盘IO，定位具体进程，确认是否有异常或不必要的IO进程，此处定位并暂停OGG进程(谨慎停止)，观察一整天数据库性能2.排除系统自动备份任务造成的IO性能资源堵塞，此处排除经过一天观察，性能虽有些微的提升，但不能排除观察时点任务稀疏的特殊性造成的假象。再次经过任务高峰期比对历史性能差异，排除该进程抢占磁盘资源造成的性能问题，进而忽略IO层面问题
二、数据库层面问题排查1.确认等待时间和阻塞原因
查看数据库中过去一天内排在前10位的等待事件及其总等待时间
SELECT * FROM (SELECT EVENT, TOTAL_WAIT_TM, ROUND(TOTAL_WAIT_TM / SUM(TOTAL_WAIT_TM) OVER(ORDER BY 1), 4) * 100 || ‘%’ ZB FROM (SELECT NVL(EVENT, ‘ON CPU’) EVENT, COUNT(*) TOTAL_WAIT_TM FROM V$ACTIVE_SESSION_HISTORY WHERE SAMPLE_TIME > TRUNC(SYSDATE) – 15 / (24 * 60) GROUP BY EVENT ORDER BY 2 DESC)) WHERE ROWNUM <= 10;
image.png
发现有大量的log file switch (checkpoint incomplete)等待事件网上百度的解释:服务器进程正要写入重做记录的时刻，若重做日志文件已满不能继续写入操作，则进程想LGWR请求执行对日志文件的切换。服务器进程由于LGWR，直到日志文件切换结束为止，需等待log file switch completion事件。但日志文件的切换结束时，如果将要投入使用的重做日志文件，还有没有完成的工作，就需要另外等待如下事件。
(1)如果对欲重新使用的重做日志文件尚未结束检查点，进程就应该等待由DBWR来结束检查点。这时，进程将等待log file switch (checkpoint incomplete)事件。(2)如果对欲重新使用的重做日志文件尚未完成对党工作，进程就应该等待ARCH进程来结束归档工作。这时，进程将等待log file switch (archiving needed)事件。这个事件只在归档模式数据库上发生。(3)如果对于欲重新使用的重做日志文件尚未完成对private strand的flush工作，就应该等待这个工作结束。这时，进程将等待log file switch (private strand flush incomplete)事件。
以上的三种等待现象在重做日志文件被循环使用的情况下，将生成许多重做数据，所以在尚未完成工作就重新使用时发生。因此这些等待现象一向是与log file switch completion等待现象一起出现的。准确的说，服务器进程首先等待log file switch completion事件，特殊情况下还会等待log file switch (checkpoint incomplete)、log file switch (archiving needed)和log file switch (private strand flush incomplete)事件。因为名字相似，所以给管理人员带来了相当混乱的几个等待现象，发生原因和解决方式相同。发生原因是比起事务所创建的重做数据，重做日志文件过小。所以解决方法是将重做日志文件的大小调整为足够大。而且，使用Direct load operation或nologging选项对减少重做数据的量也是有帮助的。
解决办法(3选1):1.添加日志组2.按照实际情况增大日志组成员的大小。3.或者同时做1,2此处选择方法2:
–1.查看当前日志组成员 SELECT MEMBER FROM V$LOGFILE; --2.查看当前日志组状态 SELECT GROUP#,MEMBERS,BYTES/1024/1024,STATUS FROM V$LOG; --3.增加日志组 ALTER DATABASE ADD LOGFILE GROUP 4 (‘D:\APP\CLPC_EDW\CLPC_EDW\REDO04.LOG’) SIZE 200M; ALTER DATABASE ADD LOGFILE GROUP 5 (‘D:\APP\CLPC_EDW\CLPC_EDW\REDO05.LOG’) SIZE 200M; ALTER DATABASE ADD LOGFILE GROUP 6 (‘D:\APP\CLPC_EDW\CLPC_EDW\REDO06.LOG’) SIZE 200M; --4.切换到新增的日志组上 ALTER SYSTEM SWITCH LOGFILE;–(可多次执行,直到CURRENT指向新建的日志组) --5.查看当前日志组状态 SELECT GROUP#,MEMBERS,BYTES/1024/1024,STATUS FROM V$LOG; SELECT * FROM V$LOG; 各种状态含义: A.CURRENT指当前的日志文件,在进行实例恢复时是必须的; B.ACTIVE是指活动的非当前日志,在进行实例恢复时会被用到。ACTIVE状态意味着,CHECKPOINT尚未完成,因此该日志文件不能被覆盖。这时也不能DROP掉,应该执行ALTER SYSTEM CHECKPOINT; --强制执行检查点;然后在操作。 C.INACTIVE是非活动日志,在实例恢复时不再需要,但在介质恢复时可能需要。 D.UNUSED表示该日志从未被写入,可能是刚添加的,或RESETLOGS后被重置。 --6.删除旧的日志组 ALTER DATABASE DROP LOGFILE GROUP 1; ALTER DATABASE DROP LOGFILE GROUP 2; ALTER DATABASE DROP LOGFILE GROUP 3; --6.1如果删不掉 ALTER SYSTEM SWITCH LOGFILE; SELECT GROUP#,MEMBERS,BYTES/1024/1024,STATUS FROM V$LOG; ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1; ALTER DATABASE DROP LOGFILE GROUP 1; --7.在操作系统下删除掉REDOLOG 日志文件 备注:可以先新建日志组作为过渡日志组,然后删除原有日志组并修改大小后进行重建。
再次查看数据库中过去15分钟内排在前5位的等待事件及其总等待时间，恢复正常:
image.png
到此，数据库性能突然变慢的症结已解决，后面的均为数据库业务功能层面的优化，时间紧迫可以忽略
2.查看过去15分钟内使用CPU及资源最多的前5位会话
SELECT * FROM (SELECT S.USERNAME, S.MODULE, S.SID, S.SERIAL#, S.EVENT, COUNT() FROM V$ACTIVE_SESSION_HISTORY H, V$SESSION S WHERE H.SESSION_ID = S.SID AND H.SESSION_SERIAL# = S.SERIAL# AND SESSION_STATE = ‘ON CPU’ AND SAMPLE_TIME > SYSDATE - INTERVAL ‘15’ MINUTE GROUP BY S.USERNAME, S.MODULE, S.SID, S.SERIAL#, S.EVENT ORDER BY COUNT() DESC) WHERE ROWNUM <= 5; SELECT DISTINCT EVENT FROM V$SESSION SESSION_STATE列有两个有效的值:ON CPU和WAITING，分别表示会话是活动的还是在等待资源，同样将 SESSION_STATE指定为WAITING值，可以查看在等待资源的前5位会话信息。
3、查看那些业务用户占用最多的资源
SELECT * FROM (SELECT U.USERNAME, H.MODULE, H.SESSION_ID SID, H.SESSION_SERIAL# SERIAL#, COUNT() FROM DBA_HIST_ACTIVE_SESS_HISTORY H, DBA_USERS U WHERE H.USER_ID = U.USER_ID AND SESSION_STATE = ‘ON CPU’ AND (SAMPLE_TIME BETWEEN TO_DATE(‘2018-07-22:00:00:00’, ‘yyyy-mm-dd:hh24:mi:ss’) AND TO_DATE(‘2021-07-22:23:59:59’, ‘yyyy-mm-dd:hh24:mi:ss’)) AND U.USERNAME != ‘SYS’ GROUP BY U.USERNAME, H.MODULE, H.SESSION_ID, H.SESSION_SERIAL# ORDER BY COUNT() DESC) WHERE ROWNUM <= 5; 113 19422 561 141 54513 529 110 20841 515 38 11920 511 12 45449 490
4、定位造成大量资源消耗的对象信息
SELECT * FROM (SELECT O.OBJECT_NAME, O.OBJECT_TYPE, S.EVENT, S.TIME_WAITED, S.CURRENT_OBJ# FROM DBA_HIST_ACTIVE_SESS_HISTORY S, DBA_OBJECTS O WHERE S.SAMPLE_TIME BETWEEN TO_DATE(‘2020-12-26:00:00:00’, ‘yyyy-mm-dd:hh24:mi:ss’) AND TO_DATE(‘2021-07-22:23:59:59’, ‘yyyy-mm-dd:hh24:mi:ss’) AND S.CURRENT_OBJ# = O.OBJECT_ID ORDER BY S.TIME_WAITED DESC); select * from DBA_HIST_ACTIVE_SESS_HISTORY s where S.CURRENT_OBJ#=‘1042886’
5、查看过去15分钟内造成最多数据库等待的sql语句
SELECT ASH.USER_ID, U.USERNAME, S.SQL_TEXT, SUM(ASH.WAIT_TIME + ASH.TIME_WAITED) TTL_WAIT_TIME FROM V$ACTIVE_SESSION_HISTORY ASH, V$SQLAREA S, DBA_USERS U WHERE ASH.SAMPLE_TIME BETWEEN SYSDATE - 15 / (24 * 60) AND SYSDATE AND ASH.SQL_ID = S.SQL_ID AND ASH.USER_ID = U.USER_ID GROUP BY ASH.USER_ID, S.SQL_TEXT, U.USERNAME ORDER BY TTL_WAIT_TIME
6、找出谁持有阻塞锁
SELECT L1.BLOCK, S1.USERNAME || ‘@’ || S1.MACHINE || ’ ( SID=’ || S1.SID || ’ ) is blocking ’ || S2.USERNAME || ‘@’ || S2.MACHINE || ’ ( SID=’ || S2.SID || ’ ) ’ AS BLOCKING_STATUS FROM V$LOCK L1, V$SESSION S1, V$LOCK L2, V$SESSION S2 WHERE S1.SID = L1.SID AND S2.SID = L2.SID AND L1.BLOCK = 1 AND L2.REQUEST > 0 AND L1.ID1 = L2.ID1 AND L2.ID2 = L2.ID2;
7、定位某些有问题的表
1.首先对某些你觉得有问题的表进行分析:ANALYZE TABLE T_RPT_TRUST_GATHER_WEEKLY COMPUTE STATISTICS;
image.png
SELECT table_name,blocks, empty_blocks, num_rowsFROM user_tables order by empty_blocks desc说明:BLOCKS 列代表该表中曾经使用过得数据库块的数目，即水线。EMPTY_BLOCKS 代表分配给该表，但是在水线以上的数据库块，即从来没有使用的数据块。
三、问题总结
1.log file switch (checkpoint incomplete)等待事件:数据库性能突然变慢的症结为REDO日志文件空间不足导致等待2.数据库残留问题包含:数据读直接跳过SGA，从硬盘读数据快到PGA，由于服务器处理PGA中数据块的速度远大于从磁盘读数据块到PGA的速度，通常解决这个问题的方法都是增大PGA的大小。由于系统资源有限，将pga_aggregate_target增大到1G，direct path read temp等待时间还是很明显3.direct path read等待事件:很多表处于高水位，导致表的数据量很大，远大于_small_table_threshold(0.02*2G),所以就走了directpath read
