V $ open_cursor and session_cached_cursor

V $ open_cursor and session_cached_cursor

V $ open_cursor is a view provided by oracle to view the cursor opened by each session. Its table structure is as follows:
SQL> desc v $ open_cursor
Name Null? Type
-------------------------------------------------------------------------------------------------
Saddr raw (4)
SID NUMBER
USER_NAME VARCHAR2 (30)
Address raw (4)
HASH_VALUE NUMBER
SQL _TEXT VARCHAR2 (60)

When we execute an SQL statement, we will generate a library cache object in the shared pool. cursor is a library cache object for SQL statements. in addition, we will have a copy of cursor in pga and a statement handle on the client. These are called cursor, in v $ open_cursor, we can see the currently opened cursor and cached cursor in pga.

Session_cached_cursor
This parameter limits the length of the session cursor cache list in pga. The session cursor cache list is a bidirectional lru linked list. When a session intends to close a cursor, if the parse count of the cursor exceeds three times, the cursor will be added to the mru end of the session cursor cache list. when a session intends to parse an SQL statement, it will first search for the session cursor cache list in pga. If it is found, the cursor will be removed from the list, then add the cursor to the MRU end when it is disabled. session_cached_cursor provides fast soft analysis and higher performance than soft parse.

The following is an experiment to prove the above theories.

SQL> select distinct sid from v $ mystat;

SID
----------
12

SQL> show parameter session_cached_cursors

NAME TYPE VALUE
-----------------------------------------------------------------------------
Session_cached_cursors integer 0

This is to set session_cached_cursors to 0. The cache cursor function is disabled.

Let's open another sqlplus to see v $ open_cursor.

SQL> select * from v $ open_cursor where sid = 12;

Saddr sid USER_NAME ADDRESS HASH_VALUE SQL _TEXT
------------------------------------------------------------------------------------------------------------------------------
95D5AFCC 12 TAOBAO 99DA7F20 745629022 select distinct sid from v $ mystat

You can see this SQL statement in v $ open_cursor.

Execute the next statement
SQL> select owner from test where rownum = 1;

OWNER
------------------------------
SYS

Let's take a look at the changes in v $ open_cursor.

SQL>/

Saddr sid USER_NAME ADDRESS HASH_VALUE SQL _TEXT
------------------------------------------------------------------------------------------------------------------------------
95D5AFCC 12 TAOBAO 99C9AEF8 608211958 select owner from test where rownum = 1

We can see that the statement has changed. Let's look at the number of current open cursor and session cached cursor.
SQL> select sid, n. name, s. value used
2 from
3 sys. v _ $ statname n,
4 sys. v _ $ sesstat s
5 where
6 n. name in ('opened cursors current ', 'session cursor cache count') and
7 s. statistic # = n. statistic #
8 and sid = 12;

SID NAME USED
------------------------------------------------------------------------------------
12 opened cursors current 1
12 session cursor cache count 0

Modify session_cached_cursors to 1.
SQL> alter session set session_cached_cursors = 1;

Session altered.

Then execute the preceding statement.

SQL> select owner from test where rownum = 1;

OWNER
------------------------------
SYS

Look at v $ open_cursor.

SQL> select * from v $ open_cursor where sid = 12;

Saddr sid USER_NAME ADDRESS HASH_VALUE SQL _TEXT
------------------------------------------------------------------------------------------------------------------------------
95D5AFCC 12 TAOBAO 99C9AEF8 608211958 select owner from test where rownum = 1

 

Here, select * from v $ open_cursor where sid = 12 is executed three times, and oracle performs soft parse three times.
SQL> select owner from test where rownum = 1;

OWNER
------------------------------
SYS
SQL> select owner from test where rownum = 1;

OWNER
------------------------------
SYS

Then execute

SQL> select distinct sid from v $ mystat;

SID
----------
12

Let's take a look at the changes in v $ open_cursor.
SQL>/

Saddr sid USER_NAME ADDRESS HASH_VALUE SQL _TEXT
------------------------------------------------------------------------------------------------------------------------------
95D5AFCC 12 TAOBAO 99C9AEF8 608211958 select owner from test where rownum = 1
95D5AFCC 12 TAOBAO 99DA7F20 745629022 select distinct sid from v $ mystat

