Re: help! different results from v$open_cursor and v$sysstat

I worked with ORACLE world-wide support to get an answer to our problem. I am reporting the findings and the final outcome to my problem here for future reference:

We were closing statements and result sets, but still received the ORA-01000 too many open cursors error. We discovered that in several cases, we were NOT closing our statements and result sets "within scope". We opened the cursor in one function, called another function, then closed the statement and result set. We cleaned up our application code to close all statements and result sets in the same function that opened them.

We also discovered, that the v$open_cursor view only reports VALID, INSTANTIATED open cursors. In our case, the cursors were closed "out of scope". They did not appear in v$open_cursor, but were being reported in the v$sesstat and v$sysstat views for statistic #3 (current opened cursors) which reports all open cursors, regardless of their status. Therefore, we were continuing to receive the ORA-01000 error even though v$open_cursor did not report any open cursors. We could not see the open cursors, because we had been instructed to look at the v$open_cursor view, and not the v$sesstat or v$sysstat views.

Lessons learned:

1. Close all statements and result sets "in scope".
2. Use v$sesstat and v$sysstat, in addition to v$open_cursor to monitor ALL open cursors (valid and invalid)

--
Carol Kuczborski
EDS/DEIS II

In article <7ml6i5$mbd$1_at_nnrp1.deja.com>,
  Carol Kuczborski <carol.kuczborski_at_eds.com> wrote:


> We are experiencing an ORA-01000 max open cursors problem.  We have

been


> researching it for a couple weeks.  Originally we were leaving cursors

> open from our Java servlet application.  We solved that by closing our

> statments and results sets within scope.

>

> Now, when we run our application under a stress load, and monitor open

> cursors with the v$open_cursors view, we see all cursors closing

> properly and none remaining open after SQL execution.  However, we

still


> receive the ORA-01000 error.  When we run the v$sysstat view and look

at


> the statistic named 'opened cursors current' we stadily see it growing

> and reporting that there are open cursors.  When run both views at the

> same time, so the totals should match.  But they don't.

>

> WHY does v$open_cursors show no open cursors, BUT v$sysstat show there

> are open cursors???????

>

> --

> Carol Kuczborski

> EDS/DEIS II

>

> Sent via Deja.com http://www.deja.com/

> Share what you know. Learn what you don't.


>



Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.