Oracle Parameters Open_cursors and session_cached_cursor detailed!

[SQL]View Plaincopyprint?

2. Sql> Show Parameter Open_cursors -the maximum number of cursor (cursors) that can be opened at the same time per session
6. NAME TYPE VALUE
8. ------------------------------------ ----------- ------------------------------
10. Open_cursors integer
12. Sql> Show Parameter Session_cached_cursor --How many closed cursor can be cached per session
16. NAME TYPE VALUE
18. ------------------------------------ ----------- ------------------------------
20. Session_cached_cursors integer
22. <p>SQL> Select Count (*) from v$open_cursor; --refers to the number of open cursor (s) </p><p> COUNT (*) at some point in the current instance
24. ----------
26. 108</p>

1, the role of Open_cursors and session_cached_cursor?

Open_cursors sets the maximum number of cursor (cursors) that can be opened at the same time per session. Session_cached_cursor sets the maximum number of closed cursor that can be cached per session. To figure out what their role is, we need to figure out how Oracle executes each SQL statement.

After reading, we learned two things:

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]View Plaincopyprint?

2. sql> SELECT MAX (a.value) as Highest_open_cur, p.value as max_open_cur
4. 2 from v$sesstat A, V$statname B, V$parameter P
6. 3 WHERE a.statistic# = b.statistic#
8. 4 and B.NAME = ' opened cursors current '
10. 5 and P.NAME = ' open_cursors '
12. 6 GROUP by P.value;
16. Highest_open_cur Max_open_cur
18. ---------------- --------------------
20. 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 the problem is still unresolved, blindly increasing the open_cursors is not right, this time you have to check the application's code is reasonable, such as whether the application opened the cursor, but did not complete the work after it did not shut down in time. The following statement can help you identify the session that caused the cursor to leak:

[SQL]View Plaincopyprint?

1. SELECT A.value, S.username, S.sid, s.serial#
2. From v$sesstat A, V$statname B, v$session S
3. WHERE a.statistic# = b.statistic#
4. and S.sid = A.sid
5. and B.NAME = ' opened Cursors curent ';

b, similarly, the value of the session_cached_cursors is not the bigger the better, we can use the following two statements to derive reasonable settings.

[SQL]View Plaincopyprint?

2. sql> SELECT NAME, VALUE from v$sysstat WHERE NAME is like '%cursor% ';
6. NAME VALUE
8. ---------------------------------------------------------------- ----------
10. Opened cursors Cumulative 15095
12. Opened Cursors current
14. Session cursor Cache hits 12308
16. Session Cursor Cache count 775
18. Cursor Authentications 324
22. sql> SELECT NAME, VALUE from v$sysstat WHERE NAME is like '%parse% ';
26. NAME VALUE
28. ---------------------------------------------------------------- ----------
30. Parse Time CPU 332
32. Parse Time elapsed 1190
34. Parse count (total) 9184
36. Parse count (hard) 1031
38. 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]View Plaincopyprint?

2. sql> SELECT ' session_cached_cursors ' PARAMETER,
4. 2 Lpad (value, 5) VALUE,
6. 3 DECODE (VALUE, 0, ' n/A ', To_char (Used/value, ' 990 ') | | '% ') USAGE
8. 4 from (SELECT MAX (s.value) used
10. 5 from v$statname N, V$sesstat S
12. 6 WHERE N.NAME = ' session cursor cache count '
14. 7 and s.statistic# = n.statistic#),
16. 8 (SELECT VALUE from v$parameter WHERE NAME = ' session_cached_cursors ') /c4>
18. 9 UNION All
20. Ten SELECT ' open_cursors ',
22. Lpad (VALUE, 5),
24. To_char (+ * used/value, ' 990 ') | | '% '
26. From (SELECT MAX (SUM (s.value)) used
28. From v$statname N, V$sesstat S
30. The WHERE N.NAME in
32. (' opened Cursors current ', ' session cursor cache count ')
34. s.statistic# = n.statistic#
36. GROUP by S.sid),
38. (SELECT VALUE from v$parameter WHERE NAME = ' open_cursors ');
42. PARAMETER VALUE USAGE
44. ---------------------- ---------- -----
46. Session_cached_cursors 20 100%
48. 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.

http://blog.csdn.net/zq9017197/article/details/7345352

Update 2:

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 current ' or Der by 2;       SID      VALUE--------------------      5430      3527      4055      4090      2012      1819         98      5349        102 1684 103 1741        98 4308 169      1970      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.

Http://www.cnblogs.com/sumsen/archive/2012/07/19/2599206.html

Oracle Parameters Open_cursors and session_cached_cursor detailed!