RE: Transactions waiting for Lock on Rollback Segments

Steve Wrote > 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'

What does ITL stand for ?
The name of the Book please ( pg 47) ?

On Oracle 7.3.4 with 8Kb DB_BLOCK_SIZE , for the respective TableS , should INITRANS & MAXTRANS be Altered to Control the No. of rows per block ? How ?

Gaja ,
Thanks so much for the Explanation . Was the Greatest

Gaja Wrote >"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".

Qs. 1 Should Number of Rollback Segments be INCREASED to 12,000 approx from 160
or Some intermediate value ?
NOTE That The Transactions OLTP in nature

QUERY :-
select table_name,avg_row_len
from sys.dba_tables
where ...
/

OUTPUT :-

TABLE_NAME                  AVG_ROW_LEN   NUMBER of ROWS INSERTed per Tran
-------------------------   ------------  ---------------------------------
DAILY_TRAN_DETAIL_TABLE      186            2
DAILY_TRAN_HEADER_TABLE       91            1
TEMP_DAILY_TRAN_TABLE         73            2

Thus 1 Transaction = 550 BYTES - Correct ?

Gaja Wrote >The size of the extents in the rollback segments
> can be derived by performing an avg(used_ublk)
> in v$transaction during peak periods.

OK -will revert back to you after next Transaction Runs Will use the Query :-
select avg(used_ublk) from v$transaction;

> -----Original Message-----
> From: Gaja Krishna Vaidyanatha [SMTP:gajav_at_yahoo.com]
> Sent: Monday, May 22, 2000 12:24 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
Received on Tue May 23 2000 - 00:48:51 CDT