All,
The truth behind the number 20 is a series of statistical
simulations, numerical analysis and ratio modeling that was done
at Oracle a few years ago (I think Cary was a part of that
exercise). If the extent sizing itself of the rollback segments
was done reasonably, given that we work with the minimum of (#
of concurrent transactions/4) worth of rollback segments in the
database, it was found that MINEXTENTS of at least 20 generated
a very low probability of the ORA-1555 (snapshot too old)
occuring. That was the net of it.
Cheers,
Gaja
- "Thapliyal, Deepak" <DThapliyal_at_ea.com> wrote:
> talking of Mr. Millsap, i had the oppurtunity to take his
> system tuning
> class ..
> the man really knows too many things is all i can say:)
>
>
> -----Original Message-----
> Sent: Saturday, October 21, 2000 5:45 PM
> To: Multiple recipients of list ORACLE-L
>
>
> The minimum value for minextents doesn't necessarily have to
> be 20, it all
> depends on how your system is used. Oracle "suggests" 20 as
> the minimum
> because Cary Millsap once said it should be 20.
>
> there are no absolutes in this.
>
> My $.02
>
> Rachel
>
>
> >From: Gillian M <zmkj04_at_yahoo.com>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> >Subject: RE: Rollback Segment Shrinkage Question
> >Date: Sat, 21 Oct 2000 11:20:20 -0800
> >
> >Note that to determine the value for optimal:
> >value of initial extent * value for minextents
> >
> >The minimum value for minextents should be 20. By
> >default it is 2.
> >
> >G!
> >--- Gaja Krishna Vaidyanatha <gajav_at_yahoo.com> wrote:
> > > Hi Rachel,
> > >
> > > Just to add another point to what you have mentioned
> > > here, and
> > > that is the increased probability of 'ORA-1555
> > > snapshot too old'
> > > occuring when OPTIMAL clause is set too low in the
> > > rollback
> > > segments. This is especially true when your
> > > environment
> > > supports a large number of small transactions and
> > > some long
> > > running reports.
> > >
> > > Cheers,
> > >
> > > Gaja
> > >
> > > --- Rachel Carmichael <carmichr_at_hotmail.com> wrote:
> > > > nope, it doesn't. Oracle only checks the size of
> > > the rollback
> > > > against
> > > > optimal when you need to move from one extent
> > > within the
> > > > rollback segment to
> > > > another.
> > > >
> > > > You are right that when it shrinks automatically
> > > it can have a
> > > > performance
> > > > hit but that's usually minimal. What's important
> > > to check is
> > > > to see if your
> > > > rollback segments are extending and shrinking
> > > constantly. If
> > > > they are, you
> > > > have sized them and/or the optimal parameter
> > > incorrectly.
> > > >
> > > >
> > > > >From: "Cale, Rick T (Richard)"
> > > <RICHARD.T.CALE_at_saic.com>
> > > > >Reply-To: ORACLE-L_at_fatcity.com
> > > > >To: Multiple recipients of list ORACLE-L
> > > > <ORACLE-L_at_fatcity.com>
> > > > >Subject: RE: Rollback Segment Shrinkage Question
> > > > >Date: Tue, 17 Oct 2000 08:25:54 -0800
> > > > >
> > > > >I think after the transaction completes it will
> > > shrink back
> > > > to optimal.
> > > > >Keep
> > > > >in mind their is a performance
> > > > >hit and you should monitor the frequency of
> > > dynamic
> > > > extensions.
> > > > >
> > > > >Rick
> > > > >
> > > > > > -----Original Message-----
> > > > > > From: Carle, William T (Bill), NBSO
> > > [SMTP:wcarle_at_att.com]
> > > > > > Sent: Tuesday, October 17, 2000 10:21 AM
> > > > > > To: Multiple recipients of list ORACLE-L
> > > > > > Subject: Rollback Segment Shrinkage Question
> > > > > >
> > > > > > Hi,
> > > > > >
> > > > > > At what point in time does a rollback
> > > segment shrink
> > > > back to its
> > > > > > optimal
> > > > > > value?
> > > > > >
> > > > > >
> > > > > > Bill Carle
> > > > > > AT&T
> > > > > > Database Administrator
> > > > > > 816-995-3922
> > > > > > wcarle_at_att.com
> > > > > >
> >
> >
> >__________________________________________________
> >Do You Yahoo!?
> >Yahoo! Messenger - Talk while you surf! It's FREE.
> >http://im.yahoo.com/
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: =?iso-8859-1?q?Gillian=20M?=
> > INET: zmkj04_at_yahoo.com
> >
> >Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> >San Diego, California -- Public Internet access /
> Mailing Lists
>
>--------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail
> message
> >to: ListGuru_at_fatcity.com (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).
>
>
> Get Your Private, Free E-mail from MSN Hotmail at
> http://www.hotmail.com.
>
> Share information about yourself, create your own public
> profile at
> http://profiles.msn.com.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rachel Carmichael
> INET: carmichr_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> San Diego, California -- Public Internet access /
> Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail
> message
> to: ListGuru_at_fatcity.com (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.com
> --
> Author: Thapliyal, Deepak
> INET: DThapliyal_at_ea.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> San Diego, California -- Public Internet access /
> Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail
> message
> to: ListGuru_at_fatcity.com (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).
>
Gaja Krishna Vaidyanatha
Director, Storage Management Products, Quest Software Inc.
Office : (972)-304-1170, E-mail : gajav_at_yahoo.com
Author - Oracle Tuning 101 by Osborne McGraw-Hill
Received on Sat Oct 21 2000 - 21:08:09 CDT
