Oracle database Parameters Open_cursors and session_cached_cursor detailed!

Open_cursors

The maximum number of cursor (cursors) that can be opened at the same time per session

Session_cached_cursor

The maximum number of closed cursor that can be cached per session

Sql> Show Parameter Open_cursors           --The maximum number of cursor (cursors) that each session can open at the same time    NAME                                 TYPE        VALUE  ----- ------------------------------------------------------------------------  open_cursors                         integer     300  sql> Show Parameter Session_cached_cursor  --The maximum number of closed cursor    NAME TYPE can be cached per session        VALUE  -----------------------------------------------------------------------------  session_ Cached_cursors               integer  sql> Select COUNT (*) from V$open_cursor;  --refers to the number of open cursor at some point in the current instance  COUNT (*)  ----------         108
1. What is the role of Open_cursors and session_cached_cursor?
Open_cursors sets the maximum number of cursor (cursors) that can be opened at a time per session. Session_cached_cursor sets the maximum number of closed cursor that can be cached per session. We need to see how Oracle executes each SQL statement.

Through analysis, we can draw

There is no relationship between a, two parameters, and there is no effect on each other.

b, two parameters have the same effect: let the subsequent same SQL statement not open the cursor, thus avoiding the soft parsing process to provide application efficiency.

2, how to correctly set the size of the parameters?
A, if the open_cursors settings are too small, there will be no noticeable improvement in system performance, and may trigger ora-o1000:m~imum open CUrsOrs exceeded. The error. If the setting is too large, system memory is consumed for no reason. We can see if your settings are justified by the following SQL statement:

Sql> SELECT MAX (a.value) highest_open_cur, C.value max_open_cur        2 from    v$sesstat A, V$statname B, V$parameter C        3   WHERE a.statistic# = b.statistic#        4 and     b.name = ' opened Cursors current '        5 and     c.name = ' Ope N_cursors '        6   GROUP by C.value;            Highest_open_cur max_open_cur      ------------------------------------                    28 300  
Highest_ Open cur is the maximum value of the actual open cursors, Max_open_ cur is the set value of the parameter open_cursors, if the two are too close, or even trigger era 101,000 error, then you should adjust the parameters Open_ The set value of the cursors. If it doesn't solve the problem, it's not right to blindly increase the open_cursors, so you have to check the application's code is reasonable, such as whether the application opened the cursor, but did not close in time after it completed the work. The following statement can help you identify the session that caused the cursor to leak:

 SELECT A.value, S.username, S.sid, s.serial# from V$sesstat A, V$statname B, v$session S WHERE a.stat istic# = b.statistic# and S.sid = a.sid and B.name = ' opened Cursors curent '; 
 Similarly, the value of session_cached_cursors is not as large as possible, we can use the following two statements to derive reasonable settings. 

Sql> SELECT NAME, VALUE from V$sysstat WHERE NAME is like '%cursor% '; NAME VALUE----------------------------------------- ---------------------------------opened cursors Cumulative 15095 o                                             Pened cursors current session cursor Cache Hits 12308 Session Cursor Cache count 775 cu Rsor authentications 324 sql> SELECT NAME, VALUE from V$sys            STAT WHERE NAME like '%parse% '; NAME VALUE----------------------------------------- ---------------------------------Parse Time CPU 332 p     Arse Time Elapsed                                                1190 Parse count (total)                                                    9184 Parse count (hard) 1031 parse count (failures)   3
Session cursor Cache Hits is the number of times that the system finds the corresponding cursors in the cache area, and parse count (total) is the overall number of resolutions, the higher the ratio, the better the performance. If the ratio is low and there is more memory left, you can consider increasing the parameter.
C, use the following SQL to determine the usage of ' session_cached_cursors '. Increase this parameter value if the usage rate is 100%.

