SQL> show parameter open_cursors --each session(How many sessions can be opened at most at the same time cursor(Cursor)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 300
SQL> show parameter session_cached_cursor --each session(Session) Up to how many can be cached off cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 20
<p>SQL> select count(*) from v$open_cursor; --Open at some point in the current instance cursor Number COUNT(*)
----------
1081. The role of open_cursors and session_cached_cursor?
open_cursors sets the maximum number of cursors per session (session) that can be opened at the same time. session_cached_cursor sets the maximum number of closed cursors per session (session). To understand their role, we need to figure out how Oracle executes each sql statement. 
After looking at the picture above, we understand two things:
a. There is no relationship between the two parameters and there will be no influence on each other. b. The two parameters have the same function: to keep the same sql statements from opening the cursor, thus avoiding the soft parsing process to provide the efficiency of the application.
2. How to set the parameters correctly and reasonably?
a. If the Open_cursors settings are too small, the system performance will not be significantly improved, and the error of ORA-O1000:m~imum open CUrsOrs exceeded may also be triggered. If the settings are too large, the system memory is consumed for no reason. We can see if your settings are reasonable through the following sql statements:
SQL> SELECT MAX(A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR
2 FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P
3 WHERE A.STATISTIC# = B.STATISTIC#
4 AND B.NAME = 'opened cursors current'
5 AND P.NAME = 'open_cursors'
6 GROUP BY P.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 setting value of the parameter Open_cursors. If the two are too close, or even trigger eRA 101000 error, then you should increase the setting value of the parameter Open_cursors. If the problem remains unsolved, it's not right to blindly increase Open_cursors. At this point, you have to check whether the application code is reasonable, such as whether the application has opened the cursor, but has not closed in time after it has finished its work. The following statements can help you identify the session that caused the cursor to leak out:
SELECT A.VALUE, S.USERNAME, S.SID, S.SERIAL#
FROM V$SESSTAT A, V$STATNAME B, V$SESSION S
WHERE A.STATISTIC# = B.STATISTIC#
AND S.SID = A.SID
AND B.NAME = 'opened cursors curent'; b. Similarly, the session_cached_cursors value is not the bigger the better, we can get reasonable settings through the following two statements.
SQL> SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%cursor%';
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 15095
opened cursors current 34
session cursor cache hits 12308
session cursor cache count 775
cursor authentications 324
SQL> SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse time cpu 332
parse time elapsed 1190
parse count (total) 9184
parse count (hard) 1031
parse count (failures) 3 session cursor cache hits is the number of times the system finds the corresponding cursors in the cache, and parse count(total) is the total number of parses. The higher the ratio of the two, the better the performance. If the ratio is low and there is more memory left, we can consider increasing this parameter.
c. Use the following sql to determine the usage of session_cached_cursors. If the utilization rate is 100%, increase this parameter value.
SQL> SELECT 'session_cached_cursors' PARAMETER,
2 LPAD(VALUE, 5) VALUE,
3 DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * 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 (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors')
9 UNION ALL
10 SELECT 'open_cursors',
11 LPAD(VALUE, 5),
12 TO_CHAR(100 * USED / VALUE, '990') || '%'
13 FROM (SELECT MAX(SUM(S.VALUE)) USED
14 FROM V$STATNAME N, V$SESSTAT S
15 WHERE N.NAME IN
16 ('opened cursors current', 'session cursor cache count')
17 AND S.STATISTIC# = N.STATISTIC#
18 GROUP BY S.SID),
19 (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors');
PARAMETER VALUE USAGE
---------------------- ---------- -----
session_cached_cursors 20 100%
open_cursors 300 16% When we execute an sql statement, we will generate a library cache object in shared pool, cursor is a library cache object for sql statement. In addition, we will have a copy of cursor in pga and a statement handle in client. These are called cursor. In v$open_cursor, we can see the currently open cursor and PG. cached cursor.
session_cached_cursor
This parameter limits the length of session cursor cache list in pga. 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, then the cursor will be added to the MRU side of session cursor cache list. When a session intends to parse an sql, it first searches for curs sessions in pga. Or cache list, if found, will detach the cursor from the list, and then add the cursor to the MRU when closed. session_cached_cursor provides fast soft analysis capabilities, providing better performance than soft parse.
Original address: http://blog.csdn.net/zq9017197/article/details/7345352