undo 表空间使用案例分析(UNDOTBS1使用率100%,enq:US-contention,释放undo)
转载 2013年02月20日 16:04:29 12007
问题描述:
近几天发现Oracle10.2.0.4数据库undo tablespace表空间使用率很高(最高时达到100%),报警系统频繁报障undo tablespace表空间使用率过高。
TABLESPACE_NAME TOTAL USED FREE PUSED PFREE
UNDOTBS1 6141 % %
查询所有表空间使用情况(今天发现自己创建的表空间在使用完的情况,这个查询语句或者其它查询语句查不出已经使用完的表空间,只能自己删除掉一些使用该表空间的用户,以便释放自己创建的表空间)
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
问题分析
2.1 业务系统影响
undo tablespace表空间使用率达到100%时,没有业务系统用户反映系统出现表空间不能扩展的报错(ORA-30036),但曾经出现过快照过旧报错(ORA-01555)。
2.2 数据库日志
查看alert_instdb.log报警文件,发现从Tue May 18 10:51:27 2010到Sat May 22 14:55:59 2010,共出现了15次与undo tablespace有关的ORA-01555报错,这些报错都是由同一条SQL语句引起的(SQL ID: 1h54gg6shbrkh),语句执行时间都很长(在20万秒以上),最长执行时间达到355659 sec。
Tue May 18 10:51:27 2010
ORA-01555 caused by SQL statement below (SQL ID: 1h54gg6shbrkh, Query Duration=349634 sec, SCN: 0x0a1b.a93246fa):
Tue May 18 10:51:27 2010
select decode(temp_kp.mc,null,temp_hdhs.mc,temp_kp.mc) as mc,
decode(temp_kp.jzyje,null, 0,temp_kp.jzyje) as jzyje,
decode(temp_kp.jzyfs,null, 0,temp_kp.jzyfs) as jzyfs,
decode(temp_kp.bdcje,null, 0,temp_kp.bdcje) as bdcje,
decode(temp_kp.bdcfs,null, 0,temp_kp.bdcfs) as bdcfs,
decode(temp_kp.ptje,null, 0,temp_kp.ptje) as ptje,
decode(temp_kp.ptfs,null, 0,temp_kp.ptfs) as ptfs,
decode(temp_kp.dkje,null, 0,temp_kp.dkje) as dkje,
decode(temp_kp.dkfs,null, 0,temp_kp.dkfs) as dkfs,
decode(temp_kp.bgdlyje,null, 0,temp_kp.bgdlyje) as bgdlyje,
decode(temp_kp.bgdlyfs,null, 0,temp_kp.bgdlyfs) as bgdlyfs,
decode(temp_kp.gjhyyje,null, 0,temp_kp.gjhyyje) as gjhyyje,
decode(temp_kp.gjhyyfs,null, 0,temp_kp.gjhyyfs) as gjhyyfs,
decode(temp_kp.gjhyycpdlje,null