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
