SQL> show parameter open_cursors NAME TYPE VALUE open_cursors integer 300 SQL> show parameter session_cached_cursor NAME TYPE VALUE session_cached_cursors integer 20 <p>SQL> select count (*) from v$open_cursor; 108</p>
1、open_cursors与session_cached_cursor的作用?
open_cursors设定每个session(会话)最多能同时打开多少个cursor(游标)。session_cached_cursor设定每个session(会话)最多可以缓存多少个关闭掉的cursor。想要弄清楚他们的作用,我们得先弄清楚Oracle 如何执行每个sql语句。
看完上图后我们明白了两件事:
a、两个参数之间没有任何关系,相互也不会有任何影响。b、两个参数有着相同的作用:让后续相同的sql语句不在打开游标,从而避免软解析过程来提供应用程序的效率。
2、如何正确合理设置参数的大小? a、如果Open_cursors设置太小,对系统性能不会有明显改善,还可能触发ORA-O1000:m~imum open CUrsOrs exceeded.的错误。如果设置太大,则无端消耗系统内存。我们可以通过如下的sql语句查看你的设置是否合理:
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是实际打开的cursors 的最大值,MAX_OPEN_ CUR是参数Open_cursors的设定值,如果二者太接近,甚至触发eRA一01000错误,那么你就应该调大参数Open_cursors的设定值。如果问题依旧没有解决,盲目增大Open_cursors也是不对的,这个时候你得检查应用程序的代码是否合理,比如说应用程序是否打开了游标,却没有在它完成工作后没有及时关闭。以下语句可以帮助你确定导致游标漏出的会话:
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、同样,session_cached_cursors的值也不是越大越好,我们可以通过下面两条语句得出合理的设置。
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就是系统在高速缓存区中找到相应cursors的次数,parse count(total)就是总的解析次数,二者比值越高,性能越好。如果比例比较低,并且有较多剩余内存的话,可以考虑加大该参数。
c、使用下面的sql判断'session_cached_cursors' 的使用情况。如果使用率为100%则增大这个参数值。
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%
当我们执行一条sql语句的时候,我们将会在shared pool产生一个library cache object,cursor就是其中针对于sql语句的一种library cache object.另外我们会在pga有一个cursor的拷贝,同时在客户端会有一个statement handle,这些都被称为cursor,在v$open_cursor里面我们可以看到当前打开的cursor和pga内cached cursor.
session_cached_cursor 这个参数限制了在pga内session cursor cache list的长度,session cursor cache list是一条双向的lru链表,当一个session打算关闭一个cursor时,如果这个cursor的parse count超过3次,那么这个cursor将会被加到session cursor cache list的MRU端.当一个session打算parse一个sql时,它会先去pga内搜索session cursor cache list,如果找到那么会把这个cursor脱离list,然后当关闭的时候再把这个cursor加到MRU端.session_cached_cursor提供了快速软分析的功能,提供了比soft parse更高的性能.