undo表空间使用率

undo表空间是Oracle数据库非常重要的表空间，它的使用率也是DBA关注的重点，但是在繁忙的生产系统中，很容易看到undo表空间使用率非常高的情况，甚至达到100%。那么，undo表空间大小应该设置多大？undo块是怎样进行分配的？undo表空间使用率很高的时候应该怎么处理？本文针对上述问题进行说明。一、undo表空间大小设置
1、首先根据数据库和业务的情况，先预设一个undo 表空间的大小；
2、待数据库运行一段时间后，可以按照如下方法估算下合理的undo表空间大小：
计算公式：UR*（USP*DBS）
UR表示undo_retention参数值
UPS表示每秒产生的undo块数量
DBS表示数据库块大小

SELECT （UR * （UPS * DBS）） AS "Bytes"
FROM （select max（tuned_undoretention） AS UR from v$undostat）,
（SELECT undoblks / （（end_time - begin_time） * 86400） AS UPS
FROM v$undostat
WHERE undoblks = （SELECT MAX（undoblks） FROM v$undostat））,
（SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name =
（SELECT UPPER（value）
FROM v$parameter
WHERE name = "undo_tablespace"））;

二、自动管理的undo块的分配算法1、如果当前extent中还有空闲块，在需要空间时会继续使用本extent中的下一个空闲块；2、当前extent使用完后，如果下一个extent是expired，就跳转到下一个extent的第一个数据块；3、如果下一个extent不是expired，就从undo表空间申请空间，如果undo表空间中存在空闲的空间，就分配新的extent加入到undosegment，然后跳转到新的undo extent的第一个数据块；4、如果undo表空间没有空闲的空间，就从offine的undo segment中偷取（steal）expired的extent，将offine的undo segment中的expired的 extent分配给当前的undo segment，并跳转到新加入的extent的第一个数据块；5、如果在offine的undo segment中没有expired的extent，就从online的undo segment中偷取expired的extent加入到当前undosegment，并跳转到新加入的extent的第一个空闲块；6、如果online的segment中没有expired的extent，就扩展undo表空间数据文件（如果开启了自动扩展），添加新的extent到当前的undo segment；7、如果undo表空间数据文件不能扩展，调低10%的retention值，然后偷取现在变为expired的undo extent；8、从任意一个offine的undo segment中偷取unexpired的extent；9、尝试重用当前段中unexpired的extent，如果所有的extent处于currently busy（事务未提交），转入到第10步；10、尝试重用任意一个online的undo segment中的unexpired extent；11、如果上述所有的步骤都失败，抛出ORA-30036 unable to extend segment by %s in undo tablespace "%s"。从以上undo块的分配算法可以看出，当undo表空间的使用率很高的时候，不一定就要增加undo表空间的大小，因为oracle会重用expired的extent或者unexpired的extent。即使undo表空间使用率到达100%，数据库也不一定会报错。undo表空间的使用率可以采用下面的方法查询：

SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / （1024 * 1024） "Size （MB）",
SUM（fs.bytes） / （1024 * 1024） "Free （MB）",
Nvl（Round（SUM（fs.bytes） * 100 / df.bytes）,1） "% Free",
Round（（df.bytes - SUM（fs.bytes）） * 100 / df.bytes） "% Used"
FROM dba_free_space fs,
（SELECT tablespace_name,SUM（bytes） bytes
FROM dba_data_files
GROUP BY tablespace_name） df
WHERE fs.tablespace_name （+） = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / （1024 * 1024）,
SUM（df.bytes_free） / （1024 * 1024）,
Nvl（Round（（SUM（fs.bytes） - df.bytes_used） * 100 / fs.bytes）, 1）,
Round（（SUM（fs.bytes） - df.bytes_free） * 100 / fs.bytes）
FROM dba_temp_files fs,
（SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used） df
WHERE fs.tablespace_name （+） = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

但是，如果undo表空间使用率非常高，且遭遇了ORA-01555或者ORA-30036，就需要进行分析。首先要分析undo表空间的大小设置是否合理，可以使用上文中估算undo表空间合理大小的算法进行计算。如果undo表空间已经足够大，但是还是遭遇ORA-01555或者ORA-30036，就需要从以下几个方面进行排查：1、对于ORA-01555需要查看查询语句的运行时间是不是太长，undo_retention设置是否过小；2、对于ORA-30036需要排查事务是否过大，undo_retention设置是否过大；3、对于ORA-30036还需要关注以下信息：--查看是否有长时间被占用的undo块

select begin_time,
end_time,
undoblks,
tuned_undoretention,
maxquerylen,
maxqueryid
from v$undostat;
如果有长时间被占用的undo块，在10g版本中需要关注Bug 5387030
--查询正在被使用的undo表空间的比例
SELECT
（（SELECT （NVL（SUM（bytes）,0））
FROM dba_undo_extents
WHERE tablespace_name="<UNDO_TABLESPACE_NAME>"
AND status IN （"ACTIVE","UNEXPIRED"）） * 100）/
（SELECT SUM（bytes）
FROM dba_data_files
WHERE tablespace_name="<UNDO_TABLESPACE_NAME>"）
"PCT_INUSE"
FROM dual;
--查看分配给undo表空间的extent的状态
SELECT DISTINCT STATUS, SUM（BYTES）/1024/1024, COUNT（*）
FROM DBA_UNDO_EXTENTS where tablespace_name = "UNDOTBS1" GROUP BY STATUS;
--查询正在使用undo段的事务及使用的undo表空间的大小
select start_time,
username,
s.MACHINE,
s.OSUSER,
r.name,
ubafil,
ubablk,
t.status,
（used_ublk * p.value） / 1024 blk, --使用undo表空间的大小
used_urec,
s1.SQL_ID,
s1.SQL_TEXT
from v$transaction t, v$rollname r, v$session s, v$parameter p,v$sql s1
where xidusn = usn
and s.saddr = t.ses_addr
and p.name = "db_block_size"
and s.SADDR=s1.ADDRESS（+）
order by 1;

对于ORA-01555，最优的解决办法是缩短查询语句的运行时间，需要对sql进行优化，对于ORA-30036，尽量将大事务进行拆分，分批提交。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址