近日有朋友问到 cursor 如何确定是否sharable的，Metalink Note 296377.1 说得比较好，简单注释如下：
1. shared SQL,parent cursor,child cursor 概念：
所有SQL都是Oracle暗式共享的(implicitly sharable)。当用户A发出一条SQL后，Oracle会根据SQL文本内容生成hash value(10g还有唯一的SQL_ID)，以便能够快速找到 Shared pool已经存在的相同SQL。如果找不到，则Oracle会为这个SQL创建一个parent cursor和一个child cursor，这与SQL是否共享是没有关系的。
parent cursor包含了SQL TEXT和相关的hash value，v$sqlarea中的每一行代表了一个parent cursor，根据address表示了其内存地址。
child
cursor包含了SQL的metadata，即使得这个SQL可以执行的所有相关信息，如OBJECT和权限，优化器设置，执行计划等。v$sql中中
的每一行表示了一个child cursor，根据hash value和address与parent cursor 关联。child
cursor有自己的address，即V$SQL.CHILD_ADDRESS。
第一个child cursor总是使用0来表示其创建顺序，V$SQL.CHILD_NUMBER = 0。因此，当从V$SQL_PLAN中查找某个SQL的执行计划时，要注意你写对了CHILD_NUMBER 。
如果有多个child cursor，则表示parent cursor有多个版本，v$sqlarea中的version_count字段就会纪录下来。
2. 如何确定SQL 是否可以共享：
假设用户A执行完一条SQL后，退出然后重新登陆，发出同一条SQL，则根据hash value找到Shared pool中已经存在的parent
cursor，然后把 此parent cursor下的child cursor
list搜寻一边，判断metadata是否完全相同，如果是，则这条sql可以共享，就完成了一次soft parse。
假设用户B之后发出一条SQL文本完全一样的SQL，但访问的table不是A用户的，而是B用户自己的，则metadata出现AUTH_CHECK_MISMATCH 和
TRANSLATION_MISMATCH ，无法共享child cursor。Oracle会因此在此parent cursor 下创建一个新的child cursor，也就是一个hard parse。
因此，SQL 是否可以共享是与parent cursor无关的，而是由child cursor决定的。
从v$sql_shared_cursor可以获得详细的无法共享的原因：
select * from v$sql_shared_cursor where kglhdpar = <parent address> –or sql_id = ”
select * from v$sql_shared_cursor where address = <parent address> –or sql_id = ”
一般常见的mismatch是：
OPTIMIZER_MISMATCH : 优化器环境设置不同，一般是optimizer相关参数
BIND_MISMATCH : 绑定变量的值的长度在第二次执行的时候发生显著的变化 AUTH_CHECK_MISMATCH : 授权关系不匹配
TRANSLATION_MISMATCH: 事务环境不匹配
其实最常见的是 BIND_MISMATCH ，在10g中可以测试一下：
create table t1(col1 varchar2(4000));
declare
v_col1 varchar2(4000);
begin
v_col1 := ‘t’;
for i in 1..30 loop
v_col1 := v_col1 ||’t';
insert into t1 values(v_col1);
end loop;
end;
/
–可以看出，变量长度在30以下的时候，还是只有一个child cursor:
SQL_TEXT EXECUTIONS CHILD_NUMBER ADDRESS HASH_VALUE
————————— ———- ———— ——– ———-
INSERT INTO T1 VALUES(:B1 ) 30 0 9E355F10 2351142747
declare
v_col1 varchar2(4000);
begin
v_col1 := ‘tttttttttttttttttttttttttttttt’;
for i in 31..4000 loop
v_col1 := v_col1 ||’t';
insert into t1 values(v_col1);
end loop;
end;
/
–可以看出，变量长度变化导致了四个child cursor 存在：
SQL_TEXT EXECUTIONS CHILD_NUMBER CHILD_ADDRESS ADDRESS HASH_VALUE
—————————- ———- ———— ————- ——– ———-
INSERT INTO T1 VALUES(:B1 ) 32 0 9E355DCC 9E355F10 2351142747
INSERT INTO T1 VALUES(:B1 ) 96 1 9E34BA18 9E355F10 2351142747
INSERT INTO T1 VALUES(:B1 ) 1872 2 9E34B8D4 9E355F10 2351142747
INSERT INTO T1 VALUES(:B1 ) 2060 3 9E34B790 9E355F10 2351142747
3. 使用cursor trace获得child cursor 无法共享的详细原因：
–9i
alter session set events ’10270 trace name context forever, level 10′;
–10g
alter system set events ‘immediate trace name cursortrace level 577, address <hash_value>’;
–(level 578/580 can be used for high level tracing (577=level 1, 578=level 2, 580=level 3)
alter system set events ‘immediate trace name cursortrace level 2147483648, address 1′;
4. cursor_sharing=similar 和 force 时的行为
cursor_sharing=similar 时
如果 predicate 为范围( >,<,>=,<= )，则出现literal replacement 时，一般不会share cursor，除非literal 完全相等；
因为在CBO下，这个时候的literal被用来确定执行计划，literal replacement会被认 为是UNSAFE的，无法共享。
在RBO时候，CURSOR是会共享的。
CBO下，如果 predicate 为=，则出现literal replacement 时，是否share cursor，取决于这个column是否有histgrams。
cursor_sharing=force 时，不管 predicate，如果出现literal replacement ，则会shared cursor，但往往容易得到性能较差的执行计划。
可以使用10046 判断一个bind variable 是否 unsafe：
The flag oacfl2 in 9i and fl2 in 10g will show if a variable is unsafe.
BINDS #2:
bind 0: dty=2 mxl=22(04) mal=00 scl=00 pre=00 acflg=10 oacfl2=500
size=24 ffset=0 bfp=1036d6408 bln=22 avl=04 flg=09 value=16064
bind 1: dty=2 mxl=22(04) mal=00 scl=00 pre=00 acflg=10 oacfl2=500 size=24 ffset=0 bfp=1036d4340 bln=22 avl=04 flg=09
If you note oacfl2=500
#define UACFBLTR 0×00000100 /* Bind was generated by LiTeRal replacement */
#define UACFUNSL 0×00000200 /* UNSafe Literal */
#define UACFNDTL 0×00000400 /* Non-DaTa LiteRal */
在11g中，child cursor 共享这部分改动不小，如果从10g升级上去，AWR往往发现某些SQL的VERSION COUNT会变多，可能会命中BUG。
11g中，视图v$sql 增加了is_bind_sensitive is_bind_aware is_shareable列：is_bind_sensitive： indicates not only whether bind variable peeking was used to generatethe execution plan but also whether the execution plan depends on the peeked value. Ifthis is the case, the column is set to Y; otherwise, it’s set to N.is_bind_aware： indicates whether the cursor is using extended cursor sharing. If yes, thecolumn is set to Y; if not, it’s set to N. If set to N, the cursor is obsolete, and it will no longerbe used.is_shareable： indicates whether the cursor can be shared. If it can, the column is set to Y;otherwise, it’s set to N. If set to N, the cursor is obsolete, and it will no longer be used.