Re: v$open_cursor and session_cached_cursors

From: crappy <crappygolucky_at_hotmail.com>
Date: 16 Oct 2001 11:49:04 -0700
Message-ID: <ce31c410.0110161049.587d0adf@posting.google.com>

thanks for the responses. i think i understand the session_cached_cursors bit now. however, i'm still wondering about the problem that got me on this track in the first place, and that is, trying to differentiate what cursors are properly closed but cached, and which were inadvertently left open by the java app.

from docs and articles at asktom, i understand that v$open_cursor shows sql_text's that aren't necessarily open cursors, just ones that have been opened at some point: so if i flush the shared pool the ones that had been closed explicitly do go away.

e.g., i have the same kind of simple procedure (much like Thomas', does an open/fetch/close of a "select * from dual" cursor), and after executing it i get

SQL> select sql_text from v$open_cursor;

SQL_TEXT

---

SELECT * FROM DUAL
select sql_text from v$open_cursor

SQL> alter system flush shared_pool;

System altered.

SQL> select sql_text from v$open_cursor;

SQL_TEXT

---

select sql_text from v$open_cursor

... which i expected. then, i thought that using v$mystat.statistic# (= 3) would show the "real" number of cursors that are truly opened, but it showed a count of 1 before the execution of the proc, and then 2 afterwards, and also after the flush. why is that? is there some other cursor popping up? tia .. Received on Tue Oct 16 2001 - 13:49:04 CDT