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

I second the Ask Tom script noted above. I used that to find thousands of cursors that were chewing up my shared pool, identical except for one litteral stuck in via dynamic SQL. Because each one was only executing once they didn't show up in STATSPACK reports as "costly". However, they were causing latch contention. I fixed the problem by fixing the SQL --- 300% increase in thru-put.

During my research on the problem I ran across the idea that, instead of decreasing the latch spin wait (so that Oracle would look for a free latch more quickly) I should increase the latch spin wait (so that a statement would have a chance to execute and free up a latch, instead of wasting CPU cycles seeing if a latch was available).

Since your problem is transient you probably can't experiement with altering the spin wait. But if it bacame more continuous, it might be an area to look into.

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