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$';

 

Analyzing ORA-4031 In Oracle Database 19c

 

Possible Reason :

1) Too many Child Cursors 

2)  In memory  enabled 

3)  Undersized Sga 

4)  Pinned objects  in shared pool 

5)  Memory Fragmentation 

6) Too many hard parsing ,  

 

Possible Solution : 

1) Use  bind variables 

2)   Set enough  Sga 

References : 

1) https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/improving-rwp-cursor-sharing.html#GUID-971F4652-3950-4662-82DE-713DDEED317C 

2) Troubleshooting: High Version Count Issues (Doc ID 296377.1)

#####################################

Logs to Collect 

#####################################

1) Tfa 

2) Using customized  sql 

2)  Using  srdc_db_ora4031.sql  retrieved from 2232371.1

Use Tfa 

$TFA_HOME/bin/tfactl diagcollect -srdc ora4031

Use your  customized  sql 

spool /tmp/memory_status.txt <<<<<< Call out the database name

set linesize 200

col VALUE for a50

set pagesize 100

set pagesize 100

col Parameter for a50

col "Session Value" for a50

col "Instance Value" for a50

column component format a25

column Initial format 99,999,999,999

column Final format 99,999,999,999

column Started format A25

select name,value from v$system_parameter where name in ( 'memory_max_target', 'memory_target', 'sga_max_size', 'sga_target', 'shared_pool_size', 'db_cache_size', 'large_pool_size', 'java_pool_size', 'pga_aggregate_target', 'workarea_size_policy', 'streams_pool_size' ) ;

select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"

from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c

where a.indx = b.indx and a.indx = c.indx and a.ksppinm in

('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size','__pga_aggregate_target','__sga_target','memory_target');

select * from v$sgastat where pool like '%shared%' order by bytes;

select NAMESPACE,GETHITRATIO,PINHITRATIO,RELOADS,INVALIDATIONS from v$librarycache;

select HS.BEGIN_INTERVAL_TIME,ss.*

from DBA_HIST_SGASTAT ss ,dba_hist_snapshot hs

where pool=’shared pool’ and name=’free memory’

and SS.SNAP_ID=HS.SNAP_ID

and SS.INSTANCE_NUMBER=HS.INSTANCE_NUMBER

and ss.instance_number=1

–and HS.BEGIN_INTERVAL_TIME between to_date(’17-09-2019 13:00:00′,’dd-mm-yyyy hh24:mi:ss’) and to_date(’17-09-2019 15:30:00′,’dd-mm-yyyy hh24:mi:ss’)

order by ss.snap_id desc;

SELECT COMPONENT ,OPER_TYPE,INITIAL_SIZE "Initial",FINAL_SIZE "Final",to_char(start_time,'dd-mon hh24:mi:ss') Started FROM V$SGA_RESIZE_OPS;

SELECT COMPONENT ,OPER_TYPE,INITIAL_SIZE "Initial",FINAL_SIZE "Final",to_char(start_time,'dd-mon hh24:mi:ss') Started FROM V$MEMORY_RESIZE_OPS;

select * from v$pga_target_advice;

spool off;