一、数据库报错
业务同事反应进行操作时报错 ORA-30036：无法按8扩展段（在还原表空间 ’UNDOTBS2‘ 中）
赶紧登录数据库一看，数据库UNDO2（32G）表空间爆满无法正常显示，UNDO1（31G）表空间使用率96%，而其中的SYSAUX（31G）表空间达到了91%
首先对两个undo表空间进行扩容，紧急避险
然后是赶紧生成awr报告和ash报告，分别是晚上 22：30 - 23：00 、 23：30 - 24：00
1、关注负载和等待事件
Elapsed: 30.10 (mins)
DB Time: 433.76 (mins)
433.76/30.10=14倍负载
enq: TM - contention
enq: IV - contention
问题sql语句
SQL ordered by Elapsed Time
可以看到有三个较为明显错误的SQL语句
1、执行时间超长且从未结束的存储过程
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN 存储过程名; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
2、执行次数最多的系统表插入语句（多嘴说一句，如果一个AWR报告出现了系统语句排在TOP SQL语句里，多少有些问题，注意挖掘）sys.wri$_optstat_histhead_history
insert into sys.wri$_optstat_histhead_history (obj#, intcol#, savtime, flags, null_cnt, minimum, maximum, distcnt, density, lowval, hival, avgcln, sample_distcnt, sample_size, timestamp#, colname) select h.obj#, h.intcol#, :3, bitand(h.spare2, 7) + 8 + decode(h.cache_cnt, 0, 0, 64), h.null_cnt, h.minimum, h.maximum, h.distcnt, h.density, h.lowval, h.hival, h.avgcln, h.spare1, h.sample_size, h.timestamp#, :4 from sys.hist_head$ h where h.obj# = :1 and h.intcol# = :2
3、执行时间超长的一个业务SQL语句
这个就不写出来来了
其中 SQL ordered by CPU Time 排序第二的有还有它
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN 存储过程名; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
%Total占比是 20.17
SQL ordered by Get s排序第一的有还有它
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN 存储过程名; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
%Total占比是 26.86
由此可以确认的是，数据库爆发故障和它必不可少
事后也确认，这个是一个数据库的统计信息存储过程，一共十个用户，每个用户都有，同一时间在白天启动
二、推导报错原因
为什么统计信息的存储过程会导致UNDO1、UNDO2、SYSAUX表空间会相继撑爆
1、首先确认为什么统计信息会导致数据库UNDO表空间被撑爆
通过这个语句查询占据UNDO表空间的SQL语句信息
SELECT s.sid,
s.serial#,
s.sql_id,
v.usn,
segment_name,
r.status,
v.rssize / 1024 / 1024 mb
FROM dba_rollback_segs r,
gv$rollstat v,
gv$transaction t,
gv$session s
WHERE r.segment_id = v.usn
AND v.usn = t.xidusn
AND t.addr = s.taddr order BY segment_name;
你可以发现的是这个SQL语句占据的UNDO表空间大小是 130M，也提及到了 SQL_ID,可以通过它继续查询，这个只是我截出来最大的
再通过gv$sql来查询语句
select * from gv$sql where sql_id = 'd0xmnp08rhfbg';
insert into sys.wri$_optstat_histhead_history (obj#,intcol#,savtime,flags, null_cnt,minimum,maximum,distcnt,density,lowval,hival,avgcln,sample_distcnt, sample_size,timestamp#,colname) select h.obj#, h.intcol#, :3, bitand(h.spare2,7) + 8 + decode(h.cache_cnt,0,0,64), h.null_cnt, h.minimum, h.maximum, h.distcnt, h.density, h.lowval, h.hival, h.avgcln, h.spare1, h.sample_size, h.timestamp#, :4 from sys.hist_head$ h where h.obj# = :1 and h.intcol# = :2
2、为什么会撑爆SYSAUX表空间
用这个语句查询SYSAUX表空间占用排名较为靠前占用模块
SELECT occupant_name "占用者名",
space_usage_kbytes / 1024 / 1024 "Space Used (GB)",
schema_name "模式名",
move_procedure "移动过程"
FROM gv$sysaux_occupants
where space_usage_kbytes > 1048576 --1G
order by "Space Used (GB)" desc;
SM/AWR排第一，表示AWR信息占用过大
SM/OPSTAT排第一，表示优化器统计信息占用过大
通过后续的多次执行这个SQL语句，SM/OPSTAT在不间断的增长，有一次确认了就是统计信息的问题。
那么为什么收集统计信息会让SYSAUX表空间撑爆？
SM/OPSTAT用于储存历史统计信息，而这些信息是存于sysaux表空间的，如果日常人工所做的分析统计数据不断地增长，而不把历史上的旧的数据删除的话，syayux迟早会爆满。默认情况下，系统会为 SM/OPTSTAT保留31天的记录。
查询了 sys.WRI$_OPTSTAT_HISTHEAD_HISTORY 系统里时间最早为 2023/1/25 0:02:10，截止到今日为1月31号可以发现的是OBJ#（表对象名称）收集次数有些多得离谱，确认收集统计信息存储过程判断方式绝对有问题，因为每个表的收集次数相差太多，而且多得离谱。
select distinct OBJ#,count(*) from sys.WRI$_OPTSTAT_HISTHEAD_HISTORY group by OBJ#;
总共次数
select count(*) from sys.WRI$_OPTSTAT_HISTHEAD_HISTORY;
我将收集统计信息按照执行时间前后进行了对比，32139906/493100=65倍
2023/1/25 0:02:10
select count(*) from sys.WRI$_OPTSTAT_HISTHEAD_HISTORY where to_char(timestamp#,'YYYYMMDD') >= 20230130 ;
32139906
select count(*) from sys.WRI$_OPTSTAT_HISTHEAD_HISTORY where to_char(timestamp#,'YYYYMMDD') < 20230130 ;
493100
3、归档为什么会撑爆？
首先确认归档暴增的这个时间段（切换次数）
SELECT TO_CHAR(FIRST_TIME, 'YYYY-MM-DD') DAY,
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '999') "00",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '999') "01",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '999') "02",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '999') "03",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '999') "04",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '999') "05",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '999') "06",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '999') "07",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '999') "08",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '999') "09",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '999') "10",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '999') "11",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '999') "12",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '999') "13",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '999') "14",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '999') "15",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '999') "16",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '999') "17",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '999') "18",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '999') "19",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '999') "20",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '999') "21",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '999') "22",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '999') "23"
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME, 'YYYY-MM-DD')
ORDER BY 1 DESC;
1月31号下午3点开始
这个是确认暴增的数据量大小
with redo_data as (
SELECT instance_number,
to_date(to_char(redo_date,'DD-MON-YY-HH24:MI'), 'DD-MON-YY-HH24:MI') redo_dt,
trunc(redo_size/(1024 * 1024 * 1024),2) redo_size_gb
FROM (
SELECT dbid, instance_number, redo_date, redo_size , startup_time FROM (
SELECT sysst.dbid,sysst.instance_number, begin_interval_time redo_date, startup_time,
VALUE -
lag (VALUE) OVER
( PARTITION BY sysst.dbid, sysst.instance_number, startup_time
ORDER BY begin_interval_time ,sysst.instance_number
) redo_size
FROM sys.wrh$_sysstat sysst , DBA_HIST_SNAPSHOT snaps
WHERE sysst.stat_id =
( SELECT stat_id FROM sys.wrh$_stat_name WHERE stat_name='redo size' )
AND snaps.snap_id = sysst.snap_id
AND snaps.dbid =sysst.dbid
AND sysst.instance_number = snaps.instance_number
AND snaps.begin_interval_time> sysdate-30
ORDER BY snaps.snap_id )
)
)
select instance_number, redo_dt, redo_size_gb,
sum (redo_size_gb) over (partition by trunc(redo_dt)) total_daily,
trunc(sum (redo_size_gb) over (partition by trunc(redo_dt))/24,2) hourly_rate
from redo_Data
order by redo_dt, instance_number;
大概就是这样，因为当时没截图，后来也爆发归档暴增的问题，这个凑活看看
查看归档暴增的数据库对象，为什么我要取这个时间段，因为数据库本身在凌晨没有业务，而且也过了备份的时间段，按理说不会有太大增长量。
SELECT TO_CHAR(BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24') SNAP_TIME,
DHSO.OBJECT_NAME,
SUM(DB_BLOCK_CHANGES_DELTA) BLOCK_CHANGED
FROM DBA_HIST_SEG_STAT DHSS,
DBA_HIST_SEG_STAT_OBJ DHSO,
DBA_HIST_SNAPSHOT DHS
WHERE DHS.SNAP_ID = DHSS.SNAP_ID
AND DHS.INSTANCE_NUMBER = DHSS.INSTANCE_NUMBER
AND DHSS.OBJ# = DHSO.OBJ#
AND DHSS.DATAOBJ# = DHSO.DATAOBJ#
AND BEGIN_INTERVAL_TIME BETWEEN TO_DATE('2023-02-01 03:00',
'YYYY-MM-DD HH24:MI')
AND
TO_DATE('2023-02-01 05:00', 'YYYY-MM-DD HH24:MI')
GROUP BY TO_CHAR(BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24'),
DHSO.OBJECT_NAME
HAVING SUM(DB_BLOCK_CHANGES_DELTA) > 0
ORDER BY SUM(DB_BLOCK_CHANGES_DELTA) DESC;
三、解决措施
1、迅速将UNDO表空间扩容
alter tablespace UNDOTBS1 add datafile '+DATA' size 31G;
alter tablespace UNDOTBS2 add datafile '+DATA' size 31G;
2、缩短 undo_retention 参数的保留时间改为一个小时，不需要强留太长时间，迅速覆盖
alter system set undo_retention=3600 scope=both sid='*';
3、关闭删除统计信息的定时任务和进程（这点我犯了傻，我关了定时任务，但是没杀存储过程进程，导致一直拖拖拉拉的持续报警）
select JOB,LOG_USER,PRIV_USER,SCHEMA_USER,WHAT from dba_jobs where WHAT = '存储过程名称;'
4、先关闭再停止
begin
dbms_job.broken(28, true, sysdate);
commit;
end;
/
DECLARE
BEGIN
dbms_job.remove(job => 28);
COMMIT;
END;
5、删除存储过程
drop PROCEDURE 用户名.存储过程名;
如果删除卡住，说明还在运行，趁这个机会去查杀存储过程的会话
1、查询正在运行的存储过程
SELECT *
FROM Gv$db_object_cache
WHERE locks > 0
AND pins > 0
AND TYPE = 'PROCEDURE';
2、如果有很多存储过程就用这个来处理，进行查询
SELECT t.* FROM GV$ACCESS t WHERE t.object='存储过程名';
3、通过查出来的sid，再去找 SID和SERIAL#
SELECT SID,SERIAL# FROM GV$SESSION WHERE SID='6666';
4、查杀语句
alter system kill session 'SID,SERIAL#,@节点' immediate;
下边就是立即杀掉节点1的会话
alter system kill session '111,2222,@1' immediate;
四、知识点
1、为什么收集统计信息会导致SYSAUX表空间爆满
每次收集新的统计信息会即时使用，而旧的统计信息会储存在SYSAUX的sys.WRI$_OPTSTAT_HISTGRM_HISTORY和中sys.WRI$_OPTSTAT_HISTHEAD_HISTORY
结论：也就是说旧的统计信息为了做一份备份，删除与否不影响正在使用的统计信息正常使用，如果过于频繁的收集统计信息会造成服务器和数据库高负载和撑大SYSAUX表空间
而且这个表空间满了就无法正常生成AWR和ASH报告
2、UNDO为什么会爆满
数据库为了能够让使用者可以进行回退和闪回，会把所有的增删改记录记录到undo表空间里，如果设置的保留时间未到，而且增删改还在拼命增长，就会撑爆UNDO表空间
如果UNDO表空间撑爆，就没法让数据库日志先行的规则执行，导致任何操作都会卡住
3、归档爆满
当业务和统计信息混在一块而且统计信息的频率极高时，业务的增删改和数据库本身的增删改，就会导致这个结果
4、怎么能够确保一个正常频率不对数据库产生较大后果的收集手段
官方说是2G以上
业务方面可以查询表碎片大的表，这代表着大量的插入和删除
开发来确认哪些表用的多
一个月一次或者两次即可
5、为什么数据库停止操作以后还会撑爆归档
停止定时任务和停止存储过程是两码事，切记！
五、反思
1、别人说没问题的就是没问题吗？
2、在不确认任何新增存储过程的威力时，能大批量和同一时间执行吗？
3、头疼治头脚疼治脚，不进行深究，当鸵鸟，能让事情自动变好吗？
4、复盘是成长最快的方式，好记性不如烂笔头
5、无论如何优先保障数据库的正常运行，然后再去排查问题或者追责，在这个事情当中，应该迅速扩容UNDO和SYSAUX表空间62G，归档设置两个小时删除一次，而不是头疼后续收缩资源
