ORA-01628: max # extents (32765) reached for rollback segment _SYSSMUxxx

Recently I came across “ORA-01628: max # extents (32765)” error. The database had resumable_timeout parameter set I received page for a session in resumable state. Looking into the alert log showed

statement in resumable session ‘User USER_E3(375), Session 9028, Instance 1’ was suspended due to
ORA-01628: max # extents (32765) reached for rollback segment _SYSSMU375_247595031$
Wed Apr 08 02:41:42 2015

Looking at the sid# 9028 details, INSERT sql was running and the session was on waiting on “statement suspended, wait error to be cl” as resumable_timeout parameter was set.

PRD04> @undo_stats
 
Undo Stats for Automatic Undo Management for Last 3 Hours
 
BEGIN_TIME         END_TIME           ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION   TXNCOUNT MAXQUERYLEN NOSPACEERRCNT
------------------ ------------------ ---------- ------------- ----------- ------------------- ---------- ----------- -------------
07-APR-15          08-APR-15              207496      22622976     6746320               46384     670317       11587             0
08-APR-15          08-APR-15              204488      22562776     6797160               46215     736330       10327             0
08-APR-15          08-APR-15              119832      22329656     7295296               46251     645728       10934             0
08-APR-15          08-APR-15              171992      22368920     7304336               46010     532932       11539             0
08-APR-15          08-APR-15              109968      22591672     7245352               46057     649534       12145             0
08-APR-15          08-APR-15              113016      22505688     7349552               45925     679114       12751             0
08-APR-15          08-APR-15              218048      22355184     7317048               46147     665002       13357             0
08-APR-15          08-APR-15              242368      22534704     7360800               46188     695974       13962             0
08-APR-15          08-APR-15              298368      22489016     7546968               45427     622305        5092             0
08-APR-15          08-APR-15               98792      22417864     7941104               44771     587418         702             0
08-APR-15          08-APR-15              151888      22744560     7860176               43790     703158        1308             0
08-APR-15          08-APR-15              353272      22606848     8085272               42483     670949        1913             0
08-APR-15          08-APR-15              679376      21995016     8654552               40691     662446        1245             0
08-APR-15          08-APR-15             1172864      21419400     8860792               39531     780894        1851             0
08-APR-15          08-APR-15             1093784      21696040     8664424               36463          0        2457             0
08-APR-15          08-APR-15             1374528      21315992     8761640               33362     733162        3062             0
08-APR-15          08-APR-15             1433128      21166848     8852944               31892     755982        3668           158
08-APR-15          08-APR-15             1454984      21040312     8960960               31767     692704        4274           293
08-APR-15          08-APR-15             1454984      21040312     8960960               31596     266300        4577           108
 
19 rows selected.

Transactions Using Rolback:
 
RBS        OS User         DB User         SID   Schema     Object Name          Type       Used RBS Blocks  # of Records
---------- --------------- --------------- ----- ---------- -------------------- ---------- ---------------- ------------------
...................
...................
_SYSSMU375 root            USER_E3         9028  ADMIN_E TESTS             TABLE PART 385255           23794634
_SYSSMU375 root            USER_E3         9028  ADMIN_E TESTS             TABLE      385255           23794634
...................
...................

26 rows selected.

 
PRD04> show parameter undo
 
NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
undo_management                      string     AUTO
undo_retention                       integer    7200
undo_tablespace                      string     UNDOTBS4

PRD04> @undo_extent_stats
 
STATUS                                 MB       PERC
------------------------------ ---------- ----------
ACTIVE                              11741          3
EXPIRED                            225670         49
UNEXPIRED                          170579         37

Looking at the sessions with active transactions

PRD04> SELECT s.sid, s.serial#, s.username, u.segment_name, count(u.extent_id) "Extent Count", t.used_ublk, t.used_urec, s.program
FROM v$session s, v$transaction t, dba_undo_extents u
WHERE s.taddr = t.addr and u.segment_name like '_SYSSMU'||t.xidusn||'_%$' and u.status = 'ACTIVE'
GROUP BY s.sid, s.serial#, s.username, u.segment_name, t.used_ublk, t.used_urec, s.program
ORDER BY t.used_ublk desc, t.used_urec desc, s.sid, s.serial#, s.username, s.program;
 
    SID    SERIAL# USERNAME               SEGMENT_NAME                   Extent Count  USED_UBLK  USED_UREC PROGRAM
------- ---------- ---------------------- ------------------------------ ------------ ---------- ---------- ------------------------------------------------
   9028      15075 USER_E3                _SYSSMU375_247595031$                 32726     389285   24043132 php@script01.example.com
   2317      38231 USER_E2                _SYSSMU344_2152184027$                   11       8303     509953 php@script06.example.com
   8930      34713 USER_E3                _SYSSMU366_2128258416$                    6       4930     401890 php@script02.example.com
   5469       3267 USER_E4                _SYSSMU362_25371301$                      3       1704     116802 php@script02.example.com
