Re: Spotting the real cause of a Production slowdown (10g)
- From: "Thomas Day" <tomday2@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 21 Apr 2006 09:35:15 -0400
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.
Other related posts:
