Handling of high usage of Oracle undo

In the near future , Frequent alarm of database monitoring of a customer UNDO High table space usage ; Maintenance personnel expand the table space by adding data files , Have already put UNDO Expand tablespace to 900GB+, There will still be alarms , Intervene in investigation and handling ~

1. see UNDO Configuration and related parameters

Normal configuration ,UNDO The retention time is 900 second . Corresponding implicit parameters are not configured , Database version 11.2.0.4

2. see UNDO Configuration of data file

The automatic expansion of data files has been turned off

3. see UNDO Segment usage

A lot of UNDO Segment is UNEXPIRED state ,ACTIVE There are very few states , Inquire about V$UNDOSTAT Parameters can be found TUNED_UNDORETENTION Automatically adjust to 20 More than 10000 seconds , Become UNDO Segment status UNEXPIRED Why . This is because UNDO Automatic management features AUM Resulting problems , Reference resources MOS file Automatic Tuning of Undo_retention Causes Space Problems ( file ID 420525.1),Automatic Tuning of Undo_retention Causes Space Problems ( file ID 420525.1) All explain this problem ; Generally, the new online system will consider shutting down alter system set "_undo_autotune" = false; Or set the maximum value of automatic adjustment alter system set "_highthreshold_undoretention" = 10800;. The default is used here , So the automatic adjustment ==TUNED_UNDORETENTION Automatically adjust to 20 More than 10000 seconds , Lead to UNDO Always for segment UNEXPIRED state .

check UNDO Segment status and automatically adjusted retention time command ：

select BEGIN_TIME,
       END_TIME,
       MAXQUERYLEN,
       MAXQUERYSQLID,
       TUNED_UNDORETENTION
  from V$UNDOSTAT  Order By begin_time Desc;


 check UNDO paragraph EXPIRED And so on ：
col "Tablespace_Name" for a15
col "Status" for a10
col "Size(GB)" for 99999
col "SIZE(GB)" for 99999.99
SELECT  seg.tablespace_name "Tablespace_Name", 
  round(ts.bytes/1024/1024/1024) "Size(GB)",
  ue.status "Status", count(*) "Used Extents",
  round(sum(ue.bytes)/1024/1024/1024, 2) "US_SIZE(GB)",
  to_char(round(sum(ue.bytes)/ts.bytes*100, 2), 99.99) "Used Rate(%)"
FROM dba_segments seg, DBA_UNDO_EXTENTS ue,
  (SELECT tablespace_name, sum(bytes) bytes
    FROM dba_data_files GROUP BY tablespace_name) ts
WHERE ue.segment_NAME=seg.segment_NAME and seg.tablespace_name=ts.tablespace_name
GROUP BY seg.tablespace_name, ts.bytes, ue.status
ORDER BY seg.tablespace_name;

4. Solution

The system cannot be shut down due to its importance , Comprehensive consideration , Setting implicit parameters and other methods all have large actions ; Use adjustment UNDO Data file attributes ： ALTER DATABASE DATAFILE '<datafile_flename>' AUTOEXTEND ON MAXSIZE <current_size>; After this setting  V$UNDOSTAT.TUNED_UNDORETENTION No longer based on UNDO Table space usage calculation , But use (MAXQUERYLEN secs + 300) and UNDO_RETENTION The maximum of the two calculations , such UNDO Space is released , as follows ：