undo表空间使用案例分析（UNDOTBS1使用率100%，enq：US
问题描述：
近几天发现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(,null,,) 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, 0,temp_kp.gjhyycpdlje) as gjhyycpdlje,
decode(temp_kp.gjhyycpdlfs,null, 0,temp_kp.gjhyycpdlfs) as gjhyycpdlfs,
decode(temp_kp.gjhwysje,null, 0,temp_kp.gjhwysje) as
2.3 Undo参数
电影爱情片排行榜SQL> show parameter undo
哪种电动车最好NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
2.4 Undo tablespace空间使用情况
怎样写读后感select file_name,autoextensible,bytes/1048576 MB,increment_by,maxbytes/1048576 Max_MB
from dba_data_files
where tablespace_name = 'UNDOTBS1';
FILE_NAME AUTOEXTENSIBLE MB INCREMENT_BY MAX_MB
/dev/rinstdbUNDO_lv NO 1023 0 0
/dev/rinstdbUNDO2_lv NO 3071 0 0
/dev/rinstdbUNDO3_lv NO 2047 0 0
select tablespace_name,status, sum(bytes)/1024/1024 MB, count(*) from dba_undo_extents
group by tablespace_name,status
TABLESPACE_NAME STATUS MB COUNT(*)
TS_UNDO EXPIRED 2.375 38
UNDOTBS1 UNEXPIRED 4344.75 2687
UNDOTBS1 EXPIRED 1796.0625 3146
UNDOTBS1表空间总空间是6141MB，已分配空间达到6140.8125MB，其中UNEXPIRED EXTENT空间占4344.75MB。
2.5 V$UNDOSTAT和DBA_HIST_UNDOSTAT
查询V$UNDOSTAT视图和DBA_HIST_UNDOSTAT数据字典可以看到：
1. 从2010-05-19 PM 10:53:38到2010-5-22 04:53:38 PM，MAXQUERYLEN值都保持在20万秒以上，MAXQUERYLEN最大值是285073s（2010-5-22 04:53:38 PM），
2. TUNED_UNDORETENTION保持在20万秒以上，TUNED_UNDORETENTION最大值是345742s （2010-5-26 09:13:38 AM），TUNED_UNDORETENTION当前值是255484（2010-5-27 02:43:38 PM）
3. SSOLDERRCNT累计值大于0
4. NOSPACEERRCNT一直都是0
从以上数据可以了解到，从2010-05-19 PM 10:53:38到2010-5-22 04:53:38 PM期间出现了多次异常超长时间查询，这些长时间查询导致TUNED_UNDORETENTION参数值变大，这些查询曾经导致ORA-01555报错，但没有导致DML语句出错。
注：从oracle10g开始，oracle提供了Automatic UNDO Retention特性，即oracle会根据实际情况自动调整undo_retention值，以尽可能避免ORA-01555的报错。v$undostat视图中几个主要列的解释：
MAXQUERYLEN Identifies the length of the longest query (in seconds) executed in the instance during the period. You can use this statistic to estimate the proper setting of the UNDO_RETENTION initialization parameter. The length of a query is measured from the cursor open time to the last fetch/execute time of the cursor. Only the length of those cursors that have been fetched/executed during the period are reflected in the view.
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论