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

denglj

文档课题：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;