Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> FW: ORA-01555: snapshot too old: rollback segment number 24 with name "_SYSSMU24$" too small

FW: ORA-01555: snapshot too old: rollback segment number 24 with name "_SYSSMU24$" too small

From: Wittenmyer Joel - CO <>
Date: Tue, 27 Jun 2006 15:41:42 -0500
Message-ID: <F5E885BEF9540D47A7BDC03CF168808722BA2D5C@tuscil_ex1>

Well, it's a client of mine, and they are challenging, but that's what makes it fun! :O

Oh, but GATHER_STATS_JOB does do 'massive DML' (ok, maybe not massive, but a lot :) ), in this case to the 'OPSTSTAT' objects in SYSAUX. Large numbers of deletes in particular. The OPSTSTAT indexes can easily grow to 2 or 3 times the size of the tables. I rebuild them monthly to reclaim several Gig of space. I've sat and watched the stats job run and had it blow out max_extents on an undo segment. Over and Over.

I believe the bug is 4288876.

As I said, it sounds similar. It may not be the same issue, but may be worth looking at.

-----Original Message-----
From: Alex Gorbachev [] Sent: Tuesday, June 27, 2006 3:12 PM
To: Wittenmyer Joel - CO
Subject: Re: ORA-01555: snapshot too old: rollback segment number 24 with name "_SYSSMU24$" too small

My respect if you can have terrabyte OLTP system running stable with auto gather stats job. Quite challenging. :)

If you have bug # - please share.
Stats gathering doesn't do "massive DML". What it does is a lot of selects and assuming those are long running, one can expect it to fail with ORA-1555 on a busy OLTP system. The only DMLs are done to dictionary tables to update stats (unless I seriously missing something). Invalidation of execution plans caused by updating statistics should have far more noticeable impact I believe.

2006/6/27, Wittenmyer Joel - CO <>:
> This sounds similar to a known bug with ASSM tablespaces. The stats job
> updates tables in SYSAUX. Those tables have indexes and up to if
> an instance does massive DML (which the stats job does in our case.
> Terabyte oltp with daily data loads and many schemas) on segments in an
> tablespace that has indexes, the undo is unbelievable. In our case it
> produces 10x the undo / redo it normally would. It can cause not only
> ora-1555, but you can blow out the max extents (32767) for an undo
> The 'workaround' of coalescing the indexes prior to running such DML is
> minimally effective. The backport patch for previous versions in some
> flat doesn't work (I know from experience.) The only sure fix is to
> to, which I am told definitely fixes the issue, and which I am
> preparing to do on several production instances even thought I HATE
> the latest patchset (hornet's nest). Of course, if you are already on
> and are seeing this, please let me know!

Best regards,
Alex Gorbachev
Received on Tue Jun 27 2006 - 15:41:42 CDT

Original text of this message