watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

          subject         part

stay Oracle in , What is a session cursor ? What are the differences between session cursors and shared cursors ? What are the types of session cursors ?


     
          Answer section          



( One ) The meaning of the session cursor

Session cursor (Session Cursor) It's the current conversation (Session) Parsing and execution SQL The carrier of , That is, the session cursor is used to parse and execute in the current session SQL, Session cursors are cached in a hash table PGA in ( Shared cursors are cached in SGA In the library cache of ). In the target SQL During the execution of , The session cursor serves as a link between the preceding and the following . because Oracle Rely on the session cursor to place the target SQL The data involved are from Buffer Cache The corresponding data block of reads PGA in , And then in PGA I'm going to do the following sort of work 、 Table connection, etc , Finally, the final processing result is returned to the user , therefore , The session cursor is the current session resolution and execution SQL The carrier of .

The comparison between shared and session cursors is shown in the following table :


Shared cursors (Shared Cursor)

Session cursor (Session Cursor)

The cache location

cached SGA The library cache in the shared pool in (Library Cache) in .

It's cached in each session's PGA in .

share

Shared cursors are shared across all sessions .

Session cursor (Session Cursor) Conversation with (Session) It's one-to-one , Session cursors of different sessions cannot be shared , This is with shared cursors (Shared Cursor) The essential difference .

Life cycle

Shared cursors have no lifecycle , Will cache .

Session cursors have a life cycle , Each session cursor goes through at least one Open、Parse、Bind、Execute、Fetch and Close One or more stages of .Oracle According to the parameters SESSION_CACHED_CURSORS To determine whether to cache used session cursors in the corresponding session PGA in .

contact

1. Session cursors are cached in a hash table PGA in , signify Oracle Will be stored and accessed in the current session through the relevant hash operation PGA The corresponding session cursor in . This access mechanism is the same as shared cursors , You can simply think of Oracle According to the goal SQL Of SQL The hash value of the text PGA The corresponding in Hash Bucket Find a matching session cursor in . Because the hash table of the cursor in the cache session corresponds to Hash Bucket in ,Oracle Will store the target SQL The address of the library cache object handle of the corresponding parent cursor , therefore ,Oracle You can find the corresponding parent cursor through the session cursor , Then you can find the target in the corresponding sub cursor SQL The parse tree and execution plan of , then Oracle You can reuse the target SQL The parse tree and execution plan of the SQL Statement .

2. A session cursor can only correspond to one shared cursor , A shared cursor can correspond to multiple session cursors at the same time .

( Two ) Classification of session cursors

Please refer to the following table for detailed classification of session cursors :

surface 3-20 Oracle The classification of session cursors in

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=


In the table above, it should be noted that , Dynamic cursors are Oracle One of the most flexible session cursors in a database , Its flexibility is shown in :① Dynamic cursors are very flexible to define , It can be defined in many ways .② Dynamic cursors can be used as input parameters of stored procedures and output parameters of functions . The various cursors in the table above are expected to be mastered by readers by doing a lot of exercises , After all, cursors are essential to the development of stored procedures .

( 3、 ... and ) Properties of the session cursor

Session cursors have 4 Attributes , See the table below :

surface 3-21  Properties of the cursor

attribute

type

brief introduction

Applicable to

apply SQL

SQL%FOUND

Boolean type

Current FETCH Whether to extract data , It means one SQL After the statement is successfully executed, whether the number of records affected by it is greater than or equal to 1, If so, the assignment is TRUE, Otherwise FALSE. In one DML Before a statement is executed ,SQL%FOUND The value of is NULL.

Implicit cursors 、 Explicit cursors

INSERT、DELETE、UPDATE、SELECT ... INTO ...

SQL%NOTFOUND

Boolean type

Current FETCH Whether the data is not extracted , It means one SQL Whether the number of records affected by the successful execution of a statement is 0, If so, the assignment is TRUE, Otherwise FALSE. In one DML Before a statement is executed ,SQL%NOTFOUND The value of is NULL.

