undo表空间使用率100%的原因查看

来源：这里教程网时间：2026-03-03 17:32:40 作者：

1 在巡检的过程中，多次发现UNDOTBS12表空间使用率为100%,多次查询数据库的alert日志，未发现数据库有undo表空间告警的相关信息。作为一个从业多年的DBA，已经认为这有可能有问题了，需要持续关注。 TABLESPACE_NAME TOT_SIZE TOT_FREE PCT_FREE MAX_FREE CHUNKS_FREE------------------------------ ---------------- ---------------- ---------------- ---------------- ----------------UNDOTBS11 184,320 134,760 73 3,968 1,697UNDOTBS12 184,320 42 0 8 7 2 查看那个用户在使用undo数据块，发现只使用了一个数据库块，当前会话没有影响undo表空间资源耗尽。set linesize 300col username for a20col machine for a20col PROGRAM for a20col name for a30SELECT S.SID, S.USERNAME, S.PROGRAM, S.MACHINE, U.NAME, T.USED_UBLK FROM V$TRANSACTION T, V$ROLLSTAT R, V$ROLLNAME U, V$SESSION S WHERE S.TADDR = T.ADDR AND T.XIDUSN = R.USN AND R.USN = U.USN ORDER BY S.USERNAME; SID USERNAME PROGRAM MACHINE NAME USED_UBLK---------- -------------------- -------------------- -------------------- ------------------------------ --------- 183 TEST_SN JDBC Thin Client yxnewapp6 _SYSSMU1787_2063356590$ 1 3 查看undo表空间UNEXPIRED、EXPIRED、ACTIVE 使用情况,发现过期的Undo数据块有177G，但undo表空间使用率为什么还是100%。 set linesize 300 col TABLESPACE_NAME for a20 SELECT TABLESPACE_NAME,STATUS,TRUNC(SUM(BLOCKS) * 8 / 1024) AS "Size M",COUNT(*) Undo_Extent_Num FROM DBA_UNDO_EXTENTS where TABLESPACE_NAME='UNDOTBS12' GROUP BY TABLESPACE_NAME, STATUS order by TABLESPACE_NAME,STATUS; TABLESPACE_NAME STATUS Size M UNDO_EXTENT_NUM-------------------- --------------------------- ---------- ---------------UNDOTBS12 EXPIRED 177223 44795UNDOTBS12 UNEXPIRED 6275 942 4 查看是否由于undo自动调试，引起的undo不足，经检查，没有自动调试，故认为是正常的 SELECT BEGIN_TIME, END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT,TUNED_UNDORETENTION, MAXCONCURRENCY AS "MAXCON" FROM V$UNDOSTAT; BEGIN_TIME END_TIME UNDOTSN UNDOBLKS TXNCOUNT TUNED_UNDORETENTION MAXCON ------------------ ------------------ ---------- ---------- ---------- ------------------- ---------- 21-JAN-21 30-NOV-21 8 3418760733 2073276409 0 821 5 查询哪些undo段被用于回滚,查询结果为空 select b.name "UNDO Segment Name", b.inst# "Instance ID", b.status$ STATUS, a.ktuxesiz "UNDO Blocks", a.ktuxeusn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq from x$ktuxe a, undo$ b where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%' and a.ktuxeusn = b.us#; 6 查看数据库的版本，为11.2.0.4.171017 (26392168)，故认为不可能是由于PSU引起的, 由于查看Undo相关参数设置是否正常，经检查，认为都是正常的。查看Undo相关的隐含参数NAME VALUE DESCRIB------------------------------ -------------------- ------------------------------------------------------------_flush_undo_after_tx_recovery TRUE if TRUE, flush undo buffers after TX recovery_gc_undo_affinity FALSE if TRUE, enable dynamic undo affinity_gc_undo_block_disk_reads TRUE if TRUE, enable undo block disk reads_undo_block_compression TRUE enable undo block compressionundo_management AUTO instance runs in SMU mode if TRUE, else in RBU modeundo_tablespace UNDOTBS12 use/switch undo tablespace_collect_undo_stats TRUE Collect Statistics v$undostat_undo_debug_mode 0 debug flag for undo related operations_verify_undo_quota FALSE TRUE - verify consistency of undo quota statistics_undo_autotune FALSE enable auto tuning of undo_retention_highthreshold_undoretention 4294967294 high threshold undo_retention in secondsundo_retention 7200 undo retention in seconds_undo_debug_usage 0 invoke undo usage functions for testing_disable_undo_tablespace_alerts FALSE disable tablespace alerts for UNDO tablespaces _smon_undo_seg_rescan_limit 10 limit of SMON continous undo segments re-scan_optimizer_undo_cost_change 11.2.0.4 optimizer undo cost change_optimizer_undo_changes FALSE undo changes to query optimizer_enable_default_undo_threshold TRUE Enable Default Tablespace Utilization Threshold for TEMPORARY Tablespaces 7 查看数据文件是否因为关闭自动扩展引起的undo调试时间变化，根据以上参数及设置，认为没有 SQL> set lines 300 SQL> col file_name for a60 SQL> select file_id,file_name,tablespace_name,bytes/1024/1024,MAXBYTES/1024/1024,autoextensible from dba_data_files where tablespace_name in ('UNDOTBS12') order by 1; FILE_ID FILE_NAME TABLESPACE_NAME BYTES/1024/1024 MAXBYTES/1024/1024 AUTOEXTEN ---------- ------------------------------------------------------------ ------------------------------ --------------- ------------------ --------- 218 +DATA_S/test/datafile/undotbs12.269.1022257191 UNDOTBS12 30720 0 NO 220 +DATA_S/test/datafile/undotbs12.268.1022257243 UNDOTBS12 30720 0 NO 271 +DATAVG/test/datafile/undotbs12.1972.1027500943 UNDOTBS12 30720 0 NO 272 +DATAVG/test/datafile/undotbs12.1973.1027500985 UNDOTBS12 30720 0 NO 344 +DATAVG/test/datafile/undotbs12.2056.1087226619 UNDOTBS12 30720 0 NO 345 +DATAVG/test/datafile/undotbs12.2055.1087226655 UNDOTBS12 30720 0 NO 6 rows selected. 8 查看undo表空间是否为NOGUARANTEE SQL> col tablespace_name for a20 SQL> select tablespace_name,block_size,extent_management,segment_space_management,contents, retention from dba_tablespaces where tablespace_name='UNDOTBS12'; TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT SEGMENT_SPACE_MANA CONTENTS RETENTION -------------------- ---------- ------------------------------ ------------------ --------------------------- --------------------------------- UNDOTBS12 8192 LOCAL MANUAL UNDO NOGUARANTEE 9 通过此视图，验证Undo是否有争用，发现undo header等待事件过长。 SQL> select * from v$waitstat; CLASS COUNT TIME ------------------------------------------------------ ---------- ---------- data block 1054253604 977861564 sort block 0 0 save undo block 0 0 segment header 1256109 339064 save undo header 0 0 free list 0 0 extent map 3536 196 1st level bmb 15523793 1738257 2nd level bmb 3159275 17816519 3rd level bmb 847 102 bitmap block 0 0 bitmap index block 57 0 file header block 1630526 11230885 unused 0 0 system undo header 1225 183 system undo block 23 0 undo header 60728323 13951843 undo block 14025728 760038 18 rows selected. 10 查看回滚段的统计信息，通过统计信息，确定是否有异常。XACTS 为0，代表事务已经提交。不为0，则代表活动的事务。 Ratio的百分比如果过高，则代表undo数据头有争用。 通过此统计信息，认为数据库在undo使用上没有问题的 set linesize 300 col name for a25 col status for a15 SELECT n.name, s.extents, s.rssize, s.optsize, s.hwmsize, s.xacts,s.waits,s.gets,100*(s.waits/s.gets) "Ratio%", s.status FROM v$rollname n, v$rollstat s WHERE n.usn = s.usn; NAME EXTENTS RSSIZE OPTSIZE HWMSIZE XACTS WAITS GETS Ratio% STATUS ------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- SYSTEM 7 450560 450560 0 1072 186417 .57505485 ONLINE _SYSSMU1291_1397830725$ 33 260169728 3860979712 0 50354 287278824 .017527919 ONLINE _SYSSMU1292_252107360$ 181 199942144 2438062080 0 47865 280260848 .017078732 ONLINE _SYSSMU1293_1495173289$ 51 190963712 3893485568 0 45848 295071129 .015537948 ONLINE _SYSSMU1294_3251668230$ 58 214032384 2281824256 0 41967 260615769 .016103016 ONLINE _SYSSMU1295_4122107009$ 34 225566720 4284604416 0 39520 255348602 .015476881 ONLINE _SYSSMU1296_3699510013$ 39 178380800 4231127040 0 36448 270844107 .013457188 ONLINE _SYSSMU1297_3481746686$ 45 228712448 1849483264 0 35460 250105401 .014178022 ONLINE ... _SYSSMU2234_569768215$ 54 277995520 328327168 0 3029 34127401 .008875566 ONLINE _SYSSMU2235_3141883639$ 37 183623680 316137472 0 3370 35601358 .009465931 ONLINE 823 rows selected. 11 以下查询用于通过查询，来确定将undo设置多大比较合适,经验证，认为将undo设置为7G，就满足目前数据库的使用。 SQL> SELECT (UR * (UPS * DBS)) AS "Bytes" 2 FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'), 3 (SELECT undoblks/((end_time-begin_time)*86400) AS UPS 4 FROM v$undostat 5 WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)), 6 (SELECT block_size AS DBS 7 FROM dba_tablespaces 8 WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace')); Bytes ---------- 7558168221 12 仔细考虑Oracle的架构，回收资源是mmon进程，既然alert日志没有相关信息，那么查看mmon进程是否有有用的信息， 果然发现undo 服务处于停滞状态，这估计就是导致undo表空间使用率降不下来的根本原因 *** 2021-11-30 16:49:56.094Unable to schedule a MMON slave at: Auto DBFUS Main Slave action has been temporarily suspended - Slave action had prior policy violations. Unknown return code: 101 *** 2021-12-01 00:50:16.197Unable to schedule a MMON slave at: Auto DBFUS Main Slave action has been temporarily suspended - Slave action had prior policy violations. Unknown return code: 101 查看相关相关参数， _swrf_mmon_dbfus为true，是开启的，为正常。 NAME VALUE DESCRIB------------------------------ -------------------- ------------------------------------------------------------_swrf_mmon_dbfus TRUE Enable/disable SWRF MMON DB Feature Usage_swrf_test_dbfus FALSE Enable/disable DB Feature Usage Testing 13 通过如下信息，可以确定在2020年7月16日后,undo就没有正常的工作过 col version for a20 col description for a30 col FIRST for a20 col LAST for a20 select a.name,a.version,a.detected_usages,to_char(a.first_usage_date,'yyyy-mm-dd hh24:mi:ss') "FIRST", to_char(a.last_sample_date,'yyyy-mm-dd hh24:mi:ss') "LAST",a.description from dba_feature_usage_statistics a where a.detected_usages >0 and a.name like '%Undo%' order by a.name,a.version; NAME VERSION DETECTED_USAGES FIRST LAST DESCRIPTION ------------------------------ -------------------- --------------- -------------------- -------------------- ------------------------------ Automatic Undo Management 11.2.0.4.0 95 2018-09-26 11:29:52 2020-07-16 19:19:24 Oracle automatically manages u ndo data using an UNDO tablesp 14 触发UNDO自动回收的机制为：一为undo表空间100%,二为调整undo的时间，但此系统已经有176G的undo数据块为 过期，故认为通过调整undo保留时间，不一定有效果。 但undo达到100%，按理会触发一次,按照目前系统的情况，认为数据库没有进行回收，故准备通过修改undo表空间阈值， 来触发数据库自动回收undo。 系统Undo表空间阈值默认值 SQL> SELECT OBJECT_NAME,warning_value, critical_value, status FROM dba_thresholds WHERE metrics_name = 'Tablespace Space Usage' AND object_name like 'UNDO%'; OBJECT_NAME WARNING_VALUE CRITICAL_VALUE STATUS -------------------- ------------------------------ -------------------- --------------------- UNDOTBS1 <SYSTEM-GENERATED THRESHOLD> 0 VALID UNDOTBS11 <SYSTEM-GENERATED THRESHOLD> 0 VALID UNDOTBS2 <SYSTEM-GENERATED THRESHOLD> 0 VALID UNDOTBS12 <SYSTEM-GENERATED THRESHOLD> 0 VALID 执行如下语句，设置UNDOTBS12表空间阈值为50%收集一次，70%收集一次， SQL> begin 2 DBMS_SERVER_ALERT.SET_THRESHOLD( 3 metrics_id => dbms_server_alert.tablespace_pct_full, 4 warning_operator => dbms_server_alert.operator_ge, 5 warning_value => '50', 6 critical_operator => dbms_server_alert.operator_ge, 7 critical_value => '70', 8 observation_period => 1, 9 consecutive_occurrences => 1, 10 instance_name => NULL, 11 object_type => dbms_server_alert.object_type_tablespace, 12 object_name => 'UNDOTBS12'); 13 end; 14 / OBJECT_NAME WARNING_VALUE CRITICAL_VALUE STATUS -------------------- ------------------------------ -------------------- --------------------- UNDOTBS1 <SYSTEM-GENERATED THRESHOLD> 0 VALID UNDOTBS11 <SYSTEM-GENERATED THRESHOLD> 0 VALID UNDOTBS2 <SYSTEM-GENERATED THRESHOLD> 0 VALID UNDOTBS12 50 70 VALID 经过调整，发现数据库已经缓慢的收集undo，但Unable to schedule a MMON slave at: Auto DBFUS Main Slave action has been temporarily suspended 还是每过几个小时触发一次。 15 DBFUS不正常，根据多年经验，认为有可能同数据库实例相关视图的统计信息有关，于是查看相关统计信息， 发现从2020年7月20日后就没有进行更新过,而undo统计信息是从2020-7-16日为最后一次。 故认为有可能是数据库实例相关视图统计信息过期，性能下降，导致undo自动回收工作异常。 15.1 查看数据库实例相关统计信息，发现最后一次为2020-7-20日。 OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED ------------------------------ ------------------------------ ---------- ------------- SYS X$KSPPO 131 2020/7/20 SYS X$KSOLSSTAT 22 2020/7/20 SYS X$KSOLSFTS 566126 2020/7/20 SYS X$KSPVLD_VALUES 617 2020/7/20 SYS X$KSPSPFH 1 2020/7/20 SYS X$KSPPI 2914 2020/7/20 SYS X$KSPPSV2 2919 2020/7/20 SYS X$KSPPCV2 2919 2020/7/20 SYS X$KSPPSV 2914 2020/7/20 SYS X$KSPPCV 2914 2020/7/20 SYS X$KSPSPFILE 2932 2020/7/20 SYS X$KSUCPUSTAT 12 2020/7/20 SYS X$KSULL 1 2020/7/20 SYS X$KSUCF 10 2020/7/20 SYS X$KSUINSTSTAT 0 2020/7/20 SYS X$KSQST 320 2020/7/20 SYS X$KSQDN 1 2020/7/20 SYS X$KSQRS 24272 2020/7/20 SYS X$KSQEQTYP 210 2020/7/20 SYS X$KSUCLNDPCC 0 2020/7/20 15.2 手动收集相关统计信息,收集dbms_stats.gather_fixed_objects_stats统计信息，执行几天都没有结束，对会话进行追踪，发现一直卡在X$KSOLSFTS表上, 查询此表的数据，使用select count(1) from X$KSOLSFTS 执行7天都没有结束，初步估计表的数据有可能有上百亿条数据，故导致收集统计信息执行不成功。 SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRATTRNEW'); PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRTSQLPLAN'); PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRATTRNEW') PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRSQLIDTAB'); PL/SQL procedure successfully completed. SQL> exec dbms_stats.gather_fixed_objects_stats; --执行2天没有完成 15.3 经过观察，自从收集统计信息后，Unable to schedule a MMON slave at: Auto DBFUS Main Slave action has been temporarily suspended 告警从每4小时 发生一次，变为到目前为止在没有发生过，故认为Unable to schedule a MMON slave at: Auto DBFUS Main Slave action has been temporarily suspended 告警 确实是由于统计信息过期导致的。 15.4 自从收集统计信息后，发现undo表空间的使用率已经慢慢的降下来,目前剩余79G，使用率为57% 。 TABLESPACE_NAME TOT_SIZE TOT_FREE PCT_FREE MAX_FREE CHUNKS_FREE ------------------------------ ---------------- ---------------- ---------------- ---------------- ---------------- UNDOTBS12 184,320 79,552 43 2,343 6,799 15.5 查询Undo自动管理的JOB,发现在2021-12-12日再次执行过，证明通过收集统计信息， Automatic Undo Management工作已经变为正常，虽然由于X$KSOLSFTS太大, 导致统计信息还有部分过期，但数据库已经缓慢恢复正常。 NAME VERSION DETECTED_USAGES FIRST LAST DESCRIPTION ------------------------------ -------------------- --------------- -------------------- -------------------- ------------------------------ Automatic Undo Management 11.2.0.4.0 97 2018-09-26 11:29:52 2021-12-12 19:17:17 Oracle automatically manages undo data using an UNDO tablespace. 16 本计划使用如下命令，针对Auto-DBFUS Action事件生成相关trace文件，找到明确的信息， 来证明是由于统计信息过期引起的异常，但自从收集统计信息后，DBFUS不在告警了， 故心里那个恨呀，为什么就手动执行了收集统计信息的操作呢，破坏了现场环境， 导致没有明确的信息来证明是由于统计信息过期引起的。 begin dbms_monitor.serv_mod_act_trace_enable(service_name=>'SYS$BACKGROUND', module_name=>'MMON_SLAVE', action_name=>'Auto-DBFUS Action'); end; / begin dbms_monitor.serv_mod_act_trace_disable(service_name=>'SYS$BACKGROUND', module_name=>'MMON_SLAVE', action_name=>'Auto-DBFUS Action'); end; / 17 重启数据库，释放相关资源，收集统计信息，经过验证，undo表空间已经恢复正常。 重启后第3天，表空间的使用情况 SQL> select a.tablespace_name,sum(a.tots) Tot_Size, sum(a.sumb) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free, sum(a.largest) Max_Free,sum(a.chunks) Chunks_Free from ( select tablespace_name,0 tots,sum(bytes/1024/1024) sumb, max(bytes/1024/1024) largest,count(*) chunks from dba_free_space a group by tablespace_name union select tablespace_name,sum(bytes/1024/1024) tots,0,0,0 from dba_data_files group by tablespace_name) a group by a.tablespace_name order by Pct_Free desc; TABLESPACE_NAME TOT_SIZE TOT_FREE PCT_FREE MAX_FREE CHUNKS_FREE ------------------------------ ---------------- ---------------- ---------------- ---------------- ---------------- UNDOTBS11 184,320 172,153 93 3,968 1,686 UNDOTBS12 184,320 149,383 81 1,381 2,623 9 rows selected.