Shulou(Shulou.com)06/01 Report--

SQL > show parameter open_cursors-how many cursor (cursor) NAME TYPE VALUE can be opened at the same time per session (session) -open_cursors integer 300SQL > show parameter session_cached_cursor-- the maximum number of closed cursor NAME TYPE VALUE that can be cached per session (session) -session_cached_cursors integer 20SQL > select count (*) from v$open_cursor -- refers to the number of cursor opened 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 simultaneously per session (session). Session_cached_cursor sets the maximum number of closed cursor that can be cached per session. To figure out what they do, 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 not be any influence on each other.

B, the two parameters have the same effect: to avoid the soft parsing process to provide the efficiency of the application by keeping subsequent identical sql statements from opening cursors.

2. How to set the size of the parameters correctly and reasonably?

A. If the Open_cursors setting is too small, there will be no significant improvement in system performance, and errors in ORA-O1000:m~imum open CUrsOrs exceeded. May be triggered.

If the setting is too large, the system memory is consumed for no reason. We can check whether your settings are reasonable through the following sql statement:

SQL > SELECT MAX (A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P WHERE A.STATISTIC# = B.STATISTIC# AND B.NAME = 'opened cursors current' AND P.NAME =' open_cursors' GROUP BY P.Vale; HIGHEST_OPEN_CUR MAX_OPEN_CUR-28 300

HIGHEST_ OPEN CUR is the maximum value of the actual open cursors, and MAX_OPEN_ CUR is the setting value of the parameter Open_cursors. If the two are too close, or even trigger the ERA-01000 error, then you should increase the setting value of the parameter Open_cursors. If the problem remains unsolved, it is also wrong to blindly increase the Open_cursors. At this time, you have to check whether the application's code is reasonable, such as whether the application opened the cursor but did not close it in time after it finished its work. The following statements can help you determine 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.STATISTIC# = 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, and we can get a reasonable setting from the following two statements.

SQL > SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE'% cursor%' NAME VALUE-opened cursors cumulative 15095opened cursors current 34session cursor cache hits 12308session cursor cache count 775 cursor authentications 324SQL > SELECT NAME VALUE FROM V$SYSSTAT WHERE NAME LIKE'% parse%' NAME VALUE-parse time cpu 332parse time elapsed 1190parse count (total) 9184parse count (hard) 1031parse 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, the better the performance. If the proportion is low and there is more memory left, you can consider increasing this parameter.

C. Use the following sql to judge the use of 'session_cached_cursors'. If the usage is 100%, increase this parameter value.

SQL > SELECT 'session_cached_cursors' PARAMETER, LPAD (VALUE, 5) VALUE, DECODE (VALUE, 0,' n * USED / VALUE, '990') | |'%') USAGE FROM (SELECT MAX (S.VALUE) USED FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME = 'session cursor cache count' AND S.STATISTIC# = N.STATISTIC#) (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors') UNION ALL SELECT' open_cursors', LPAD (VALUE, 5), TO_CHAR (100 * USED / VALUE, '990') | |'% 'FROM (SELECT MAX (SUM (S.VALUE)) USED FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME IN (' opened cursors current', 'session cursor cache count') AND S.STATISTIC# = N.STATISTIC# GROUP BY S.SID) (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors') PARAMETER VALUE USAGE-session_cached_cursors 20 100%open_cursors 300 16%

When we execute a sql statement, we will generate a library cache object,cursor in shared pool that is a library cache object for the sql statement. In addition, we will have a copy of cursor in pga and a statement handle on the client side, which 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 session cursor cache list in pga. Session cursor cache list is a two-way lru linked list. When a session intends to close a cursor, if the parse count of the cursor is more than 3 times, then the cursor will be added to the MRU side of the session cursor cache list. When a session plans to parse a sql, it will first search the session cursor cache list in the pga. If it finds it, it will detach the cursor from the list, and then add the cursor to the MRU when it is closed. Session_cached_cursor provides fast soft analysis and higher performance than soft parse.

Update 2:

OPEN_CURSORS is a very interesting parameter, and DBA often finds that the OPEN CURSORS in his system is very large. Let's take a look at an example: SQL > select sid,value from v$sesstat a minute vandalism statname b where a.statistic#=b.statistic# and name='opened cursors current' order by 2 SID VALUE-- 5430 93 3527 95 4055 96 4090 97 2012 98 1819 98 5349 1684 1741 1741 116 4308 1970 170 1369 181 4208 184 887 2145215 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 the largest session that opens CURSOR in the session reaches 2620. In the eyes of most people, CURSOR is closed after use, and the number of OPENED CURSORS should not be too large. Is there a CURSOR leak in the application, and some applications do not close when they use CURSOR? In fact, we have always misunderstood the concept of OPEN CURSOR. It is considered that only the CURSOR being FETCH is in the OPEN state, and once the FETCH is finished, after CLOSE CURSOR, the CURSOR is in the closed state. Therefore, the number of CURSOR for OPEN state in a session should be very small. In fact, this is not the case, some CURSOR is already CLOSE in the program, but oracle will buffer it in order to improve the performance of CURSOR. The closure of these buffered CURSOR in the program is just a soft close. In fact, it is not closed in the session, but is placed in a CURSOR buffer.

Before Oracle 9.2.0.5, the function of the OPEN_CURSORS parameter was twofold, on the one hand, to limit the total number of CURSORS opened in a session. On the other hand, the OPEN_CURSORS parameter also serves 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, then you can keep the OPEN state first. If the number of CURSOR currently open has reached the limit of the OPEN_CURSORS parameter, a buffered CURSOR that is not actually open at that time will be closed first. If all the CURSOR in the buffer pool is actually open, then ORA-1000, "maximum open cursors exceeded" will be reported.

After Oracle 9.2.0.5, the OPEN_CURSORS parameter is no longer responsible for PL/SQL buffering, and SQL in PL/SQL can also use SESSION_CACHED_CURSORS session buffering. This parameter becomes a pure limitation.

Even so, the OPEN_CURSORS parameter is still closely related to CURSOR's buffering mechanism, because it limits the maximum value of the current session opening CURSOR. Setting a larger OPEN_CURSORS parameter can not only avoid ORA-1000, but also make the session buffer more CURSOR and improve the performance of SQL parsing. However, the setting of this parameter takes up more PGA space and consumes a certain amount of physical memory. Therefore, this parameter is not set as large as possible. In a general OLTP system, 1000 / 3000 is enough. In systems with shared server mode, this parameter is set slightly more conservatively, because the larger the parameter, the more SGA space it takes up.

It is also important to note that this parameter has been dynamic since Oracle 9.0 and can be adjusted dynamically at any time.

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.