Implicit cursors 、 Explicit cursors

SQL%ROWCOUNT

Numerical type

It means the most recent one SQL The number of records that are affected by a statement after it has been successfully executed , Follow up execution of SQL Will be covered SQL%ROWCOUNT Value .

Implicit cursors 、 Explicit cursors

SQL%ISOPEN

Boolean type

Whether the cursor is open , Returns when the cursor is open TRUE. For implicit cursors ,SQL%ISOPEN The value of is always FALSE.

Explicit cursors

When a DML After the statement ,DML The results of the statement are stored in these four cursor properties , These properties are used to control the program flow or to understand the state of the program . When running DML When the sentence is ,PL/SQL Open a built-in cursor and process the result . In these properties ,SQL%FOUND and SQL%NOTFOUND Boolean value ,SQL%ROWCOUNT It's an integer value . It should be noted that , If the cursor is implicit , It's in PL/SQL The properties in the above table can be used directly in , If the cursor is an explicit cursor , In the properties in the above table “SQL%” Need to be replaced with the name of the custom explicit cursor . This in the table above 4 Properties still apply to dynamic cursors .

( Four ) Parameters related to session cursor

There are two important parameters related to session cursors , Respectively OPEN_CURSORS and SESSION_CACHED_CURSORS, These two parameters are described in detail below .

(1) Parameters OPEN_CURSORS It is used to set that in a single session, you can use OPEN Total number of session cursors with state coexistence , The default value is 50. If the value is 300, It means that in a single session, you can use OPEN The total number of session cursors with state coexistence cannot exceed 300, otherwise Oracle Will report a mistake “ORA-1000:maximum open cursors exceeded”. View V$OPEN_CURSOR Can be used to query the status of the database OPEN Or has been cached in PGA Number and details of session cursors in ( for example ,SQL_ID and SQL Text etc. ). Of course , Or from the view V$SYSSTAT We found all of the current OPEN The total number of session cursors that state exists .

[email protected] > show parameter open_cursors
2
3NAME                                 TYPE        VALUE
4------------------------------------ ----------- ------------------------------
5open_cursors                         integer     65535
6
7SELECT USERENV('SID') FROM DUAL;
8SELECT * FROM V$OPEN_CURSOR WHERE SID=16;
9SELECT * FROM V$SYSSTAT D WHERE D.NAME ='opened cursors current';
     


