Re: Session Allocation latch contention - limiting concurrent SQL
- From: Martic Zoran <zoran_martic@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 10 Feb 2005 06:10:01 -0800 (PST)
Thanks Scott,
That is now more clear.
See comments inline:
--- scott.hutchinson@xxxxxxxxxxxxxxxxxxxxx wrote:
> Zoran,
>
> The transactions are 90% select, 10% insert. The
> coding is in SAP's proprietary language ABAP.
When you are accusing a lots of SQL's for being the
problem you should probably know for which particular
type of SQL this is happening?
On which action/SQL these waits are attached?
Sorry for not knowing SAP, but are they using a
connection/session management on the web server side
causing creating/terminating sessions in OCI/JDBC?
> 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.
Of course. This is going at least to remove the
networking piece that is always a big piece while
doing very fast SQL's (lookups, ...).
> I've reproduced the same problem with a stand-alone
> java program, which repetitively fetches data with >
a simple query. then run multiple instances of this >
program and see the same results.
What is SQL looks like?
Did your test do any session/connection management, or
just make the connection and do a lots of SQL's?
And these SQL's are done with PreparedStatement with
the bind variables usage?
To be consistent. I will ask again the question from
the beggining: do you have hard parsing of these SQL's
on the server side? Are you using binds?
> 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.
Did you count SQL*Net waits from client as waits?
What is the percentage of app, database and network
elapsed time for one particular business unit causing
the problem?
> Running the test case locally on the DB server gives
a very small improvement only.
If you used the TCP/IP connection then the improvement
was only the difference between remote and local
server SQL*NEt wait. In your case small.
Still, we should know how big chunk is
"SQL*Net message from client" (my usually suspect in
fast SQL's) for the particular session or even better
critical business unit (tracing with 10046).
> 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.
Huh, latches, what was that :)
The problem with latches is that sometimes waiting on
one latch more time then on the other is not giving
you the proper answer. Mostly because the latches are
related to each other. You can hold one latch while
requesting another.
Would you execute this latch_sleeps script from ixora
site:
http://www.ixora.com.au/scripts/latches.htm#latch_sleeps
The latch level will tell you which latch is more
important. Also impact will tell you which latches are
with highest impact. Of course these figures are
global, so you need to find out which latch is
basically doing the biggest damage to your
performances.
By tuning the proper latch/action (that does not
necesseraly need to be session allocation) your waits
can be gone.
Sorry for asking so many questions.
Hopefully this will help somebody else to help you out
:)
Regards,
Zoran
__________________________________
Do you Yahoo!?
Meet the all-new My Yahoo! - Try it today!
http://my.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
