Re: snapshot too old problem

In article <8tlv9k$6m1$1_at_nnrp1.deja.com>,   mr_potato_head_at_my-deja.com wrote:
> Hi,
> I'm running oracle 8.1.6 with solaris 7. I created a RBS tablespace
> originally with the following setup:
>
> rem ********** tablespace for rollback **********
> CREATE TABLESPACE RBS
> DATAFILE '/u/home/oracle/admin/wilma/links/rbs01_2000' SIZE
> 1998M REUSE
> AUTOEXTEND ON NEXT 1998K
> MINIMUM EXTENT 512K
> DEFAULT STORAGE
> ( INITIAL 512K
> NEXT 512K
> MINEXTENTS 4
> MAXEXTENTS 4096 );
>
> rem **** creating rollback segments ****************
> CREATE PUBLIC ROLLBACK SEGMENT RBS0
> TABLESPACE RBS
> STORAGE ( OPTIMAL 250M );
> CREATE PUBLIC ROLLBACK SEGMENT RBS1
> TABLESPACE RBS
> STORAGE ( OPTIMAL 250M );
> CREATE PUBLIC ROLLBACK SEGMENT RBS2
> TABLESPACE RBS
> STORAGE ( OPTIMAL 250M );
> CREATE PUBLIC ROLLBACK SEGMENT RBS3
> TABLESPACE RBS
> STORAGE ( OPTIMAL 250M );
> CREATE PUBLIC ROLLBACK SEGMENT RBS4
> TABLESPACE RBS
> STORAGE ( OPTIMAL 250M );
> CREATE PUBLIC ROLLBACK SEGMENT RBS5
> TABLESPACE RBS
> STORAGE ( OPTIMAL 250M );
> CREATE PUBLIC ROLLBACK SEGMENT RBS6
> TABLESPACE RBS
> STORAGE ( OPTIMAL 250M );
>
> My problem is that I do about 18 million inserts a day and near the
 end
> of the day when I attempt to do a select query I get "snapshot too
 old".
> I thought with the optimal command that the extents would max out and
> it would circulate through each rollback segment (the entire 250
 megs )
> before it overrights itself. This didn't appear to be the case so I
> attempted to remove the optimal command but I don't know how. I
 changed
> it to "0", does this remove it? If it does, I noticed that the
> minextents are at 4, I believe I need to max this out so it would
> take longer for the rollback segments to overright but I don't know
> how to do this either. I changed the minextents in the RBS
> tablespace but this didn't change the rollback segments themselves.
> Do I need to rebuild the rollback segments? Can anyone recommend
> a modification to my script above to do what I need? Thanks in
advance.

What's set at the tablespace level is merely what a segment will acquire by default (unless otherwise specified). Once the segment has acquired those defaults, they are the segment's settings -hence you are correct that to effect any changes, you need to do it at the segment level, not the tablespace (though clearly, in this case, you might want to do both).

An Optimal setting means that the Rollback Segment will, when crossing an extent boundary, check to see if it is larger than Optimal, and if so, shrink back to -or close to- the Optimal size, by dropping up unneeded  extents.

It's a well-established fact that the use of Optimal can render you much more liable to snapshot too old errors -first, it makes the segments smaller, and snapshot too olds always arise because the rollback segment is too small. Second, the discard of unneeded extents means that extents are dropped which may contain information needed for read-consistency.

Frankly, my advice has always been never to set Optimal... I always advise letting the segments do what they want during the day, and issue a scripted 'alter rollback segment X shrink to 250M;' at the dead of night when no-one cares: it means all your segments start the day at an ideal size, but you are in control of the shrinkage, not Oracle.

As to de-setting it, well... v$rollstat shows you whether optimal has been set or not -and if you alter optimal to 0, it shows as zero. Whereas a segment with no optimal set shows nothing in the OPTSIZE column. So, in short, I'd not be comfortable with changing it to zero. I'd drop the lot, and re-create them ommitting the 'optimal' clause.

Incidentally, if *growth* of rollback segments is occurring frequently, it rather suggests that your extent size is too small in the first place (or the number of extents is too small). You might consider making them bigger while you're at it -512K extents for 18 million row inserts sounds distinctly under-powered.

Regards
HJR
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

--
======================================================
'Baldric, you wouldn't recognise a cunning plan if it
painted itself blue, and danced naked on top of a
harpsichord singing 'Cunning Plans Are Here Again!'


Sent via Deja.com http://www.deja.com/
Before you buy.