..........
..........

13 rows selected

As per Metalink Doc “Troubleshooting ORA-1628 – max # extents (32765) reached for rollback segment (Doc ID 1580182.1)”

With automatic undo, you have no control over the extent sizes, nor do you have the ability to shrink them. This all happens automatically. The system automatically decides on the extent size, however it will generally start allocating larger extents if an undo segment is extending a lot. The large number of extents in the rollback segment is likely due to fragmentation in the undo tablespace: Oracle can probably only allocate extents of 64k due to fragmentation, so it is very probable to hit the max extents issue.

The maximum number of extents for undo segments is limited to 32K and a long/large running transaction can exhaust this limit by adding new extents if the next extent of the current one is not an expired one and finally will receive ORA-1628.

So, after getting the ORA-1628 errors in the transaction that extended the undo segment until its limit, future transactions will not be allowed to bind to the undo segment until it is not shrinked (you may see that the number of extents is decreased).

So, The two major causes of ORA-1628 issue are a very large transaction or undo tablespace fragmentation.

In case of large transaction, That is solved by splitting the large transaction to smaller ones (e.g. frequent commits).

In case of undo tablespace fragmentation, That is solved by recreating the undo tablespace (this is also the recommended solution of Bug 10330444 and Bug 10229998 which were filed for the same issue and closed as not a bug).

The UNDO tablespace had around 10Gb free, so adding space to UNDO wouldn’t resolve the issue. We informed the client’s Dev team and it was taken care.

Below are few sqls related to UNDO which I think can be useful while diagnosing issues


col "Parameter" format A32
col "Session Value" format A32
col "Instance Value" format A32
select a.inst_id, a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.inst_id=b.inst_id and b.inst_id=c.inst_id
and a.ksppinm in ('_smu_debug_mode', '_highthreshold_undoretention')
order by 2;

   INST_ID Parameter			    Session Value		     Instance Value
---------- -------------------------------- -------------------------------- --------------------------------
	 1 _highthreshold_undoretention     4294967294			     4294967294
	 1 _smu_debug_mode		    0				     0


select segment_name,
   round(nvl(sum(act),0)/(1024*1024*1024),3 ) "ACT GB BYTES",
   round(nvl(sum(unexp),0)/(1024*1024*1024),3) "UNEXP GB BYTES",
   round(nvl(sum(exp),0)/(1024*1024*1024),3) "EXP GB BYTES",
   NO_OF_EXTENTS
   from ( select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp, count(*) NO_OF_EXTENTS
   from DBA_UNDO_EXTENTS
   where status='ACTIVE' and tablespace_name = 'UNDOTBS4'
   group by segment_name
   union
   select segment_name,00 act, nvl(sum(bytes),0) unexp, 00 exp , count(*) NO_OF_EXTENTS
   from DBA_UNDO_EXTENTS
   where status='UNEXPIRED' and tablespace_name = 'UNDOTBS4'
   group by segment_name
   union
   select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp, count(*) NO_OF_EXTENTS
   from DBA_UNDO_EXTENTS
   where status='EXPIRED' and tablespace_name = 'UNDOTBS4'
   group by segment_name
   ) group by segment_name, NO_OF_EXTENTS having NO_OF_EXTENTS >= 30 order by 5 desc;

SEGMENT_NAME		       ACT GB BYTES UNEXP GB BYTES EXP GB BYTES NO_OF_EXTENTS
------------------------------ ------------ -------------- ------------ -------------
_SYSSMU375_247595031$			  0	    10.082	      0 	32765
_SYSSMU313_3664260743$			  0	     1.209	      0 	 2925
_SYSSMU316_1833322353$			  0		 0	   .607 	 2299
_SYSSMU367_1976067076$			  0		 0	   .788 	 2003
_SYSSMU577_2158293664$			  0		 0	  1.186 	 1710
_SYSSMU503_170622332$			  0		 0	   .604 	 1617
_SYSSMU330_231237204$			  0	      .899	      0 	 1523
_SYSSMU336_3955560249$			  0	     1.375	      0 	 1240
_SYSSMU5059_1468117272$ 		  0		.5	      0 	 1089
_SYSSMU306_1485117688$			  0	    12.145	      0 	  964

We can now take look at extent details for the specific undo segment which caused error. The undo segment was primarily made up of tiny (64 KB) extents

PRD04> break on report
PRD04> compute sum label Total of Extent_Count Extent_MB on report
PRD04> col Extent_MB format 999,999.00
PRD04> SELECT segment_name, bytes/1024 "Extent_Size_KB", count(extent_id) "Extent_Count", bytes * count(extent_id) / power(1024, 2) "Extent_MB" FROM dba_undo_extents WHERE segment_name = '_SYSSMU375_247595031$' group by segment_name, bytes order by 1, 3 desc;

