RE: Transactions waiting for Lock on Rollback Segments

From: <LBhat_at_LEVI.com>
Date: Thu, 18 May 2000 18:55:57 -0700
Message-Id: <10501.106037@fatcity.com>

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

Increase total no. of rollback segments upto 50 (not more than that, oracle advice)
Increase transactions_per_rollback_segments parameter. You may be able to balance between (1) and (2).
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-----
From: VIVEK_SHARMA [mailto:vivek_sharma_at_inf.com] Sent: Thursday, May 18, 2000 5:06 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Transactions waiting for Lock on Rollback Segments

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 ,
> > >
> > >DATABASE Setup - Oracle 7.3.4 , 160 Rollback Segments Each with -
> EXTENT
> > >size 512 K MINEXTENTS 20 , OPTIMAL 10M
> > >
> > >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
Received on Thu May 18 2000 - 20:55:57 CDT