(2) Parameters SESSION_CACHED_CURSORS It is used to set that in a single session Soft Closed The state cache is in PGA The total number of session cursors in . stay Oracle 10g China and Murdoch think 20( Be careful : The default value recorded in the official document is 0 It's wrong ),11g China and Murdoch think 50.

[email protected] > show parameter session_cached_cursors
2
3NAME                                 TYPE        VALUE
4------------------------------------ ----------- ------------------------------
5session_cached_cursors               integer     50
     


It can be seen from the above results that ,SESSION_CACHED_CURSORS The value of is 50, It means in this library , In a single conversation, you can use Soft Closed The state cache is in PGA The total number of session cursors in cannot exceed 50.

About parameters SESSION_CACHED_CURSORS The following points need to be noted :

① Oracle Will use LRU Algorithm to manage these cached session cursors ( From the dump This can be confirmed in the document ), So even if one Session With Soft Closed The state cache is in PGA The total number of session cursors in has reached SESSION_CACHED_CURSORS It doesn't matter what the upper limit is ,LRU The algorithm is still able to ensure that the frequent repeated execution of SQL The cache hit rate of the corresponding session cursors is higher than that of those that do not execute repeatedly SQL.

① stay Oracle 11gR2 in , A session cursor can be cached in PGA The necessary condition in is that the session cursor corresponds to SQL Parsing and executing more than 3 Time .Oracle The purpose of this is to avoid those that are executed very few times SQL The corresponding session cursor is also cached in PGA in , these SQL It is likely to be executed only once and not repeatedly , So take those that are executed a few times SQL The corresponding session cursor is cached in PGA It doesn't make much sense . You can use the following SQL Statement to query all session cursors cached in the current system :

1 SELECT D.INST_ID, D.SQL_ID,D.SQL_TEXT,D.SID,D.USER_NAME,D.HASH_VALUE FROM GV$OPEN_CURSOR D WHERE D.CURSOR_TYPE='SESSION CURSOR CACHED';
     

Here is an example of session cursor caching :

 1 [email protected] > alter system flush shared_pool;-- The production warehouse should be used with caution
 2
 3System altered.
 4
 5-- Start the first 1 Execution times
 [email protected] > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
 7
 8no rows selected
 9
[email protected] > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;
11
12  COUNT(*)
13----------
14        14
15
[email protected] > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
17
18no rows selected
19
20-- Start the first 2 Execution times :
[email protected] > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;
22
23  COUNT(*)
24----------
25        14
26
[email protected] > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
28
29no rows selected
30
31-- Start the first 3 Execution times :
[email protected] > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;
33
34  COUNT(*)
35----------
36        14
37
[email protected] > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
39
40SQL_ID        CURSOR_TYPE
41------------- ----------------------------------------------------------------
429r01dt51f46tf DICTIONARY LOOKUP CURSOR CACHED
43 As you can see from the results , Although it has been cached to PGA It's in , But the type is “DICTIONARY LOOKUP CURSOR CACHED”, Not at all “SESSION CURSOR CACHED”, So let's start with 4 Execution times :
[email protected] > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;
45
46  COUNT(*)
47----------
48        14
49
[email protected] > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
51
52SQL_ID        CURSOR_TYPE
53------------- ----------------------------------------------------------------
549r01dt51f46tf SESSION CURSOR CACHED
55
[email protected] > SELECT a.VERSION_COUNT,a.EXECUTIONS,a.PARSE_CALLS,a.LOADS FROM v$sqlarea a WHERE a.SQL_ID='9r01dt51f46tf';
57
58VERSION_COUNT EXECUTIONS PARSE_CALLS      LOADS
59------------- ---------- ----------- ----------
60            1          4           3          1
61
62-- As you can see from the results , stay SQL sentence “SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;” The first 4 After this execution ,Oracle The corresponding session cursor has been cached in the PGA It's in , At this time, the type of the cached session cursor is “SESSION CURSOR CACHED”. Let's start with 5 Execution times :
[email protected] > SELECT /*test scc*/ COUNT(*) FROM SCOTT.EMP;
64
65  COUNT(*)
66----------
67        14
68
[email protected] > SELECT D.SQL_ID,D.CURSOR_TYPE FROM V$OPEN_CURSOR D WHERE D.SID=USERENV('SID') AND D.SQL_TEXT LIKE 'SELECT /*test scc*/ COUNT(*)%' ;
70
71SQL_ID        CURSOR_TYPE
72------------- ----------------------------------------------------------------
739r01dt51f46tf SESSION CURSOR CACHED
74
[email protected] > SELECT a.VERSION_COUNT,a.EXECUTIONS,a.PARSE_CALLS,a.LOADS FROM v$sqlarea a WHERE a.SQL_ID='9r01dt51f46tf';
76
77VERSION_COUNT EXECUTIONS PARSE_CALLS      LOADS
78------------- ---------- ----------- ----------
79            1          5           3          1
     

And it turns out that , The cached session cursor is still of type “SESSION CURSOR CACHED”, No more changes .

( 5、 ... and ) Session cursor's dump file

Session cursor's dump Documents can be passed through Level The value is 3 Of errorstack obtain , The acquisition process is as follows :

1SELECT COUNT(*) FROM SCOTT.EMP;-- perform 5 Time , Let it be PGA in
2ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ERRORSTACK LEVEL 3';
3SELECT COUNT(*) FROM SCOTT.EMP;
4ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME ERRORSTACK OFF';
5SELECT VALUE FROM V$DIAG_INFO;