oracle cursor

Oracle will generate something called cursor after it parse an statement. This thing--cursor will be stored in Library cache, if next time you need to execute the same statement, you can use that cursor and this will reduce the work oracle need to do. But cursor will consume resources like memory space. So if one session open a lot cursors -- one million for example. This may exhausting the memory space of entire DB. So oracle introduce a initialization parameter called open_cursors. This parameter controls the upper limit for how many cursors a session can open concurrently.

SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300

The above information means you can open no more than 300 cursors concurrently.

But how to check how many cursors we currently opening in a session? Two methods might shows in your mind immediately.

1. v$open_cursor  performance view

2. "opened cursors current" statistic information in v$statname

Actually it is all wrong.  The definiation in oracle(10.2)official document saying that "This view lists cursors that each user session currently has opened and parsed."   But actually this is a lie. The v$open_cursors contains information about cursors you currently opened and cursors you already closed but cached in session.  

To understand this, you have to know another initialization parameter —— session_cached_cursors

SQL> show parameter session_cached_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     100

This parameter controls how many cursor can be cached in your session.  Oracle session will automatically cache some cursors incase they will use them further.  The benefit for this is very obvious. By caching the cursor you open frequently, you can reduce the access to library cache hence reduce concurrent workload on library cache.  This is a good design for oracle. But the problem is that oracle put those cached cursor information in v$open_cursors view. So query this view to find out the cursors you currently opend is not an option.  Check below experiment you may get more clear about this.

We open a session which is called test here and we input a statement.

SQL> select sid from v$mystat where rownum<2;

       SID
----------
       525

Then we open a new session called monitor to check 

SQL> select saddr,sid,user_name,sql_text from v$open_cursor where sid=525;

SADDR                   SID USER_NAME          SQL_TEXT
---------------- ---------- ------------------ ------------------------------------------------------------
00000003C38F5350        525 SYS                select type#,blocks,extents,minexts,maxexts,extsize,extpct,u
00000003C3A6E408        525 SYS                select sid from v$mystat where rownum<2

The result is reasonable. First line is some system cursor. The second line shows the cursor we currently opened. Then we go back to the previous session run another statement. This action will close the previous cursor. (Issue a new statement in sqlplus will cause the previcous statment close its cursor)

SQL> select * from dual;

D
-
X

Now, a new statement was issued in the test session, we check the monitor session.

SQL> select saddr,sid,user_name,sql_text from v$open_cursor where sid=525;

SADDR                   SID USER_NAME          SQL_TEXT
---------------- ---------- ------------------ ------------------------------------------------------------
00000003C38F5350        525 SYS                select type#,blocks,extents,minexts,maxexts,extsize,extpct,u
00000003C3A6E408        525 SYS                select sid from v$mystat where rownum<2
00000003C3A6E408        525 SYS                select * from dual

Well, we can see the previous two statement still there. The cursor for "select sid from v$mystat where rownum<2" should been closed in the test session. But we can still see it.

But if you set the session_cached_cursor to 0 which means no cursor can be cached in the session. You will see something different.  Let`s do the experiment again with a tiny different.

Open a test session and issue below statement.

SQL> alter session set session_cached_cursors = 0 ;

Session altered.

SQL> select sid from v$mystat where rownum<2;

       SID
----------
       525

Now go to monitor session check.

SQL> select saddr,sid,user_name,sql_text from v$open_cursor where sid=525;

SADDR                   SID USER_NAME          SQL_TEXT
---------------- ---------- ------------------ ------------------------------------------------------------
00000003C38F5350        525 SYS                select type#,blocks,extents,minexts,maxexts,extsize,extpct,u
00000003C3A6E408        525 SYS                select sid from v$mystat where rownum<2

Ok we have two records in this view. The system cursor and the cursor we are opening. Now let`s issue another statement in the test session. This will cause the cursor for "select sid" close. 

SQL> select * from dual;

D
-
X

Now we check the v$open_cursors in monitor view

SQL> select saddr,sid,user_name,sql_text from v$open_cursor where sid=525;

SADDR                   SID USER_NAME          SQL_TEXT
---------------- ---------- ------------------ ------------------------------------------------------------
00000003C38F5350        525 SYS                select type#,blocks,extents,minexts,maxexts,extsize,extpct,u
00000003C3A6E408        525 SYS                select * from dual

Now you can see, the cursor for "select sid" is gone. Because we do not permit to cache cursor in session. So once the cursor is closed, it is gone. You can not find it in v$open_cursor view.  

OK. summarize, v$open_cursors contains information about cursors you currently opening and cursors cached in your session. 

Then what about the second way? Using v$sesstat and v$statname to check the statistics "session_cached_cursors" seems reasonable. But it is also not accurate. Because it is possible that even you closed a cursor, the number for "opened cursors current" still not decreasing. Because when you close a cursor, oracle mark this cursor as closeable. Oralce will not remove the cursor untile it need the resource occupied by the cursor. This information is get from below link  http://www.orafaq.com/node/758 and I have pasted the related content below.

Frustratingly for developers, the session statistic ‘currently open cursors‘ can include some cursors that the application has closed. When application code calls for a cursor to be closed, Oracle actually marks the cursor as "closeable". The cursor may not actually be closed until Oracle needs the space for another cursor.

So it‘s not possible to test to see if a complex application is closing all its cursors by starting a session, running a test, and then checking to see if currently open cursors has gone down to 1. Even if the application is closing all its cursors properly, currently open cursors may report that some "closeable" cursors are still open.

View Code

But we still have to use this inaccurate method because there seems  no more accurate way.

SELECT SID, n.NAME para_name, s.VALUE used FROM SYS.v_$statname n, SYS.v_$sesstat s   WHERE n.NAME IN (‘opened cursors current‘, ‘session cursor cache count‘,‘opened cursors cumulative‘)   AND s.statistic# = n.statistic#  AND SID =525;

       SID PARA_NAME                                                              USED
---------- ---------------------------------------------------------------- ----------
       525 opened cursors cumulative                                               106
       525 opened cursors current                                                    1
       525 session cursor cache count                                                0

oracle cursor,古老的榕树,5-wow.com