SQL 调优解析之open cursor reuse 和 session cached cursor

hunter

提交的SQL语句执行时，首先判断是否存在打开的游标，如果为否则检查session cached cursor是否缓存这提交的SQL语句，通过
http://t.askmaclean.com/thread-2817-1-1.html
提到了这个问题，最终结果总结为下面的这段话:
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.)

读这段话意思是如果设置了session_cached_cursor参数，则在收到SQL语句的解析请求时，优化器在library cache中检查该语句解析请求次数是否大于3次，如果大于3次，则将游标置入session cached cursor队列，后续的解析会走session cached cursor从而避免了软解析。

找了一下关于open cursor和session cached cursor的资料，但是能找到的不多，请问一下，Open cursor reuse是怎么检查的，它的内部原理是怎样的，谢谢！

Maclean Liu(刘相兵

SQL> select view_definition
  2    from v$fixed_view_definition
  3   where view_name = 'GV$OPEN_CURSOR';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select inst_id,
       kgllkuse,
       kgllksnm,
       user_name,
       kglhdpar,
       kglnahsh,
       kgllksqlid,
       kglnaobj,
       kgllkest,
       decode(kgllkexc, 0, to_number(NULL), kgllkexc),
       kgllkctp                                              -- CURSOR_TYPE
  from x$kgllk
where kglhdnsp = 0
   and kglhdpar != kgllkhdl

KGLHDPAR!=KGLLKHDL 该条件排除了父游标，所以V$OPEN_CURSOR列出的均是child cursor
KGLHDNSP ==> NAME SPACE =0

SQL> select distinct kgllkctp from X$kgllk;

KGLLKCTP
----------------------------------------------------------------
SESSION CURSOR CACHED
PL/SQL CURSOR CACHED
OPEN
OPEN-RECURSIVE
DICTIONARY LOOKUP CURSOR CACHED
BUNDLE DICTIONARY LOOKUP CACHED



OPEN CURSOR本质上还是一种  null  breakable library cache lock ， breakable lcl的目的就是为了快速定位一个游标，