SEGMENT_NAME		       Extent_Size_KB Extent_Count   Extent_MB
------------------------------ -------------- ------------ -----------
_SYSSMU375_247595031$			   64	     31858    1,991.13
_SYSSMU375_247595031$			 8192	       493    3,944.00
_SYSSMU375_247595031$			 4096	       178	712.00
_SYSSMU375_247595031$			 2048		23	 46.00
_SYSSMU375_247595031$			 1024		22	 22.00
_SYSSMU375_247595031$			16384		22	352.00
_SYSSMU375_247595031$			65536		17    1,088.00
_SYSSMU375_247595031$			 3072		12	 36.00
_SYSSMU375_247595031$			24576		12	288.00
_SYSSMU375_247595031$			12288		10	120.00
_SYSSMU375_247595031$			49152		 8	384.00
_SYSSMU375_247595031$			 1152		 7	  7.88
_SYSSMU375_247595031$			 1088		 6	  6.38
_SYSSMU375_247595031$			 3136		 6	 18.38
_SYSSMU375_247595031$			32768		 5	160.00
_SYSSMU375_247595031$			57344		 5	280.00
_SYSSMU375_247595031$			20480		 5	100.00
_SYSSMU375_247595031$			28672		 5	140.00
_SYSSMU375_247595031$			40960		 4	160.00
_SYSSMU375_247595031$			 1216		 4	  4.75
_SYSSMU375_247595031$			 6144		 4	 24.00
_SYSSMU375_247595031$			 3264		 3	  9.56
_SYSSMU375_247595031$			 3200		 3	  9.38
_SYSSMU375_247595031$			 5248		 2	 10.25
_SYSSMU375_247595031$			 2240		 2	  4.38
_SYSSMU375_247595031$			 3776		 2	  7.38
_SYSSMU375_247595031$			 2368		 2	  4.63
_SYSSMU375_247595031$			 2112		 2	  4.13
_SYSSMU375_247595031$			 5120		 2	 10.00
_SYSSMU375_247595031$			 1344		 2	  2.63
_SYSSMU375_247595031$			 3328		 2	  6.50
_SYSSMU375_247595031$			 2752		 2	  5.38
_SYSSMU375_247595031$			 3392		 2	  6.63
_SYSSMU375_247595031$			 2432		 2	  4.75
_SYSSMU375_247595031$			 7616		 1	  7.44
_SYSSMU375_247595031$			36864		 1	 36.00
_SYSSMU375_247595031$			 3584		 1	  3.50
_SYSSMU375_247595031$			 4032		 1	  3.94
_SYSSMU375_247595031$			 2304		 1	  2.25
_SYSSMU375_247595031$			 7872		 1	  7.69
_SYSSMU375_247595031$			 1280		 1	  1.25
_SYSSMU375_247595031$			11776		 1	 11.50
_SYSSMU375_247595031$			 5440		 1	  5.31
_SYSSMU375_247595031$			 7360		 1	  7.19
_SYSSMU375_247595031$			11264		 1	 11.00
_SYSSMU375_247595031$			 3712		 1	  3.63
_SYSSMU375_247595031$			35520		 1	 34.69
_SYSSMU375_247595031$			14336		 1	 14.00
_SYSSMU375_247595031$			59648		 1	 58.25
_SYSSMU375_247595031$			 6400		 1	  6.25
_SYSSMU375_247595031$			 2560		 1	  2.50
_SYSSMU375_247595031$			 3456		 1	  3.38
_SYSSMU375_247595031$			37888		 1	 37.00
_SYSSMU375_247595031$			 9536		 1	  9.31
_SYSSMU375_247595031$			 6336		 1	  6.19
_SYSSMU375_247595031$			15168		 1	 14.81
_SYSSMU375_247595031$			14400		 1	 14.06
_SYSSMU375_247595031$			 3520		 1	  3.44
_SYSSMU375_247595031$			 6528		 1	  6.38
_SYSSMU375_247595031$			19520		 1	 19.06
_SYSSMU375_247595031$			 1600		 1	  1.56
_SYSSMU375_247595031$			 2176		 1	  2.13
_SYSSMU375_247595031$			 6272		 1	  6.13
_SYSSMU375_247595031$			 9344		 1	  9.13
_SYSSMU375_247595031$			 5376		 1	  5.25
					      ------------ -----------
Total						     32765   10,324.25

65 rows selected.
Advertisements

2 thoughts on “ORA-01628: max # extents (32765) reached for rollback segment _SYSSMUxxx

  1. You have not mentioned DB version. Check if this is existing code which started erroring out after upgrade. We had faced this error after upgrading to 11.2.0.4 from 11.2.0.2. Check Note 1951022.1 too

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s