Not as soon as the Update is Over.. Only after a Certain period of Time.
This operation is done By SMON.
HTH
Best Regards,
Ganesh R
DID : +65-6215-8413
HP : +65-9067-8474
===========================================
Live to learn... forget... and learn again.
===========================================
-----Original Message-----
Sent: Thursday, June 05, 2003 10:05 PM
To: Multiple recipients of list ORACLE-L
With optimal, the rollback segment shrinks back to its optimal size as
soon as the update, insert, or delete transaction is committed.
However, there may be a long running query that is using that rollback
segment for a consistent view of the database. If the rollback segment
shrinks, the query cannot find the data that it needs. This results in
a snapshot too old error.
<rgaffuri
@cox.net> To: Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent by: root cc:
Subject: Re: Re: Snapshot
too old during stress test... how to avoid
06/05/2003 08:31
AM
Please respond
to ORACLE-L
why would removing optimal setting help with snapshot too old?
what I dont understand is that each of my DML transactions are
independent of each other. They query and perform DML on different
tables. None of them overlap. The only time they overlap is when they
hit the data dictionary for some brief queries.
>
> From: Tim Gorman <[EMAIL PROTECTED]>
> Date: 2003/06/05 Thu AM 03:25:36 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: Snapshot too old during stress test... how to avoid
>
> Speaking of this trick with a txn in each RBS, I've got a shell script
> on
my
> website (http://www.evdbt.com/tools.htm) that does just that. It is
named
> "prevent1555.sh" which uses a stored procedure created by a SQL script
named
> "prevent1555_ddl.sql"...
>
> As Jared mentioned, it is kind of a last resort, but it works...
>
>
>
>
> on 6/4/03 4:05 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED]
wrote:
>
> > .. and if it still doesn't work, use the trick of putting a
> > transaction in each of the rollback segments while the system is
> > otherwise quiesced, and *do not* commit or rollback the
> > transactions.
> >
> > This forces the rollbacks to extend if necessary, they will never
> > wrap back to the first extent ( actually the second) as long as
> > those transactions are not committed.
> >
> > It just uses a lot of disk space. Disk is cheap, right? :)
> >
> > Consider offlining all your production RBS and creating temporary
> > ones that you can easily drop later.
> >
> > I've used it with SAP client copies, which will not run to
> > completion without doing this, at least on our system.
> >
> > Jared
> >
> >
> >
> >
> >
> > Kirtikumar Deshpande <[EMAIL PROTECTED]>
> > Sent by: [EMAIL PROTECTED]
> > 06/04/2003 09:45 AM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
> > cc:
> > Subject: Re: Snapshot too old during stress test... how
to
avoid
> >
> >
> > Try removing optimal setting, and shrinking RBS to the min extents
> > (or even below) before running your tests.
> >
> > - Kirti
> >
> >
> > --- Garry Gillies <[EMAIL PROTECTED]> wrote:
> >> From memory (of a course attended looong ago),
> >> Oracle recommends one rollback segment for every
> >> three to four users.
> >> Four rollback segments between thirty six processes
> >> does seem a little mean.
> >>
> >> Garry
> >>
> >>
> >>
> >>
> >>
> >> <[EMAIL PROTECTED]>
> >> Sent by: [EMAIL PROTECTED]
> >> 04/06/03 13:59
> >> Please respond to ORACLE-L
> >>
> >>
> >> To: Multiple recipients of list ORACLE-L
> > <[EMAIL PROTECTED]>
> >> cc:
> >> Subject: Snapshot too old during stress test... how
to
> > avoid
> >>
> >>
> >> Im testing worst case scenarios right now. So Im doing batch
> >> updates,inserts,deletes and 'create table as' from a staging
tablespace
> > of
> >> approximately 5GBs to a master tablespace of approximately 11GBs.
> >>
> >> Ive got my job queue processes set to 36 and Im running 36 at a
> >> time
in
> >> the background in order to gather statistics and timing under worst
case
> >
> >> scenarios.
> >>
> >> Im in 8.1.7.3 and I have increased the size of my RBS tablespace to
11GB
> >
> >> for this test. I have 4 standard RBS with optimal size set to 1GB.
> >> Why would I get a snapshot too old? I would think that 11GBs of
> >> rollback
> > would
> >> be big enough. Would increasing the number of Rollback segments
> >> avoid
> > this
> >> even though I have the same amount of space in the tablespace?
> >>
> >> In reality Im going to seriallize the process to avoid this and to
> > improve
> >> performance, however, I want to stress the system.
> >>
> >> any advice?
> >>
> >> --
> >> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >> --
> >> Author: <[EMAIL PROTECTED]
> >> INET: [EMAIL PROTECTED]
> >>
> >
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
> > http://calendar.yahoo.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Tim Gorman
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L (or the
> name of mailing list you want to be removed from). You may also send
> the HELP command for other information (like subscribing).
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Thomas Day
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ganesh Raja
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
