Re: Weird database hanging

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.

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