Oracle DBA应知应会 -- Session_cached_cursors和OPEN_CURSORS

SESSION_CACHED_CURSORS参数的引入是个偶然。Oracle FORMS的开发人员发现业务逻辑要求FORM之间经常需要来回切换，而一旦某个FORM切换到另外一个FORM，老FORMS上的所有的CURSOR必须全部关闭，下回再次切换回来的时候，又需要再次打开。这样就大大降低了FORM切换的效率。于是开发人员提出能否开发一种类似软关闭的方式，使CURSOR并没有真正关闭，从而提高FORM应用的效率。于是SESSION_CACHED_CURSORS参数就应运而生了，在UGA中建立一个独立的CURSOR缓冲池，将常用的CURSOR缓冲起来，下次执行的时候直接从缓冲池中取出CURSOR就可以执行，不需要再次PARSE。这个缓冲池和OPEN_CURSORS所控制的CURSOR缓冲池是不同的，前者最初的目的是为PL/SQL中的CURSOR服务，而SESSION_CACHED_CURSORS的缓冲在9.2.0.5之前是不能缓冲PL/SQL中的CURSOR的，9.2.0.5以后，才支持缓冲PL/SQL中的CURSOR。CURSOR被放到会话缓冲池中的前提是这个CURSOR被多次解析，当一个CURSOR上进行了3次解析后，这个CURSOR就被放入SESSION CACHE中了。
SESSION_CACHED_CURSORS参数在共享池优化工作中是十分常用的。这个参数的主要目的是将某个会话中的常用的SQL放入UGA中的SESSION CACHE里，以便于下子再调用时，不需要再做解析，从而减少共享池的争用。
如果说使用绑定变量的目的是减少硬解析，从而改善共享池，提高并发能力，那么SESSION_CACHED_CURSORS参数的目的是减少软解析，从而进一步提高共享池的性能。对于某些系统而言，不仅仅是硬解析需要优化，大量的并发执行，使软解析也可能成为系统的瓶颈。比如下面的AWR报告：
Load Profile

从报告中看出，平均每秒执行的数量为11725，每秒的解析数量为8958。在这种情况下，共享池的争用十分严重：
Instance Efficiency Percentages (Target 100%)

我们可以看到，虽然共享池的命中率很高，但是librar cache闩锁的争用十分严重。相关的等待接近总体的50%。在这种极端的情况下，通过加大SESSION_CACHED_CURSORS参数，可以有效的减少软解析的数量，从而提高缓解共享池相关闩锁的争用。
针对这个案例，我建议客户采取了两个措施，第一个是设置SESSION_CACHED_CURSORS为200，同时为了保障性能，建议他们采取了第二个措施，将DB CACHE加大了5－10G。第二个措施是我考虑到第一个参数调整后并发量会大幅度提升，并发量增加后DB CACHE的压力会有所增加，从而影响整体的优化效果。
事实证明加大SESSION_CACHED_CURSORS参数的效果十分明显，共享池闩锁争用明显降低，平均每秒事务数量从155提高到300左右。
为什么SESSION_CACHED_CURSORS会有这么大的效果呢？为了了解这一点，我们需要做一个实验。分析一下SESSION_CACHED_CURSORS对LIBRARY CACHE PIN和LIBRARY CACHE LOCK的影响。
为了分析LIBRARY CACHE PIN/LOCK，我们需要首先了解一下10049事件。在10.2版本开始，10049事件可以全面的监控LIBRARY CACHE的PIN/LOCK和INVALIDATION。10049事件的参数在9i和10g版本中有所不同。下面我们来做一个测试：

首先我们要使用EVENT 10049,需要找出sql的hashvalue，用hashvalue的低位作为LEVEL的高位，加上0X2030（TRACE PIN/LOCK）,产生LEVEL的值：
var id number;
exec :id:=1099;
select empno,ename from emp where empno=:id;
select hash_value from v$sqlarea where sql_text like 'select empno,ename%';

HASH_VALUE