sql> SELECT ' session_cached_cursors ' PARAMETER, 2 lpad (value, 5) VALUE, 3 DECODE (value, 0 , ' N/A ', to_char (+ * used/value, ' 990 ') | |           '% ') USAGE 4 from (SELECT MAX (s.value) used 5 from V$statname N, V$sesstat S 6 WHERE N.name = ' Session cursor cache count ' 7 and s.statistic# = n.statistic#), 8 (SEL       ECT VALUE from v$parameter WHERE NAME = ' session_cached_cursors ') 9 UNION all ten SELECT ' open_cursors ', Lpad (VALUE, 5), To_char (Used/value, ' 990 ') | |           '% ' from (SELECT MAX (SUM (s.value)) used from V$statname N, V$sesstat S 15  WHERE N.name in + (' opened cursors current ', ' session cursor cache count ') s.statistic# = n.statistic# GROUP by S.sid), (SELECT VALUE from V$parameter WHERE NAM E = ' Open_curSors ');       PARAMETER VALUE USAGE-------------------------------------session_cached_cursors 20   100% open_cursors 300 16%

When we execute an SQL statement, we will produce a library cache object,cursor in the shared pool, which is a library cache for SQL statements Object. In addition we will have a copy of the cursor in the PGA, and at the same time the client will have a statement handle, which are called the cursor, in V$open_ Inside the cursor we can see the currently open cursor and the cached cursor inside the PGA.

Session_cached_cursor
This parameter limits the length of the cursor cache list in the PGA session, the cursor cache list is a two-way, LRU list, when a session is intended to close a cursor, If the cursor has a parse count of more than 3 times, the cursor will be added to the MRU side of the session cursor cache list. When a session is going to parse a SQL, it will first search the PGA for the session The cursor cache list, if found, then puts the cursor out of the list and then adds the cursor to the MRU end when it is closed. Session_cached_cursor provides fast, soft-analysis capabilities that provide a greater than soft Parse for higher performance.

Open_cursors is a very interesting parameter, and often a DBA discovers that the open cursors in his system is very large. Let's look at an example:

 sql>select sid,value from V$sesstat a,v$statname b where a.statistic#=b.statistic# and Name= ' opened cursors Curre       NT ' ORDER by 2;      SID VALUE--------------------5430 93 3527 95 4055 96 4090 97 2012 98 1819 98 5349 102 1684 103 1741 116 4308 16        9 1970 170 1369 181 4208 184 887 214 5215 214 3518        214 868 214 1770 215 4050 215 1809 231 3010 235 762 237 731 471 4013 1066 2648 1152 2255 1172 2322 2620 

We see that the open_cursors parameter of this system is set to 3000, while in the session the largest session of the cursor opens at 2620. In the eyes of the average person, the cursor is closed after use, the number of opened cursors should not be too much, the application has a cursor leak, some applications use the cursor is not closed? In fact, we have always misunderstood the concept of open cursor. It is considered that only the cursor that is being fetch is open, and once the fetch is finished, the cursor is closed when the cursor is close. Therefore, the number of cursor in the open state in a session should be small. In fact, some of the cursor in the program is already close, but Oracle to improve the performance of the cursor will be buffered, these buffered cursor, in the program is closed just a soft shutdown, in fact, in the session is not closed, Instead, it is placed in a cursor buffer.

Prior to Oracle 9.2.0.5, the open_cursors parameter was dual, on the one hand limiting the total number of cursors opened by a session. On the other hand, the open_cursors parameter also acts as a buffer for PL/SQL cursor. In PL/SQL, if a cursor is closed, the cursor is not hard-closed immediately, but is first saved in the cursor buffer. If the number of cursor currently open in this session has not reached the value of the Open_cursors parameter, you can leave the open state first. If the number of cursor that is currently open has reached the limit of the open_cursors parameter, then first a buffered cursor that is not actually opened is closed. If all the cursor in the buffer pool is actually open, it will be reported ORA-1000, "Maximum open cursors exceeded".

After Oracle 9.2.0.5, the open_cursors parameter no longer assumes the work of PL/SQL buffering, which can also be used with session_cached_cursors session buffering. This parameter becomes a purely limiting one.

Nonetheless, the Open_cursors parameter is closely related to the cursor's buffering mechanism, because this parameter limits the maximum value of the cursor that is currently open for a session. Setting a larger open_cursors parameter avoids ORA-1000, and allows the session to buffer more cursor, improving the performance of SQL parsing. However, the larger the parameter setting will take up a larger PGA space and consume a certain amount of physical memory. Therefore, this parameter is not set to the larger the better, the general OLTP system, 1000-3000 is enough. In a system with Shared server mode, this parameter is set to a slightly more conservative setting, because the larger the parameter, the larger the amount of SGA space is occupied.

It is also important to note that starting with Oracle 9.0, this parameter is already dynamic and can be dynamically adjusted at any time.

Oracle database Parameters Open_cursors and session_cached_cursor detailed!