V $ open_cursor has two records. Let's look at the number of current open cursor and session cached cursor.

SQL> select sid, n. name, s. value used
2 from
3 sys. v _ $ statname n,
4 sys. v _ $ sesstat s
5 where
6 n. name in ('opened cursors current ', 'session cursor cache count') and
7 s. statistic # = n. statistic #
8 and sid = 12;

SID NAME USED
------------------------------------------------------------------------------------
12 opened cursors current 1
12 session cursor cache count 1

A SQL statement is included in the session cursor cache. "select owner from test where rownum = 1" the SQL-related cursor is cached.

Execute a new statement.
SQL> select owner from test where rownum <2;

OWNER
------------------------------
SYS

Let's take a look at v $ open_cursor.
SQL> select * from v $ open_cursor where sid = 12;

Saddr sid USER_NAME ADDRESS HASH_VALUE SQL _TEXT
------------------------------------------------------------------------------------------------------------------------------
95D5AFCC 12 TAOBAO 99CA4FFC 2584499260 select owner from test where rownum <2
95D5AFCC 12 TAOBAO 99C9AEF8 608211958 select owner from test where rownum = 1

We can see that the previous "select distinct sid from v $ mystat" has been disabled and is invisible in v $ open_cursor, however, cached cursor "select owner from test where rownum = 1" can still see

At this time, select distinct sid from v $ mystat is run three times.
SQL> select distinct sid from v $ mystat;

SID
----------
12
SQL> select distinct sid from v $ mystat;

SID
----------
12
SQL> select distinct sid from v $ mystat;

SID
----------
12

Then run select owner from test where rownum <2

SQL> select owner from test where rownum <2;

OWNER
------------------------------
SYS

View v $ open_cursor again

SQL> select * from v $ open_cursor where sid = 12;

Saddr sid USER_NAME ADDRESS HASH_VALUE SQL _TEXT
------------------------------------------------------------------------------------------------------------------------------
95D5AFCC 12 TAOBAO 99C6C628 1755089113 select owner from test where rownum <2
95D5AFCC 12 TAOBAO 99DA7F20 745629022 select distinct sid from v $ mystat

The original cached cursor "select owner from test where rownum = 1" has been replaced by the session cursor cache and changed to "select distinct sid from v $ mystat"

Modify session_cached_cursors.

Alter session set session_cached_cursors = 2;

Repeat the above process and go to v $ open_cursor.

SQL>/

Saddr sid USER_NAME ADDRESS HASH_VALUE SQL _TEXT
------------------------------------------------------------------------------------------------------------------------------
95D5AFCC 12 TAOBAO 99CA4FFC 2584499260 select owner from test where rownum <2
95D5AFCC 12 TAOBAO 99C9AEF8 608211958 select owner from test where rownum = 1
95D5AFCC 12 TAOBAO 99DA7F20 745629022 select distinct sid from v $ mystat

We can see that there are three cursor instances. Check the number of current open cursor instances and session cached cursor instances.

SQL> select sid, n. name, s. value used
2 from
3 sys. v _ $ statname n,
4 sys. v _ $ sesstat s
5 where
6 n. name in ('opened cursors current ', 'session cursor cache count') and
7 s. statistic # = n. statistic #
8 and sid = 12;

SID NAME USED
------------------------------------------------------------------------------------
12 opened cursors current 1
12 session cursor cache count 2

There are two cached cursor and one current open cursor.

From this experiment, we can clearly see the functions of the v $ open_cursor view and session_cached_cursors parameters.

Here I will mention another parameter open_cursors related to cursor, which limits the maximum number of cursor that can be opened by each session. If the number of cursor currently opened exceeds open_cursor, A ORA-01000 will be reported: maximum open cursors exceeded, usually when using a java connection pool if improperly handled will cause a ORA-01000 if the java application server uses soft close cursor and keeps the cursor in cache size, the database considers this cursor to be open ,. therefore, parameters such as open_cursors and session_cached_cursors must be matched with the cursor cache size of the application.