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

From: VIVEK_SHARMA [mailto:vivek_sharma_at_inf.com] 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
Received on Fri May 19 2000 - 00:52:06 CDT