2892642740
用计算器看看16进制：
AC6A39B4
这个十六进制数的低位是39B4，我们要TRACE PIN/LOCK因此TRACE LEVEL=39B42030,转换为10进制就是：968106032，下面首先我们将session_cached_cursors设置为0，关闭会话的CURSOR CACHE，看看会发生什么情况：
alter session set session_cached_cursors=0 ;
alter session set events '10049 trace name context forever,level 968106032';
设置对相关的LIBRARY CACHE的PIN和LOCK做TRACE。准备结束，通过多次执行下面的语句来检查LIBRARY CACHE PIN和LIBRARY CACHE LOCK的情况。
exec :id:=1010;
select empno,ename from emp where empno=:id;
exec :id:=1011;
select empno,ename from emp where empno=:id;
从TRACE看到，每次执行，TRACE文件中的PIN和LOCK就会增加：
KGLTRCLCK kglget hd = 0x1FBCB8A4 KGL Lock addr = 0x20F75530 mode = N
KGLTRCLCK kglget hd = 0x1FBCB7C0 KGL Lock addr = 0x20FA8970 mode = N
KGLTRCPIN kglpin hd = 0x1FBCB7C0 KGL Pin addr = 0x20F259D0 mode = S
KGLTRCPIN kglpndl hd = 0x1FBCB7C0 KGL Pin addr = 0x20F259D0 mode = S
KGLTRCLCK kgllkdl hd = 0x1FBCB7C0 KGL Lock addr = 0x20FA8970 mode = N
KGLTRCLCK kgllkdl hd = 0x1FBCB8A4 KGL Lock addr = 0x20F75530 mode = N
首先我们来看这里涉及2个地址，通过library_cache的trace看到：
BUCKET 14772:
LIBRARY OBJECT HANDLE: handle=1fbcb8a4 mutex=1FBCB958(1)
name=select empno,ename from emp where empno=:id
hash=cedcee1ccc9795f332f72482ac6a39b4 timestamp=12-24-2007 21:55:26
namespace=CRSR flags=RON/KGHP/TIM/PN0/SML/KST/DBN/MTX/[120100d0]
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch#=3 hpc=0000 hlc=0000
lwt=1FBCB900[1FBCB900,1FBCB900] ltm=1FBCB908[1FBCB908,1FBCB908]
pwt=1FBCB8E4[1FBCB8E4,1FBCB8E4] ptm=1FBCB8EC[1FBCB8EC,1FBCB8EC]
ref=1FBCB920[1FBCB920,1FBCB920] lnd=1FBCB92C[1FBCB6F0,23192730]
LIBRARY OBJECT: object=2052e5e4
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
CHILDREN: size=16
child# table reference handle

0 2052d080 2052cd34 1fbcb7c0
DATA BLOCKS:
data# heap pointer status pins change whr

0 23169fcc 2052e67c I/-/A/-/- 0 NONE 00
BUCKET 14772 total object count=1
上面的信息这就是这个SQL的PARENT CURSOR的HANDLE，而红色部分的1FBCB7C0是其子CURSOR HANDLE的地址：
LIBRARY OBJECT HANDLE: handle=1fbcb7c0 mutex=1FBCB874(0)
namespace=CRSR flags=RON/KGHP/PN0/[10010000]
kkkk-dddd-llll=0000-0000-0000 lock=0 pin=0 latch#=3 hpc=0000 hlc=0000
lwt=1FBCB81C[1FBCB81C,1FBCB81C] ltm=1FBCB824[1FBCB824,1FBCB824]
pwt=1FBCB800[1FBCB800,1FBCB800] ptm=1FBCB808[1FBCB808,1FBCB808]
ref=1FBCB83C[2052CD34,2052CD34] lnd=1FBCB848[1FBCB848,1FBCB848]
CHILD REFERENCES:
reference latch flags

2052cd34 0 CHL[02]
LIBRARY OBJECT last freed from HPD addn data CBK
从上面的TRACE上看到，执行SQL过程中，需要4个LOCK,2个PIN。那么如果是被CACHE的CURSOR会怎么样呢？
22:01:01 SQL> alter session set session_cached_cursors=20 ;
会话已更改。
22:14:35 SQL> select empno,ename from emp where empno=:id;
未选定行
22:14:39 SQL> select empno,ename from emp where empno=:id;
未选定行
22:14:42 SQL> select empno,ename from emp where empno=:id;
未选定行
22:14:56 SQL> select empno,ename from emp where empno=:id;
未选定行
22:16:09 SQL> select empno,ename from emp where empno=:id;
我们来看看TRACE，看看有什么发现 :
* 2007-12-24 22:16:21.722
KGLTRCPIN kglpin hd = 0x1FBBF998 KGL Pin addr = 0x21248078 mode = S
KGLTRCPIN kglpndl hd = 0x1FBBF998 KGL Pin addr = 0x21248078 mode = S
令人惊奇的是，LIBRARY CACHE LOCK不见了，对PAENT CURSOR的LOCK/PIN都消失了。这就是SESSION CACHED CURSORS优化分析过程的一个直观表现。那么为什么不需要对CHILD和PARENET加LIBRARY CACHE LOCK呢？我们先来看PARENT CURSOR HANDLE：
BUCKET 14772:
LIBRARY OBJECT HANDLE: handle=1fbcb8a4 mutex=1FBCB958(1)
name=select empno,ename from emp where empno=:id
hash=cedcee1ccc9795f332f72482ac6a39b4 timestamp=12-24-2007 22:14:39
namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/SML/KST/DBN/MTX/[120100d4]
kkkk-dddd-llll=0001-0001-0001 lock=N pin=0 latch#=3 hpc=0002 hlc=0002
lwt=1FBCB900[1FBCB900,1FBCB900] ltm=1FBCB908[1FBCB908,1FBCB908]
pwt=1FBCB8E4[1FBCB8E4,1FBCB8E4] ptm=1FBCB8EC[1FBCB8EC,1FBCB8EC]
ref=1FBCB920[1FBCB920,1FBCB920] lnd=1FBCB92C[2329CBD8,231B6A88]
DEPENDENCY REFERENCES:
reference latch flags

