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

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

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