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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxx> 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@xxxxxxxxx>:
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
