Oracle High Undo UNEXPIRED utilization due to Autotune retention causing ORA-01555

This is  very old issue where undo extends puck  up high retention ( more then defined undo_retention) and  undo block remains in unexpired state for long time .  This is because of   _undo_autotune which is  by default set to true . 

 _undo_autotune   will try to override the undo_retention parameter. When _undo_autotune is set to True (default value), based on the size of the undo tablespace Oracle will try to keep the undo segments to higher time than defined in undo_retention parameter.

SQL> select TABLESPACE_NAME,STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY tablespace_name,STATUS order by tablespace_name;

TABLESPACE_NAME STATUS SUM(BYTES) COUNT(*)

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

UNDOTBS1 EXPIRED 65536 1
UNDOTBS1 UNEXPIRED 10285809664 3271
UNDOTBS2 EXPIRED 142802944 6
UNDOTBS2 UNEXPIRED 4242735104 642

Suggested Solutions :

One of below solution   can  be applied . 

1)    Setting  “_undo_autotune” = false;

2) Setting   _smu_debug_mode=33554432

With this setting, V$UNDOSTAT.TUNED_UNDORETENTION is not calculated based on a percentage of the fixed size undo tablespace. Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.

Set   at CDB level and monitor the database

3)  Setting “_HIGHTHRESHOLD_UNDORETENTION”=900

Setting    this parameter will limit high value of undo retention.

alter system set “_HIGHTHRESHOLD_UNDORETENTION”=900 SCOPE=spfile;

Views : 

SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED';

select TABLESPACE_NAME,retention from dba_tablespaces where TABLESPACE_NAME like '%UNDO%';

select TABLESPACE_NAME,STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY tablespace_name,STATUS order by tablespace_name;

select max(maxquerylen),max(tuned_undoretention) from DBA_HIST_UNDOSTAT;

SELECT

a.ksppinm "Parameter",

decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",

c.ksppstvl "Instance",

decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",

decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",

decode(p.isdefault,'FALSE','F','TRUE','T') "D",

a.ksppdesc "Description"

FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p

WHERE a.indx = b.indx AND a.indx = c.indx

AND p.name(+) = a.ksppinm

AND lower(a.ksppinm) in ('_smu_debug_mode')

ORDER BY a.ksppinm;

  select segment_name, nvl(sum(act),0) "ACT BYTES",

  nvl(sum(unexp),0) "UNEXP BYTES",

  nvl(sum(exp),0) "EXP BYTES"

  from (select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp

  from dba_undo_extents where status='ACTIVE' group by segment_name

  union

  select segment_name, 00 act, nvl(sum(bytes),0) unexp, 00 exp

  from dba_undo_extents where status='UNEXPIRED' group by segment_name

  union

  select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp

  from dba_undo_extents where status='EXPIRED' group by segment_name)

  group by segment_name

  order by 1

 /

select segment_name, nvl(sum(act),0) "ACT BYTES",

nvl(sum(unexp),0) "UNEXP BYTES",

nvl(sum(exp),0) "EXP BYTES"

from (select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp

from dba_undo_extents where status='ACTIVE' group by segment_name

union

select segment_name, 00 act, nvl(sum(bytes),0) unexp, 00 exp

from dba_undo_extents where status='UNEXPIRED' group by segment_name

union

select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp

from dba_undo_extents where status='EXPIRED' group by segment_name)

group by segment_name

order by 1

/

select tablespace_name,round(sum(case when status = 'UNEXPIRED' then bytes else 0 end) / 1048675,2) unexp_MB ,

round(sum(case when status = 'EXPIRED' then bytes else 0 end) / 1048576,2) exp_MB ,

round(sum(case when status = 'ACTIVE' then bytes else 0 end) / 1048576,2) act_MB

from dba_undo_extents group by tablespace_name

/

SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec FROM v$session s, v$transaction t WHERE s.taddr = t.addr ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

References :

ORA-01555 for long running queries if "_undo_autotune"=true (Doc ID 2784427.1)

High Tuned_undoretention though Undo Datafiles are Autoextend on (Doc ID 2582183.1)

Customer Recommended High Undo Utilization On 19c PDB Database due to Automatic TUNED_UNDORETENTION (Doc ID 2710337.1)

Related Issues :

Bug 31113682 - ORA-1555 FOR LONG RUNNING QUERIES IF "_UNDO_AUTOTUNE"=TRUE

Active Undo Segment Keep Growing And Not Releasing/Reusing Space (Doc ID 2343397.1)

Dump Undo Block :

for our  analysis we  can  try to  dump  one of undo block to get more information . 

select SEGMENT_NAME, sum(bytes)/1024/1024/1024 from DBA_UNDO_EXTENTS where status='UNEXPIRED' and segment_name in (

SELECT a.name from

v$rollname a,

v$rollstat b,

dba_rollback_segs c,

v$transaction d,

v$session e

WHERE

a.usn=b.usn AND

a.name=c.segment_name AND

a.usn=d.xidusn AND

d.addr=e.taddr ) group by SEGMENT_NAME order by 2;

alter session set tracefile_identifier='XID';

alter session set max_dump_file_size = unlimited;

alter system dump undo header '_SYSSMU31_4199861047$';