Steve,
Thank you for your clarification on my extent sizing suggestion,
but I'd like you to know that it was not based on the premise
that updates to the transaction table occur only when undo
generation moves into a new extent. Since I did not mention
that specifically in my posting and one should not assume it.
The extent sizing suggestion was more based on the number of
transaction entries that need to be maintained in the
transaction table, when undo entries of the same transaction
stagger across multiple extents. The overhead of accessing the
transaction table multiple times, to "rebuild" the
read-consistency image of one/more blocks of data from the
rollback segments should be factored into the sizing of the
rollback segment. That is one of the primary factors that needs
to be kept in mind while sizing extents of rollback segments.
Cheers,
Gaja.
- Steve Adams <steve.adams_at_ixora.com.au> wrote:
> Hi Gaja,
>
> I think your extent sizing suggestion is based on an incorrect
> premise.
>
> A transaction must update its transaction table slot in its
> rollback
> segment header block every time that its undo generation moves
> into a new
> undo block.
> It seems that your argument is based on the premise that these
> updates only
> occur when undo generation moves into a new extent.
> Apart from dynamic extension, it is the database block size,
> rather than
> the extent size, that affects the number of gets on the
> segment header
> block.
>
> Regards,
> Steve Adams
> http://www.ixora.com.au/
> http://www.oreilly.com/catalog/orinternals/
> http://www.christianity.net.au/
>
>
> -----Original Message-----
> From: Gaja Krishna Vaidyanatha [SMTP:gajav_at_yahoo.com]
> Sent: Monday, May 22, 2000 4:54 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Transactions waiting for Lock on Rollback
> Segments
>
> Friends,
>
> I totally agree with Steve's sentiments. There are many
> issues
> on the table here, and my response to all of them make this
> posting a long one. Thank your for your patience is reading
> this through.
>
> The limitation to prevent us from creating more than 50
> rollback
> segments was lifted in early Oracle7, when Oracle started
> supporting the max_rollback_segments parameter. The
> rollback_segments parameter itself could not support more than
> 50 entries prior to Oracle7, and hence the limitation. The
> documentation has not changed with time. This limitation has
> been long lifted. If there is justification and a need to
> create more rollback segments, then by all means go right
> ahead
> and do it. Personally, I have created as many rollback
> segments
> as the value of the max_rollback_segments parameter.
>
> The "ball park" optimum number of rollback segments that is
> required for most environments can be derived by the formula :
> "no. of concurrent txns/4". The size of the extents in the
> rollback segments can be derived by performing an
> avg(used_ublk)
> in v$transaction during peak periods. The number that is
> acquired from the above average should then be raised to the
> next power of 2(e.g. if the avg(used_ublk) is 14045, then the
> initial and next size for the rollback segments can be 16K).
> Obviously, this is not the size for the big_roll rollback
> segments that you use for your batch jobs.
>
> The goal here is to try to accomodate the undo entries of a
> transaction in 1 extent, to avoid re-reading the rollback
> segment header. The rollback segment header contains the
> transaction table and the pointers to the location where a
> given
> transaction's undo entry is located. Reading the transaction
> table over and over again can and will cause contention for
> that
> rollback segment.
>
> I am not in any way suggesting that multiple transactions
> should
> not write their undo entries to the same extent. All I am
> trying to communicate here is a design to reduce contention on
> the rollback segment header and the transaction table that it
> hosts.
>
> The minimum number of extents in a rollback segment should be
> at
> least 20. Simulations and tests done in Oracle's Internal
> Labs
> in the past using this MINEXTENTS value, has shown to reduce
> the
> probability of the "snapshot too old" error occurring.
>
> Personally, I am not a big fan of the "OPTIMAL" parameter as
> this again increases the probability of the "snapshot too old"
> error. I'd rather shrink the rollback segment via a job at a
> time that is appropriate (wee hours of the morning) than have
> "OPTIMAL" kill a long-running query (batch report). I know if
> you are running a database prior to 7.3, then you are out of
> luck. I have observed that adhering to the above set of rules
> has helped me proactively manage the rollback segment problem
> very well. Your mileage might vary, but is definitely worth
> the
> effort.
>
> The Tuning Manual's recommendation to create as many freelists
> as the number of concurrent inserts is extreme, absurd and
> makes
> no sense. The law of diminishing returns will come to play in
> a
> significant manner, if this recommendation is implemented.
>
> For e.g., if a 1000 concurrent inserts frequently occur on
> table, the effect of configuring 1000 freelists (even if
> supported) on the high water mark of the table is palpable.
> When a 1000 freelists are configured for a table, and when a
> new
> extent is allocated for the table with say a 10000 blocks,
> then
> each freelist will contain 10 blocks. When multiple
> concurrent
> inserts hit this table, each transaction can and will
> potentially be assigned a different freelist and hence will
> insert into different blocks.
>
> We are all aware of the fact that a full table scan will read
> and process "high water mark" worth blocks. By configuring an
> outlandish number of freelists, on very large tables with many
> concurrent inserts, the "high water mark" of the table is
> unnecessarily inflated, which can cause stress on the I-O
> sub-system, while performing full table scans on these tables.
> Basically, you are doing more I-O than what is required.
>
> In most environments, the need for more than (2 * # of CPUs)
> worth of freelists should be questioned. As I mentioned
> before,
> the law of diminishing returns will play its part and the
> performance "delta" between having a table with (2 * # of
> CPUs)
> worth of freelists and anything that is significantly higher,
> will be miniscule. Plus, you will have to factor the
> additional
> cost of the inflated high water mark of the table.
>
> I know this from having done some tests in 7.3.5 and I am
> pretty
> sure that this functionality has not changed radically in
> Oracle8/8i. Proactive configuration of an optimal number of
> freelists and the effective use of the INITRANS parameter
> should
> take care of block level contention in most cases. Thanks for
> your patience.
>
> Best Regards,
>
> Gaja.
>
> --- Steve Adams <steve.adams_at_ixora.com.au> wrote:
> > Hi All,
> >
> > There is some rather peculiar advice being given here.
> > The old recommendation to not create more than 50 rollback
> > segments has
> > been out of date for nearly a decade.
> > You cannot increase freelists to numbers of the order of
> 1000
> > or more, not
> > even with a 32K database block size.
> > The transactions_per_rollback_segment parameter does not
> > impact rollback
> > segment usage in any way - only the number of public
> rollback
> > segments to
> > acquire at startup.
> >
> > Looking down at the initial problem, my guess is that it was
> > actually an
> > ITL entry shortage (see page 47 in my book).
> > If this is 8i, you may want to consider using the 'alter
> table
> > minimize
> > records_per_block' command to control the number of rows
> > allowed in each
> > block.
> >
> > Regards,
> > Steve Adams
> > http://www.ixora.com.au/
> > http://www.oreilly.com/catalog/orinternals/
> > http://www.christianity.net.au/
> >
> >
> > -----Original Message-----
> > From: LBhat_at_levi.com [SMTP:LBhat_at_levi.com]
> > Sent: Friday, May 19, 2000 5:07 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Transactions waiting for Lock on Rollback
> > Segments
> >
> > The thumb rule/max by default for no. of transactions per
> > rollback segment
> > is 4. In the Oracle Server Tuning manual it suggest to have
> a
> > n/4 rollback
> > segments for "n" concurrent transactions, but not more than
> 50
> > and adds
> > "these guidelines are appropriate for most application
> mixes".
> >
> > As you noted down increasing the freelists from 50 to higher
> > levels such
> > as 1000 or 1500 will give you better picture. You may
> > increase the
> > freelists
> > further if you still encounter contention.
> >
> > To start with you may still keep the no. of rollback
> segments
> > to 160 and
> > by increasing the transactions_per_rollback_segments to 15.
> > Also you have
> > set the optimal size to 10M which is equal to minextents.
> The
> > overhead for
> > this frequent shrinkage can be avoided by this.
> >
> > Hope that helps you.
> >
> > Bhat
> >
> > Sent: Friday, May 19, 2000 1:29 PM
> >
> > Thanks so much for the info
> >
> > L Bhat wrote "1. Increase total no. of rollback segments
> upto
> > 50 (not more
> > than that,
> > oracle advice)"
> >
> > Qs. 1 Should the Number of Rollback segments in the FULL
> > Database
> > NEVER be More than 50 ?
> >
> > NOTE - Had Setup - 160 Rollback Segments Each with - EXTENT
> > size 512
> > K MINEXTENTS 20 OPTIMAL 10M
> >
> > Qs. 2 Since 2300 Concurrent OLTP Transactions Happen, What
> > should be
> > the Value of transactions_per_rollback_segments ?
> >
> > NOTE - FREELISTS on Each of the 3 Tables being Inserted
> into
> > was ONLY 50
> > whereas 2300 Concurrent Oracle Session process were doing
> the
> > inserts
> >
> >
> >
> > > -----Original Message-----
> > > From: LBhat_at_LEVI.com [SMTP:LBhat_at_LEVI.com]
> > > Sent: Friday, May 19, 2000 8:44 AM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: RE: Transactions waiting for Lock on Rollback
> > Segments
> > >
> > > Cut-Paste from Orace 7 Server Tuning Manual.
> > >
> >
>
> > > <<To reduce contention for the free lists of a table,
> > re-create the table
> > > with
> > > a larger value for the FREELISTS storage parameter.
> > Increasing the
> > > value of this parameter to the number of Oracle processes
> > that
> > > concurrently insert data into the table may benefit
> > performance for the
> > > INSERT statements>>
> > >
> > > wrt to your problem, I would advice to check the following
> > >
> > > 1. Increase total no. of rollback segments upto 50 (not
> > more than that,
> > > oracle advice)
> > > 2. Increase transactions_per_rollback_segments parameter.
> > > You may be able to balance between (1) and (2).
> > > 3. Increase the freelists on the tables participating in
> > the process to
> > > have sufficient
> > > no. of freelists for the no. of concurrent
> transactions.
> > >
> > >
> > > BTW, is there any overhead/drawback if the table is having
> > more than
> > > enough
> > > freelists?
> > > List, your suggestions pls.
> > >
> > > Regards.
> > >
> > > -----Original Message-----
> > > Sent: Thursday, May 18, 2000 5:06 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > >
> > > What ADVISABLE value , Heuristic for Calculation of the
> > same ?
> > >
> > > > -----Original Message-----
> > > > From: LBhat_at_LEVI.com [SMTP:LBhat_at_LEVI.com]
> > > > Sent: Wednesday, May 17, 2000 1:19 PM
> > > > To: Multiple recipients of list ORACLE-L
> > > > Subject: RE: Transactions waiting for Lock on Rollback
> > Segments
> > > >
> > > > Freelists are meant to be created for the tables, hence
> > you should
> > > > not try to create it on rollback segments. Instead try
> to
> > increase
> > > > the freelists on the tables which participate in online
> > transactions.
> > > >
> > > > Regards.
> > > >
> > > > Bhat
> > > >
> > > > -----Original Message-----
> > > > Sent: Wednesday, May 17, 2000 2:44 PM
> > > > To: Multiple recipients of list ORACLE-L
> > > >
> > > >
> > > >
> > > >
> > > > Banking product Application - Bancs2000
> > > >
> > > > On Oracle 7.3.4.5.0 FREELISTS for ROLLBACK segments NOT
> > allowed .
> > > > Allowed only for Tables & indexes
> > > >
> > > > SQL> create public rollback segment bm1_temp_test1
> > > > 2 tablespace tba_roll
> > > > 3 storage (initial 512K next 512K maxextents 121
> > freelists 50);
> > > > storage (initial 512K next 512K maxextents 121
> > freelists 50)
> > > >
> > *
> > > > ERROR at line 3:
> > > > ORA-02169: FREELISTS storage option not allowed
> > > >
> > > > ADVICE PLEASE
> > > >
> > > > -----Original Message-----
> > > > From: Gogala, Mladen [SMTP:MGogala_at_oxhp.com]
> > > > Sent: Tuesday, May 16, 2000 9:48 PM
> > > > To: Multiple recipients of list ORACLE-L
> > > > Subject: RE: Transactions waiting for Lock on Rollback
> > > > Segments
> > > >
> > > > Create more rollback segments.
> > > >
> > > >
> > > >
> > > > > -----Original Message-----
> > > > > From: Chris Kempster [SMTP:chris_kempster_at_hotmail.com]
> > > > > Sent: Tuesday, May 16, 2000 10:28 PM
> > > > > To: VIVEK_SHARMA
> > > > > Subject: Re: Transactions waiting for Lock on Rollback
> > Segments
> > > > >
> > > > >
> > > > > Hi there, I was wondering what the name of the
> > bench-marking
> > > application
> > > > > you were using is called?
> > > > >
> > > > > hmmm... check free-lists on the segments, it sounds
> like
> > a
> > concurrency
> > >
> > > > > issue.
> > > > >
> > > > > >From: VIVEK_SHARMA <vivek_sharma_at_inf.com>
> > > > > >Reply-To: ORACLE-L_at_fatcity.com
> > > > > >To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > > > > >Subject: Transactions waiting for Lock on Rollback
> > Segments
> > > > > >Date: Mon, 15 May 2000 23:04:28 -0800
> > > > > >
> > > > > >
> > > > > >CASE - Benchmarking Application on a Specific Machine
> E
> > 6.5 K SunOS
> > > 5.6
> > > > > >
> > > > > >AIM - To pass 50,000 OLTP Transactions in 1 Hour
> using
> > 2500 Oracle
> > > > > >sessions
> > > > > >(with respective Unix users)
> > > > > >
> > > > > >HARDWARE setup - Total 3 E6.5K machines = 2 machines
> > Containing APP
> > > ,
> > > > 1
> > > > > >machine Containing DB
> > > > > >20 GB RAM in each , 18 CPUs in each ,
> > > > > >
> > > > > >
> > > > > >
> > > > > >PROBLEM Started the firing off of OLTP transactions .
> > Till
> > Completion
> > > > of
> > > > > 10
> > > > > >Thousand (roughly) OLTP Transactions , there were NO
> > WAIT for Locks
> > > on
> > > > > Any
> > > > > >Rollback Segment
> > > > > >
> > > > > >After Completion there seemed about 8-10 Transactions
> > WAITING for
> > > Lock
> > > > on
> > > > > >Each Rollback segment
> > > > > >
> > > > > >ADVISE PLEASE
> > --
> > Author:
> > INET: LBhat_at_LEVI.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858)
> > 538-5051
> > San Diego, California -- Public Internet access /
> > Mailing Lists
> >
>
>
>
> =====
> Gaja Krishna Vaidyanatha | 3460 West Bayshore Road,
> Manager - Integration | Palo Alto, CA 94303
> & Consulting Services | gaja_at_brio.com
> Global Alliances | (650)-565-4442
> Brio Technology | www.brio.com
>
> "Opinions and views expressed are my own and not of Brio
> Technology"
>
> __________________________________________________
> Do You Yahoo!?
> Send instant messages & get email alerts with Yahoo!
> Messenger.
> http://im.yahoo.com/
> --
> Author: Gaja Krishna Vaidyanatha
> INET: gajav_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).
> --
> Author: Steve Adams
> INET: steve.adams_at_ixora.com.au
>
> 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
Received on Mon May 22 2000 - 07:41:26 CDT
