AUTOALLOCATE and Undo Segments

Recently I had a query from another DBA who asked why a monitoring script had reported early in the morning "Threshold : Max_Free less than Twice the largest Extent. Potential error if the DataFile cannot AutoExtend" and listing two Undo Segments with 64MB extents in the UNDOTBS1 tablespace. Apparently, although DBA_EXTENTS did show two Undo Segments with extents of 64MB, the question was whether the nextextent will be bigger than 64m -- "it should be 64K. because the unit of the tablespace "undotbs1" is 64K. parameters "next" & "min_extents" are obsolete in LMT."

My response was : "AUTOALLOCATE means that the Next ExtentSize is automatically determined. Segments start with 64K extents then automatically switch to 1MB extents and 8MB extents and finally 64MB extents as they start growing. That is why it is important to know the size of the last (largest) extent . If the largest extent is already 64MB, then the next WILL be 64MB. At 07:12 in the morning, segments _SYSSMU8$ and _SYSSMU9$ were already using 64MB extents. Since AUM periodically drops and recreates Undo segments, they may get created with 64KB extents. As a Segment grows for large transaction, its Next Extent will autoallocate to 1MB, 8MB and 64MB extents. Do NOT confuse LMT and AutoAllocate. They are two different concepts." I was able to produce a listing showing undo segments with 64KB, 1MB and 8MB extents (by the time I ran the query, there were no 64MB extents around -- because of the "Automatic" Undo Management that Oracle does). The alert had just warned that if there had been a long running transaction which had continued to grow and the undo tablespace datafile did not autoextend, the transaction might have failed -- just as would happen with a table or index segment in a normal tablespace whose datafile does not extent to allocate the next extent for the table and index. To further test this, I just ran a few SQLs on my PC : UNDO SEGMENTS BEFORE RUNNING MY TRANSACTIONS :

SQL> select segment_name, bytes/1024, blocks , count(*)
2 from dba_extents
3 where tablespace_name = 'UNDOTBS1'
4 group by segment_name, bytes/1024, blocks
5 order by 1
6 /
SEGMENT_NAME BYTES/1024 BLOCKS COUNT(*)
--------------- ---------- ---------- ----------
_SYSSMU1$ 64 8 2
1024 128 6
_SYSSMU10$ 64 8 16
1024 128 6
_SYSSMU2$ 64 8 115
1024 128 28
_SYSSMU3$ 64 8 2
1024 128 7
_SYSSMU4$ 64 8 2
1024 128 1
_SYSSMU5$ 64 8 2
1024 128 4
_SYSSMU6$ 64 8 25
1024 128 23
_SYSSMU7$ 64 8 3
1024 128 9
_SYSSMU8$ 64 8 2
1024 128 9
_SYSSMU9$ 64 8 127
1024 128 21
20 rows selected.
SQL> spool off

RUNNING MY TRANSACTIONS (and validating that Oracle does allocate a different Undo Segment for each new transaction) :


