Re: Weird database hanging
- From: "Thomas Day" <tomdaytwo@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 21 Sep 2007 16:02:18 -0400
Oracle's "cursors" are not necessarily the same as PL/SQL cursors. Any SQL
that Oracle keeps in the SGA, it calls "cursors". It may be that your
application is not using bind variables or is using dynamic SQL.
When Oracle wants to load new SQL into the SGA it querries each of these
"cursors" to see which one to swap out. Your report -
"Statistics for Last 24 Hours
Last Known Value 15743.28
Average Value 7096.27
High Value 22405
Low Value 0
My OPEN_CURSORS parameter is set to 700. I'd like to know how these
numbers all jive."
suggests that 15K cursors is taking a while to query. If your latch spin is
set to too small a number the latch will go back to sleep before Oracle can
find a cursor to swap out. Nothing will get a chance to run.
The SQL below will show the cursors that have been loaded once and only
executed once. If there are thousands of these then your application is the
problem.
-- shows cursors that are not using bind variables
select name, type, loads, executions, SHARABLE_MEM, pins, kept ,
'--------------------------------------------------------------------'
from v$db_object_cache where executions = 1 and type = 'CURSOR'
AND LOADS = 1
order by 1,5
/
Making your SGA bigger is not the answer. It simply allows more cursors to
be loaded and makes it more difficult for Oracle to find which one to swap
out.
Making the latch spin smaller (which some Oracle documentation suggests) is
not the answer because that simply makes the latch go back to sleep sooner.
Making the latch spin larger may help. It may allow Oracle to actually do
some work before the latch goes back to sleep.
I can't say that this is your problem; however, I did have a database with a
RESIN Web Ap that was using dynamic SQL without bind variables. Looking at
the most expensive SQL in STATSPACK did not solve the problem. It was when
we looked at the least expensive SQL that we found thousands of nearly
identical cursors. We also had a high number of latch spin waits.
Rewriting the Ap to use bind variables reduced the number of cursors in the
SGA from thousands to hundreds, spend up the normal through-put by 300% and
eliminated the occasional SGA thrashing that prevented any new connections.
I hope that this helps but I don't have enough info to be sure that it will.
Other related posts:
