Re: snapshot too old problem

On Tue, 31 Oct 2000 08:19:02 GMT, 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.

That is not what optimal does.
Optimal shrinks a rollback segment back to the size optimal after the full size is no longer needed.
The minimum size of optimal is the minimum size of the rollback segments. In your case apparently 4, although without options it should be 2.. Check the sum of the initial and next column in dba_rollback_segs.

If you do it like that the rollback segments will shrink to a few Meg and there will be room enough for a rollback segment that needs the size.

You change rollback segments with the alter rollback segment statement

For instance:
alter rollback segment storage (optimal 10 M);

Dropping them and recreating them with sensible values will also work.

For the rest I advice to look at the oracle books online. For statements you need the book "Oracle Server SQL Reference" They come with the oracle CD and are very good in explaining this kind of stuff.

Joachim.

> 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.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
Joachim Verhagen (jcdverha_at_xs4all.nl)
WWW http://www.xs4all.nl/~jcdverha/  (Science Jokes)