SQL>
SQL> REM Begin Transactions here ========================================================
SQL>
SQL>
SQL> drop table hemant.test_txn_table ;
Table dropped.
SQL>
SQL> create table hemant.test_txn_table as
2 select * from dba_objects
3 union
4 select * from dba_objects
5 /
Table created.
SQL>
SQL> update hemant.test_txn_table
2 set object_id=object_id+1, owner=substr(owner,1,3)'_HKC' ;
51538 rows updated.
SQL>
SQL> select t.xidusn, r.name, t.start_time, t.used_ublk, t.used_urec
2 from v$transaction t, v$rollname r
3 where t.xidusn=r.usn
4 /
XIDUSN NAME START_TIME USED_UBLK USED_UREC
---------- ------------------------------ -------------------- ---------- ----------
6 _SYSSMU6$ 05/31/07 23:01:20 635 51538
SQL> commit;
Commit complete.
SQL>
SQL> create index hemant.test_txn_tbl_ndx on hemant.test_txn_table(owner);
Index created.
SQL>
SQL> delete hemant.test_txn_table
2 where owner = 'SYS_HKC';
24692 rows deleted.
SQL>
SQL> select t.xidusn, r.name, t.start_time, t.used_ublk, t.used_urec
2 from v$transaction t, v$rollname r
3 where t.xidusn=r.usn
4 /
XIDUSN NAME START_TIME USED_UBLK USED_UREC
---------- ------------------------------ -------------------- ---------- ----------
7 _SYSSMU7$ 05/31/07 23:01:28 771 24823
SQL> rollback;
Rollback complete.
SQL>
SQL> update hemant.test_txn_table
2 set object_id=object_id+1, owner=substr(owner,1,3)'_HKC2'
3 where owner = 'SYS_HKC';
24692 rows updated.
SQL>
SQL> select t.xidusn, r.name, t.start_time, t.used_ublk, t.used_urec
2 from v$transaction t, v$rollname r
3 where t.xidusn=r.usn
4 /
XIDUSN NAME START_TIME USED_UBLK USED_UREC
---------- ------------------------------ -------------------- ---------- ----------
9 _SYSSMU9$ 05/31/07 23:01:40 833 74076
SQL> commit;
Commit complete.
SQL>
SQL> delete hemant.test_txn_table
2 where rowid in (select rowid from hemant.test_txn_table where rownum <>
SQL> select t.xidusn, r.name, t.start_time, t.used_ublk, t.used_urec
2 from v$transaction t, v$rollname r
3 where t.xidusn=r.usn
4 /
XIDUSN NAME START_TIME USED_UBLK USED_UREC
---------- ------------------------------ -------------------- ---------- ----------
5 _SYSSMU5$ 05/31/07 23:01:50 5 248
SQL> commit;
Commit complete.
SQL>
SQL> delete hemant.test_txn_table
2 where rowid in (select rowid from hemant.test_txn_table where rownum <>
SQL> select t.xidusn, r.name, t.start_time, t.used_ublk, t.used_urec
2 from v$transaction t, v$rollname r
3 where t.xidusn=r.usn
4 /
XIDUSN NAME START_TIME USED_UBLK USED_UREC
---------- ------------------------------ -------------------- ---------- ----------
1 _SYSSMU1$ 05/31/07 23:01:51 3 126
SQL> commit;
Commit complete.
SQL>
SQL> insert into hemant.test_txn_table
2 select * from dba_objects
3 union
4 select * from dba_objects
5 union
6 select * from hemant.test_txn_table ;
102888 rows created.
SQL>
SQL> select t.xidusn, r.name, t.start_time, t.used_ublk, t.used_urec
2 from v$transaction t, v$rollname r
3 where t.xidusn=r.usn
4 /
XIDUSN NAME START_TIME USED_UBLK USED_UREC
---------- ------------------------------ -------------------- ---------- ----------
6 _SYSSMU6$ 05/31/07 23:01:51 294 5186
SQL> commit;
Commit complete.
SQL> spool off


UNDO SEGMENTS *AFTER* RUNNING THOSE TRANSACTIONS :


SQL> select segment_name, bytes/1024, blocks , count(*)
2 from dba_extents
3 where tablespace_name = 'UNDOTBS1'
4 group by segment_name, bytes/1024, blocks
5 order by 1
6 /
SEGMENT_NAME BYTES/1024 BLOCKS COUNT(*)
--------------- ---------- ---------- ----------
_SYSSMU1$ 64 8 2
1024 128 6
_SYSSMU10$ 64 8 16
1024 128 6
_SYSSMU2$ 64 8 107
1024 128 26
_SYSSMU3$ 64 8 2
1024 128 7
_SYSSMU4$ 64 8 33
1024 128 7
_SYSSMU5$ 64 8 2
1024 128 1
_SYSSMU6$ 64 8 25
1024 128 23
_SYSSMU7$ 64 8 3
1024 128 9
_SYSSMU8$ 64 8 2
1024 128 9
_SYSSMU9$ 64 8 127
1024 128 21
20 rows selected.
SQL>

Thus, we can see that different Undo Segments grew at different rates and Extent sizes vary from 64KB to 1MB in the above example but can be 64MB as well.