UNDO表空间使用率过高的处理方式

文档课题：UNDO表空间使用率过高的处理方式.

应用场景：UNDO表空间使用率过高，想必很多数据库管理员都遇到过此问题，接下来了解undo表空间的相关知识.

1、undo块使用情况

SQL>select status,sum(bytes/1024/1024) from dba_undo_extents group by status;

STATUS SUM(BYTES/1024/1024)

--------------------------------------

EXPIRED 21.25

UNEXPIRED 10

2、当前undo空闲

SQL> selecttablespace_name,sum(bytes/1024/1024) from dba_free_space wheretablespace_name='UNDOTBS1' group by tablespace_name

TABLESPACE_NAME SUM(BYTES/1024/1024)

----------------------------------------

UNDOTBS1 157.75

3、表空间情况

SQL>set line 200

SQL>col GRAPH for a50

SQL>col tablespace for a20

SQL> select total.ts tablespace,

total.mb total_mb,

to_char(trunc(total.mb -nvl(free.mb,0),2),'fm999990.0999') used_MB,

NVL(free.mb,0) free_mb,

DECODE(total.mb,NULL,0,NVL(ROUND((total.mb -free.mb)/(total.mb)*100,2),100)) pct_used,

CASE WHEN (total.mb IS NULL) THEN'['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'

ELSE '['|| DECODE(free.mb,

null,'XXXXXXXXXXXXXXXXXXXX',

NVL(RPAD(LPAD('X',trunc((100-ROUND( (free.mb)/(total.mb) * 100,2))/5),'X'),20,'-'),

'--------------------'))||']'

END as GRAPH

from

(select tablespace_name ts,sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,

(select tablespace_name ts,sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free

where total.ts=free.ts(+)

UNION ALL

select t3.tn tablespace,

t3.bytes total_mb,

to_char(trunc(t3.bytes -nvl(t4.bytes,0),2),'fm999990.0999') used_mb,

t4.bytes free_mb,

ROUND(((to_char(trunc(t3.bytes -nvl(t4.bytes,0),2),'fm999990.0999'))/t3.bytes)*100,2) pct_used,

CASE WHEN (t3.bytes ISNULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'

ELSE '['|| DECODE(t4.bytes,

null,'XXXXXXXXXXXXXXXXXXXX',

NVL(RPAD(LPAD('X',trunc((100-ROUND( (t4.bytes)/(t3.bytes) * 100,2))/5),'X'),20,'-'),

'--------------------'))||']' end asgraph

FROM(select tablespace_name tn,sum(bytes)/1024/1024 bytes from dba_temp_files groupby tablespace_name) t3,

(select tablespace_name tn,sum(bytes_cached)/1024/1024bytes from v$temp_extent_pool group by tablespace_name) t4

wheret3.tn = t4.tn(+)

order by5 Desc;

TABLESPACE TOTAL_MB USED_MB FREE_MB PCT_USED GRAPH

------------------------------ ---------- ---------- ------------------------------------------------------------

TEMP 97 97 0 100 [XXXXXXXXXXXXXXXXXXXX]

SYSTEM 1080 1065.625 14.375 98.67[XXXXXXXXXXXXXXXXXXX-]

SYSAUX 1400 1303 97 93.07 [XXXXXXXXXXXXXXXXXX--]

USERS 18.75 6.4375 12.3125 34.33[XXXXXX--------------]

UNDOTBS1 190 32.25 157.75 16.97[XXX-----------------]

TSP_ORDADM1 200 4 196 2 [--------------------]

TEST 150 2.625 147.375 1.75[--------------------]

TSP_ORDADM 800 1 799 .13 [--------------------]

已选择 8 行.

4、undo_retention自动调整

undo表空间使用率跟undo_retention的值有关，保留时间越长unexpired越大，进而undo表空间就越大.从oracle10g r2开始，oracle默认是开启undo表空间的自动调整功能，目的在于避免频繁的ora-01555告警发生.意思是oracle会忽略undo_retention参数设置的阀值，而是根据undo表空间的大小和使用率来自动调整undo信息的保留时间.

通过视图v$undostat.tuned_undoretention可查询该值是否有被自动调整，以下查询语句：

SQL>select to_char(begin_time,'yyyy-mm-dd hh24:mi:ss'),to_char(end_time,'yyyy-mm-ddhh24:mi:ss'),tuned_undoretention from v$undostat

TO_CHAR(BEGIN_TIME,'YYYY-MM-DDHH24:MI:TO_CHAR(END_TIME,'YYYY-MM-DDHH24:MI:SS TUNED_UNDORETENTION

---------------------------------------------------------------------------- -------------------

2022-04-2110:10:01 2022-04-2110:20:01 900

2022-04-2110:00:01 2022-04-2110:10:01 900

2022-04-2109:50:01 2022-04-2110:00:01 2334

2022-04-2109:40:01 2022-04-2109:50:01 2330

2022-04-2109:30:01 2022-04-21 09:40:01 1726

2022-04-2109:20:01 2022-04-2109:30:01 1121

2022-04-2109:10:01 2022-04-2109:20:01 900

2022-04-2109:00:01 2022-04-2109:10:01 900

已选择 33 行.

结论：以上可看出，undo_retention值有被自动调整到2334s.

5、相关知识

undo被使用顺序.

事务会优先使用undo空闲空间、过期状态(expired)的undo区，然后才会尝试扩展表空间数据文件.在以上步骤都不能获得undo表空间后，才会去使用未过期(unexpired)的undo区.

oracle主要通过以下方式回收undoextend，使其成为undo表空间的空闲空间：

a、smon进程周期性地回收过期状态的undoextent，对于offline的undo段，会回收所有过期的undoextent.对于online的undo段，回收时会根据undo_retention时间和undo块的使用率来确定回收的数量.undo_retention越大，能回收的undo空间就越小.

b、进程在给事务分配undo块时，会回收多余过期状态的undoextent.因此，如果系统未过期的undo extent越多，这部分undo块就不会返回给undo表空间而成为空闲空间，导致undo表空间已分配空间一直很多，同时使用率也会显示很高.

6、解决方案

为避免undo表空间使用率过高，可采取以下措施：

a、降低undo_retention值

b、增加undo表空间数据文件

c、修改隐藏参数_undo_autotune=false来禁用自动undo保留调整功能

alter system set"_undo_autotune"=false scope=both sid='*';

d、设置隐藏参数_sum_debug_mode

alter system set "_sum_debug_mode"=33554432;

e、若undo_retention参数值在10800以下，建议设置为10800.

alter system set undo_retention=10800;