to setting this value higher than actually needed.
SESSION_CACHED_CURSORS: specifies the number of session cursors to cache. Repeated parse calls of the same SQL (including recursive SQL) or PL/SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.
If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache, thus avoiding even a soft parse. (Technically, a parse can’t be completely avoided; a “softer” soft parse is done that’s faster and requires less CPU.)
In the session cursor cache, Oracle manages the cached cursors using a LRU list. Once more than SESSION_CACHED_CURSORS closed cursors are cached, Oracle starts dropping cached cursors off the LRU end of the list whenever it needs to make room to cache a new cursor.
当我们执行一条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更高的性能，也就是说连open cursor的动作都给省了。
三、分析及调整
查看当前系统session配置 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 50 98% --当前session_cached_cursors的使用率为98%，应考虑增加该参数值 open_cursors 300 20% --当前open_cursors仅为20%，说明当前够用 -- 也可以通过下面的脚步查看cursor的使用情况 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 ---------------- --------------------------------------------- 3