Undo space management in 10G – bug 5387030.8

Bug 5387030.8 causes undo segments to remain as unexpired far longer than the undo_retention time that has been set. It is in all versions of 10G up to the 10.2.0.4 patch. Below is a scenario that can be used to see if the problem is applicable to your environment

–Standard 10g database – undo retention set to 900 seconds

–Show undo information – the last column tuned_undoretention is the interesting one

prompt ++++++++++++++++++++
prompt Set to show details for the last 2 hours only
prompt ++++++++++++++++++++

set pages 60

select
TO_CHAR(Begin_Time,’DD-MON-YY HH24:MI’) c1,
TO_CHAR(End_Time,’DD-MON-YY HH24:MI’) c2,
SUM(Undoblks) c3,
SUM(Txncount) c4,
MAX(Maxquerylen) c5,
MAX(Maxconcurrency) c6,
tuned_undoretention c7
from
v$undostat
where
Begin_Time > sysdate-2/24
group by
TO_CHAR(Begin_Time,’DD-MON-YY HH24:MI’),
TO_CHAR(End_Time,’DD-MON-YY HH24:MI’),
tuned_undoretention
order by TO_CHAR(Begin_Time,’DD-MON-YY HH24:MI’)

SQL> @undo_rpt
++++++++++++++++++++
Set to show details for the last 2 hours only
++++++++++++++++++++

Total Total Highest
Undo Number of Longest Concurrent Undo
Start End Blocks Transactions Query Transaction Retention
Time Time Used Executed (sec) Count (sec)
————— ————— ——– ———— ——- ———– ———
21-FEB-08 08:22 21-FEB-08 08:32 0 1 0 0 210,816
21-FEB-08 08:32 21-FEB-08 08:42 0 5 0 0 212,596
21-FEB-08 08:42 21-FEB-08 08:52 1 4 0 3 214,355
21-FEB-08 08:52 21-FEB-08 09:02 24 33 0 3 215,674
21-FEB-08 09:02 21-FEB-08 09:12 0 2 0 0 217,442
21-FEB-08 09:12 21-FEB-08 09:22 0 54 0 0 219,219
21-FEB-08 09:22 21-FEB-08 09:32 0 2 0 0 220,987
21-FEB-08 09:32 21-FEB-08 09:42 0 6 0 0 222,755
21-FEB-08 09:42 21-FEB-08 09:52 0 1 0 0 224,532
21-FEB-08 09:52 21-FEB-08 10:02 23 32 0 2 226,027
21-FEB-08 10:02 21-FEB-08 10:12 0 3 0 0 227,793
21-FEB-08 10:12 21-FEB-08 10:14 0 0 0 0 227,968

12 rows selected.

;
– Set a undo_retention period of 60 secs

Alter system set undo_retention =60;

–Monitor unexpired undo segments

column TSPACE format a20
col status format a20
SELECT tablespace_name TSPACE, status, COUNT(*) AS HOW_MANY
FROM dba_undo_extents
GROUP BY tablespace_name, status
/

SQL> /

TSPACE STATUS HOW_MANY
——————– ——————– ———-
UNDO EXPIRED 3
UNDO UNEXPIRED 302

–create a table and fill it with data to generate undo usage

SQL> /

TSPACE STATUS HOW_MANY
——————– ——————– ———-
UNDO EXPIRED 1
UNDO ACTIVE 27
UNDO UNEXPIRED 307

SQL> commit;

Commit complete.
TSPACE STATUS HOW_MANY
——————– ——————– ———-
UNDO EXPIRED 1
UNDO UNEXPIRED 334

– wait ~> 60 seconds (5-10 minutes to be certain) and the unexpired extents should not have been released
– monitor undo figures with the script above

SQL> alter system set “_smu_debug_mode” = 33554432;

System altered.

SQL> @undo_rpt
++++++++++++++++++++
Set to show details for the last 2 hours only
++++++++++++++++++++

Total Total Highest
Undo Number of Longest Concurrent Undo
Start End Blocks Transactions Query Transaction Retention
Time Time Used Executed (sec) Count (sec)
————— ————— ——– ———— ——- ———– ———
21-FEB-08 08:42 21-FEB-08 08:52 1 4 0 3 214,355
21-FEB-08 08:52 21-FEB-08 09:02 24 33 0 3 215,674
21-FEB-08 09:02 21-FEB-08 09:12 0 2 0 0 217,442
21-FEB-08 09:12 21-FEB-08 09:22 0 54 0 0 219,219
21-FEB-08 09:22 21-FEB-08 09:32 0 2 0 0 220,987
21-FEB-08 09:32 21-FEB-08 09:42 0 6 0 0 222,755
21-FEB-08 09:42 21-FEB-08 09:52 0 1 0 0 224,532
21-FEB-08 09:52 21-FEB-08 10:02 23 32 0 2 226,027
21-FEB-08 10:02 21-FEB-08 10:12 0 3 0 0 227,793
21-FEB-08 10:12 21-FEB-08 10:22 3 72 0 2 229,532
21-FEB-08 10:22 21-FEB-08 10:32 3,809 4,790 0 3 192,820
21-FEB-08 10:32 21-FEB-08 10:41 0 0 0 0 600 *******************

12 rows selected.

SQL> @undo_status

TSPACE STATUS HOW_MANY
——————– ——————– ———-
UNDO EXPIRED 335 *****************************