Understand V $ OPEN_CURSOR, V $ SESSION_CACHED_CURSOR [zt]

These two concepts are confusing.
Concept:
OPEN_CURSOR defines the maximum number of cursors that can be opened by each Session. You can use select * from v $ parameter where name = 'open _ cursors 'to query data defined in the init. ora file.
V $ OPEN_CURSOR: the current Session cache cursor, instead of the previously opened cursor. V $ SESSION_CACHED_CURSOR: The cursor that is closed and cached by the current Session.
The current Session cursor cache cursor displayed in V $ OPEN_CURSOR. to precisely query the total number of opened cursors of the current Session, you need to query them from V $ SESSTAT.

Select a. value,
S. username,
S. sid,
S. serial #
From
V $ sesstat,
V $ statname B,
V $ session s
Where
A. statistic # = B. statistic # and
S. sid = a. sid and
B. name = 'opened cursors current ';

Principle of Session Cache:
When the SESSION_CACHED_CURSOR value is set, Oracle queries from the library cache when there is a parse request. If there are more than three identical parse requests, the cursor will be saved to the Session's cursor cache. For the same query in the future, do not even use soft parse to retrieve it directly from the Session cursor cache.

Verification:
Log on to two SQL * PLUS clients: Session test and Session monitor.

1. Check whether the parameter settings and the executed SQL statement are found in V $ OPEN_CURSOR.Session Test:
SQL> show parameter session_cached_cursors;

NAME TYPE VALUE
------------------------------
Session_cached_cursors integer 0
SQL> select sid from v $ mystat where rownum = 1;
SID
----------
9
SQL> select sid from v $ mystat where rownum = 1;
SID
----------
9

The preceding execution result shows that the current parameter sets session_cached_cursors to 0 and does not cache the cursor closed by the current Session. The ID of the current Session is 9.Session Monitor:SQL> SELECT SID, n. NAME para_name, s. VALUE used
2 from sys. v _ $ statname n, SYS. v _ $ sesstat s
3 WHERE n. name in ('opened cursors current ', 'session cursor cache count ')
4 AND s. statistic # = n. statistic #5 and sid = 9;
SID PARA_NAME USED
-------------------------------------------
9 opened cursors current 1
9 session cursor cache count 0
SQL> select SID, USER_NAME, SQL _TEXT from v $ open_cursor where sid = 9;
SID USER_NAME SQL _TEXT
------------------------------------------------------------------------
9 SCOTT select sid from v $ mystat where rownum = 1

The preceding execution result shows that a cursor is currently stored in V $ OPEN_CURSOR and the last statement executed by Session Test is corresponding to the SQL statement. V $ SESSION_CACHED_CURSOR does not store the cursor.

2. Change the value of the V $ SESSION_CACHED_CURSOR parameter.Session Test:
SQL> alter session set session_cached_cursors = 1;
Session altered.
SQL> show parameter session_cached_cursors;
NAME TYPE VALUE
------------------------------------------------------------------------
Session_cached_cursors integer 1

3. Verify the following conclusions. If the cursor is saved to SESSION_CACHED_CURSOR, the cursor is closed and the SQL statement corresponding to the cursor is executed more than three times. OPEN_CURSOR is stored in SESSION_CACHED_CURSOR and the opened cursor (not an exact value ).Session Test:
SQL> select sid from v $ mystat where rownum = 1;
SID
----------
9
SQL> select sid from v $ mystat where rownum = 1;
SID
----------
9
SQL> select sid from v $ mystat where rownum = 1;
SID
----------
9

Session Monitor:
SQL> select SID, USER_NAME, SQL _TEXT from v $ open_cursor where sid = 9;
SID USER_NAME SQL _TEXT
------------------------------------------------------------------------
9 SCOTT select sid from v $ mystat where rownum = 1
SQL> SELECT SID, n. NAME para_name, s. VALUE used
2 from sys. v _ $ statname n, SYS. v _ $ sesstat s
3 WHERE n. name in ('opened cursors current ', 'session cursor cache count ')
4 AND s. statistic # = n. statistic #
5 and sid = 9;
SID PARA_NAME USED
-------------------------------------------
9 opened cursors current 1
9 session cursor cache count 0

V $ open_cursor dooes not show all open cursors. it shows more than that, the best option to find the number of open cursors is from v $ sysstat. the preceding execution result shows that the number of cursor instances in SESSION_CACHED_CURSOR is still 0 even if the same cursor is opened three times.

Next, the cursor will be closed in Session Test (by executing another statement ).

Session Test:SQL> select * from t where rownum! = 7;
No rows selected

Session Monitor:
SQL> SELECT SID, n. NAME para_name, s. VALUE used
2 from sys. v _ $ statname n, SYS. v _ $ sesstat s
3 WHERE n. name in ('opened cursors current ', 'session cursor cache count ')
4 AND s. statistic # = n. statistic #
5 and sid = 9;
SID PARA_NAME USED
-------------------------------------------
9 opened cursors current 1
9 session cursor cache count 1
SQL> select SID, USER_NAME, SQL _TEXT from v $ open_cursor where sid = 9;
SID USER_NAME SQL _TEXT
------------------------------------------------------------------------
9 SCOTT select sid from v $ mystat where rownum = 1
9 SCOTT select * from t where rownum! = 7

The preceding execution result shows that after the cursor is opened three times, if it is closed, the cursor will be stored in SESSION_CACHED_CURSOR. In addition, the SQL statement shown in OPEN_CURSOR shows that OPEN_CURSOR stores the SQL statement in SESSION_CACHED_CURSOR AND THE opened cursor (not the exact value ).

Others:SESSION_CACHED_CURSOR uses the LRU algorithm. If a new cursor needs to be cached and the current cursor cache is full, the cursor that is used at least will be cleared. Adjust the SESSION_CACHED_CURSOR parameter. Use the following SQL statement to obtain the cursor from the cache and the number of PARSE. This is a reference for adjustment. Select cach. value cache_hits, prs. value all_parses,
Prs. value-cach.value sess_cur_cache_not_used
From v $ sesstat cach, v $ sesstat prs,
V $ statname nm1, v $ statname nm2
Where cach. statistic # = nm1.statistic # and
Nm1.name = 'session cursor cache hits 'and
Prs. statistic # = nm2.statistic # and
Nm2.name = 'parse count (total) 'and
Cach. sid = & sid and prs. sid = cach. sid;

Refer:
1. Monitoring Open and Cached Cursors
2. session_cached_cursor