RE: Transactions waiting for Lock on Rollback Segments

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
> --------------------------------------------------------------------
> 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
Received on Fri May 19 2000 - 00:28:32 CDT