Re: Session Allocation latch contention - limiting concurrent SQL

  • From: Martic Zoran <zoran_martic@xxxxxxxxx>
  • To: scott.hutchinson@xxxxxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 10 Feb 2005 02:07:26 -0800 (PST)

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, ....)?

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
> --

Do you Yahoo!? 
Yahoo! Mail - Easier than ever with enhanced search. Learn more.

Other related posts: