概述

最近一个月因为数据库存储空间问题冒出了很多隐藏的问题，观察时间段都是凌晨引起的，因空间就剩不到100G了，而凌晨产生的归档日志却有100G，所以就触发了一系列问题，下面记录下排查定位的过程~

1、观察日志切换次数

SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22,
 SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
 COUNT(*) TOTAL
FROM v$log_history a
WHERE first_time>=to_char(sysdate-10)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;

可以看到基本在1点和2点 日志切换都会达到130次左右，本来以为是接口平台每天定时这时间段跑接口导致，但是查看接口平台基本是每隔十几分钟或1小时就跑，很少有固定在1-2点左右时间段去跑的，那么具体问题在哪呢？是哪些sql导致的？

2、观察0点到8点事务量

select b.*
 from (
select t.sql_id,
 t.module,
 (select to_char(dbms_lob.substr(sql_text, 20, 1)) from sys.wrh$_sqltext where sql_id = t.sql_id and rownum <=1) sql_text1,
 to_char(s.begin_interval_time, 'yyyy-mm-dd hh24') begin_interval_time1,
 to_char(t.rows_processed_delta, '9999,9999,9999') rows_processed_delta, 
 round((ratio_to_report(rows_processed_delta) over ())*100,2) rtr_row,
 t.executions_delta,
 to_char(t.rows_processed_delta/(t.executions_delta+1),'9999,999999') row_exec 
 from sys.wrh$_sqlstat t, wrm$_snapshot s
 where t.dbid = s.dbid
 and t.snap_id = s.snap_id
 and s.begin_interval_time >= to_date(to_char(trunc(sysdate),'yyyy-mm-dd') ||'00:00:00','yyyy-mm-dd hh24:mi:ss')
 and s.begin_interval_time <= to_date(to_char(trunc(sysdate) ,'yyyy-mm-dd')||'08:00:00','yyyy-mm-dd hh24:mi:ss')
 order by t.snap_id desc
 ) b
 where (upper(sql_text1) like '%INSERT%' or
 upper(sql_text1) like '%DELETE%' or
 upper(sql_text1) like '%UPDATE%')
order by rows_processed_delta

可以观察到有4个数据库的定时任务产生的事务量是最多的，这四个定时任务主要是对一些大表归档，只保留最近30天的数据，但是定时却是每天的1点30分执行一次，有点太频繁了。

3、禁止4个定时任务

begin
 dbms_scheduler.disable('SYSTEM.ARCHIVE_120_TABLES');
end;
/
begin
 dbms_scheduler.disable('SYSTEM.ARCHIVE_LOG_TABLES');
end;
/
begin
 dbms_scheduler.disable('SYSTEM.AUTO_ARCHIVE_DPCA');
end;
/
begin
 dbms_scheduler.disable('SYSTEM.AUTO_ARCHIVE_202');
end;
/

4、隔天观察日志切换次数及事务量

可以看到日志切换次数从250多次切换到现在10次切换，很明显这4个定时任务就是罪魁祸首了。

5、调整策略

与业务商定后修改这4个定时任务分别为每周六1点、2点、5点、10点进行。

修改定时任务时间这块前面介绍了，所以这里就不多说了。。。

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~