二、Execute to Parse相关参数描述
Google了一些关于这个问题的描述，大部分描述涉及到了以下2个参数
OPEN_CURSORS： specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.
It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors. The number will vary from one application to another. Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead 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%，说明当前够用