20553df0 0 [60]
LOCK OWNERS:
lock user session count mode flags

212383e8 2372d86c 2372d86c 1 N [00]
LIBRARY OBJECT: object=2059dea0
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
CHILDREN: size=16
child# table reference handle

0 205544a0 20554154 1fbbf998
DATA BLOCKS:
data# heap pointer status pins change whr

0 232307b8 2059df38 I/P/A/-/- 0 NONE 00
BUCKET 14772 total object count=1
可以看出，和普通的LIBRARY CACHE不同，lock不是0，而是lock=N,这说明在该对象上，已经有一个NULL的LOCK了，因此不需要加锁了。再看看子CURSOR:
LIBRARY OBJECT HANDLE: handle=1fbbf998 mutex=1FBBFA4C(0)
namespace=CRSR flags=RON/KGHP/PN0/[10010000]
kkkk-dddd-llll=0000-0041-0041 lock=N pin=0 latch#=3 hpc=0002 hlc=0002
lwt=1FBBF9F4[1FBBF9F4,1FBBF9F4] ltm=1FBBF9FC[1FBBF9FC,1FBBF9FC]
pwt=1FBBF9D8[1FBBF9D8,1FBBF9D8] ptm=1FBBF9E0[1FBBF9E0,1FBBF9E0]
ref=1FBBFA14[20554154,20554154] lnd=1FBBFA20[1FBBFA20,1FBBFA20]
CHILD REFERENCES:
reference latch flags

20554154 0 CHL[02]
LOCK OWNERS:
lock user session count mode flags

20f89814 2372d86c 2372d86c 1 N [00]
LIBRARY OBJECT: object=20553cb4
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
DEPENDENCIES: count=1 size=16
dependency# table reference handle position flags

0 2046173c 20461484 1fbcb668 24 DEP[01]
READ ONLY DEPENDENCIES: count=1 size=16
dependency# table reference handle flags

0 20554070 20553df0 1fbcb8a4 /ROD/KPP[60]
AUTHORIZATIONS: count=1 size=16 minimum entrysize=16
00000000 36000000 00020000 00000000
ACCESSES: count=1 size=16
dependency# types

0 0009
TRANSLATIONS: count=1 size=16
original final

1fbcb668 1fbcb668
DATA BLOCKS:
data# heap pointer status pins change whr

0 23217f84 20553e04 I/P/A/-/- 0 NONE 00
6 20461370 1fcf1d3c I/-/A/-/- 0 NONE 00
子CURSOR上同样又一个NULL的锁。由于对于CACHE的CURSOR，不需要再进行PIN操作，这样就减少了共享池的闩锁争用，有效的提高了SQL并发执行的效率。从这一点也可以很好的解释本节开始时那个优化案例的效果了。
SESSION_CACHED_CURSORS可以有效的减少软分析，那么什么情况下需要减少软分析呢，我们看看下面的查询：
SQL> col cursor_cache_hits format a20 truncate;
SQL> col soft_parses format a20 truncate;
SQL> col hard_parses format a20 truncate;
SQL> select
to_char(100 * sess / calls, '9999990.00') || '%' cursor_cache_hits,
to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,
to_char(100 * hard / calls, '999990.00') || '%' hard_parses
from
( select value calls from v$sysstat where name = 'parse count (total)' ),
( select value hard from v$sysstat where name = 'parse count (hard)' ),
( select value sess from v$sysstat where name = 'session cursor cache hits' );
2 3 4 5 6 7 8
CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES

85.34% 14.61% 0.06%
从上面的查询结果来看，CURSOR CACHE的命中率是85.34，软解析的比例是14.16%，硬解析的比例是0.06%。目前这个系统的session_cached_cursors参数设置为200。加大session_cached_cursors参数能否提高命中率，进一步减少软解析呢？我们可以用下面的查询继续分析：
Select 'session_cached_cursors' parameter, lpad(value, 5) value,
decode(value, 0, ' n/a', to_char(100 * 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'
) ;
PARAMETER VALUE USAGE

session_cached_curso 200 100%
从查询结果来看，CACHE的使用率是100%，如果有必要的话，加大session_cached_cursors参数，还可以进一步减少软解析。