Session Allocation latch contention - limiting concurrent SQL
- From: scott.hutchinson@xxxxxxxxxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 10 Feb 2005 14:50:25 +0000
Zoran,
The transactions are 90% select, 10% insert. The coding is in SAP's
proprietary language ABAP. We have made many changes so data is buffered
within the application, removing the multitude of repeated selects of the same
data. This gave good results.
I've reproduced the same problem with a stand-alone java program, which
repetitively fetches data with a simple query. I then run multiple instances
of this program and see the same results.
While running the test case, the CPU utilisation is 15% on the application
servers, and 50% on the DB server. The network (20-gbit backbone with 1-gbit
NICs) runs at 5% utilisation. Running the test case locally on the DB server
gives a very small improvement only.
The most significant wait is V$LATCH #4 (session allocation) where up to 12
seconds is spent waiting in each 1 second interval (total wait incurred by all
concurrent sessions). Complete stats for this latch are in my original post.
The next most waited on latch is #157 (library cache), but this only accounts
for 1/3 of the time spent waiting on Session Allocation.
Thanks,
Scott.
Quoting Martic Zoran <zoran_martic@xxxxxxxxx>:
> Which kind of transactions you are talking here:
> inserts or mix of select and DML?
>
> Which business unit is the most critical?
> What is it doing?
>
> Usually with smartly bulking DML (if there is no so
> many failed inserts) are the best for scalability.
> I had the very good scalability results on 24 and 64
> CPU machines while using bulk inserts.
> Standard normal inserts is hard to tune on multi CPU
> machine. You will usually spend a lots of time
> designing the table and elimiating different waits
> until you hit the limit of few thousands per second
> per CPU.
>
> Let us know what you are doing exactly and which part
> you can change at all.
>
> If no code changes then you should send us the
> statistics showing on which events you are spending
> the most of the time. If all sessions are doing the
> same then one profile of time spent from that session
> will be perfect.
>
> You are of course using PreparedStatements or binds?
> Is the code in PL/SQL or C/Java/Somthing else?
>
> Also what is sar -u showing you on the machine that is
> fully loaded (can show that you are overkilling the
> machine, ....)?
>
> Regards,
> Zoran Martic
>
>
>
>
> --- scott.hutchinson@xxxxxxxxxxxxxxxxxxxxx wrote:
>
> > I have a problem where I can not increase the number
> > of concurrent SQL
> > statements running through our system. I believe
> > this is caused by contention
> > on the SESSION ALLOCATION latch while internal stats
> > are being updated.
> >
> > The highest number of SQL statements I can run is
> > 30,000 per second. I am
> > running SAP code to migration many millions of
> > customers into SAP, so we are
> > limited with the number of code changes we can make.
> > I've set timed_statistics
> > to false and statistics_level to basic. I see very
> > high values in
> > V$LATCH.WAIT_TIME for this latch(**per
> > second**:60000 Gets, 22000 Misses, 17000
> > Spin gets, 5000 sleeps, 12 secs Wait time).
> >
> > Does anyone have suggestions (other than RAC) for
> > ways to overcome this? The DB
> > server has 32 CPUs, and we have 9 application severs
> > running the code (with a
> > total of 96 CPUs).
> >
> > Thanks,
> > Scott Hutchinson
> > Interact Analysis Ltd
> >
> > ::This message sent using the free Web Mail service
> > from http://TheName.co.uk
> > --
> > //www.freelists.org/webpage/oracle-l
> >
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Mail - Easier than ever with enhanced search. Learn more.
> http://info.mail.yahoo.com/mail_250
>
::This message sent using the free Web Mail service from http://TheName.co.uk
--
//www.freelists.org/webpage/oracle-l
Other related posts:
