RE: Spotting the real cause of a Production slowdown (10g)

From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Fri, 21 Apr 2006 09:25:50 +0100

A couple of thoughts, based on Sharp increase in latch waits. Extreme CPU Random statements with extreme execution counts Growth of SGA

Increased latch waits imply greater contention, which means more CPU time in spinning - so the first two are concurrent symptoms. Of course, because everyone's CPU usage goes up, the time spent by an individual session in the run queue but not running may go up, which makes latch waits worse.

Do you see an increase in the number of processes, and
is the application working through a fairly typical application
server layer ? A common scenario:

1    database server gets a little overloaded and slows down

2    application server sees database response get worse so
       launches another half-dozen processes

3    A process connection requires a large contiguous chunk of
   memory in the shared pool - can't get it, so start a massive
   flush of ilbrary cache (and eventually triggers the auto resize)

4    Massive flush of library cache hammers library cache latches
   so database response slows down

go to para 2 above The presence of high execution counts on "random" statements could be because the respones doesn't get back to the application server in time, amd fires the query again through another connection (making the connect loading worse).

Typical solution - size the shared_pool_reserved to allow for all the sessions that might connect. Something around 25KB times sessions plus space for all the other stuff that you can find in there at present. (sum x$ksmspr for anything that doesn't look like 'session param' and add a bit.


To check:
   v$resource_limit to check peaks in process and session
   counts.

v$sgastat - during the problem to see if free memory is increasing (beyond the memory added by resize).

   x$ksmspr - I think Steve Adams has some comments
   about what this can tell you, but it can give you a break
   down of usage that MAY be useful - handle with care.

Do not touch:
   x$ksmsp

By the way, is the library cache growing by reducing the db cache, or simply growing into available memory ?


Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

--
//www.freelists.org/webpage/oracle-l

RE: Spotting the real cause of a Production slowdown (10g)

Other related posts: