RE: UNDO Space Error

Changes to the volume of data can drive something like this without changing the code.  

IF you have any jobs that have a single commit at completion (regardless of batch size that is being handled), and especially if the input batch sizes have tended to rise over time, those are worth checking.  

IF you find the culprit, the options are:  

1. Changing the code to handle a defined “monolith size” (NOT 1 except under specific conditions of small integral transactions that must be separately committed and verified) and toss a commit once per monolith (and at the end when you underflow the monolith size for the last chunk.
2. Monitor the inbound size of “batches” and hand your transaction processor small enough bites to work with your existing code.

OR it could be something completely different. But this is worth checking. Oracle will handle pretty big chunks, which sometimes causes developers to ignore the fact that nothing handles arbitrarily large chunks.  

Good luck,  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Pap Sent: Saturday, March 20, 2021 2:35 PM
To: Sayan Malakshinov
Cc: Oracle L
Subject: Re: UNDO Space Error  

Thank You Sayan.

I checked using the below query during run time, but don't see any one specific session coming out as one of suspects. But I will try to see it again when the issue occurs. But another doubt I have is , if there exists any historical AWR view matching v$transaction which will help me go back and check the exact snap times(when failure occurred) to see what exact session/process is the culprit and find the cause?

select b.addr,

  a.sid,

  a.username,

  b.xidusn,

  b.used_urec,

  (b.used_ublk*8192)/1024 size_mb

from

  gv$session a,

  gv$transaction b

where

  a.saddr = b.ses_addr

  and a.inst_id=b.inst_id

order by b.used_ublk desc nulls last    

On Sat, Mar 20, 2021 at 11:56 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

Hi Pap,  

Have a look at V$transaction    

--

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE Associate
http://orasql.org  

сб, 20 мар. 2021 г., 21:22 Pap <oracle.developer35_at_gmail.com>:

Hello Listers, Its version 11.2.0.4 of oracle. We suddenly started encountering ORA-30036(ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2') in one of the databases. We don't have any changes done to the code. We already increased the UNDO tablespace size from ~100Gb to ~190GB but still facing the same and this time we want to find the root cause rather than keep on increasing the size of tablespace.

During one of the failures I saw DBA_UNDO_EXTENTS was showing almost all of the extent status as UNEXPIRED. dba_free_space was showing zero space for that tablespace. We have the data files in the undo tablespace set as autoextend ON. And we are using AUTO undo management with UNDO retention set as 900.

I understand there are two types of UNDO noted by oracle , one is UNDO read which the SELECT query sometimes fails with Ora-01555 (but here we are not encountering that). The other one is UNDO generation because of the DML(INSERT/UPDATE/DELETE) and in this case our failure is because of the same UNDO and each time it's mostly INSERT queries failing while doing data load. So is there any way I can track the exact session/sql/user which is generating maximum UNDO from any historical AWR views and also during run time?

How to debug from history and get the cause of this sudden increase in UNDO space consumption? Any other fix other than increasing UNDO space?

Regards

pap    

--

http://www.freelists.org/webpage/oracle-l Received on Sat Mar 20 2021 - 23:24:50 CET