不懂 2019-07-07
最近一个月因为数据库存储空间问题冒出了很多隐藏的问题,观察时间段都是凌晨引起的,因空间就剩不到100G了,而凌晨产生的归档日志却有100G,所以就触发了一系列问题,下面记录下排查定位的过程~
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导致的?
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分执行一次,有点太频繁了。
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; /
可以看到日志切换次数从250多次切换到现在10次切换,很明显这4个定时任务就是罪魁祸首了。
与业务商定后修改这4个定时任务分别为每周六1点、2点、5点、10点进行。
修改定时任务时间这块前面介绍了,所以这里就不多说了。。。
后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~