QUERIES NOT SHARED WHEN CURSOR_SHARING=FORCE (并未共享游标)

前天有个朋友问我了个问题发现v$sqlarea 中文本相同的sql还有很多记录，也就是并未共享游标，也许很多人知道当cursor_sharing=force，可以尽可能的减小hard parse,记住是min,并不是完全防止硬解析，因为解析也很浪费资源
下面看我的实验

SQL> conn system/oracle
Connected.
SQL> alter system flush shared_pool;
System altered.
SQL> conn anbob/anbob
Connected.
SQL> declare
  2  c int :=100;
  3  begin
  4  insert into testsharing values(100,200);
  5  insert into testsharing values(300,400);
  6  insert into testsharing values(c,500);
  7  insert into testsharing values(c,600);
  8  execute immediate 'insert into testsharing values(911,1)';
  9  execute immediate 'insert into testsharing values(911,2)';
 10  commit;
 11  end;
 12  /
PL/SQL procedure successfully completed.
SQL> conn system/oracle
Connected.
SQL> col sql_text for a70
SQL> l
  1* select executions,loads,sql_text,hash_value,address,sql_id from v$sqlarea where lower(sql_text) like '%testsharing%' and sql_text not like '%$%'
SQL> /
EXECUTIONS      LOADS SQL_TEXT                                                       HASH_VALUE ADDRESS  SQL_ID
---------- ---------- ---------------------------------------------------------------------- ---------- -------- --------------------
         1          2  declare  c int :=100;  begin  insert into testsharing values(100,200) 4245713616 33ABEFB4 0mbv6jgyj0wqh
                      ;  insert into testsharing values(300,400);  insert into testsharing v
                      alues(c,500);  insert into testsharing values(c,600);  execute immedia
                      te 'insert into testsharing values(911,1)';  execute immediate 'insert
                       into testsharing values(911,2)';  commit;  end;
         1          1 INSERT INTO TESTSHARING VALUES(:B1 ,600)                               3173049334 33A26CEC 7cdbkm2yk1szq
         2          1 insert into testsharing values(:"SYS_B_0",:"SYS_B_1")                  1921846338 33AE1570 9tbyx65t8u322
         1          1 INSERT INTO TESTSHARING VALUES(300,400)                                 596477512 33A693DC 5ju5zhwjsv1k8
         1          1 INSERT INTO TESTSHARING VALUES(100,200)                                4073443016 338E0714 dca5787tcrmq8
         1          1 INSERT INTO TESTSHARING VALUES(:B1 ,500)                               3038634095 338C13CC aukkkbqujvs3g
6 rows selected.

note:
当然如果plsql中的两条sql完全一样，包括字面常量是还是可以共享的
execute immediate 的也是可以共享

SQL> conn anbob/anbob
Connected.
SQL> variable c number;
SQL> variable d number;
SQL> begin
  2  :c:=1000;
  3  :d:=2000;
  4  end;
  5  /
PL/SQL procedure successfully completed.
SQL> 
SQL> insert into testsharing values(1,100);
1 row created.
SQL> insert into testsharing values(:c,200);
1 row created.
SQL> insert into testsharing values(:d,300);
1 row created.
SQL> insert into testsharing values(:d,:c);
1 row created.
SQL> insert into testsharing values(2,500);
1 row created.
SQL> commit;
Commit complete.
SQL> conn system/oracle
Connected.
SQL> select executions,loads,sql_text,hash_value,address,sql_id from v$sqlarea where lower(sql_text) like '%testsharing%' and sql_text not like '%$%';
EXECUTIONS      LOADS SQL_TEXT                                                                 HASH_VALUE ADDRESS  SQL_ID
---------- ---------- -------------------------------------------------------------------------------- ---------- -------- --------------------
         1          1 insert into testsharing values(:d,:c)                                            1444290358 338CAB68 bx2d4sxb1c7tq
         1          1 insert into testsharing values(:d,:"SYS_B_0")                                    1404221571 339D2A10 6dg5dhj9v5f43
         2          2 insert into testsharing values(:"SYS_B_0",:"SYS_B_1")                            1921846338 33AE1570 9tbyx65t8u322
         1          1 insert into testsharing values(:c,:"SYS_B_0")                                     335258613 339D2B68 awdyb549zr8zp

note: 如果指定了不同的绑定变量表，parent cursor 也是不可以共享的
除了上面的还有就是准备修改cursor_sharing 前有时要必须连续 flush shared_pool 两次，
接自 MOS

   Interesting. You may need to flush the shared pool "harder"... Try your testcase again, but issue the ALTER SYSTEM FLUSH SHARED_POOL twice in a row before changing the cursor_sharing setting. It looks like that the statement doesn't show up in V$SQLAREA any longer but still seems not to be flushed entirely and I can find some left-overs of it in the underlying X$KGL library cache fixed tables with the same hash value. After the second "flush shared pool" these are gone and then the cursor sharing works as expected.

还有就是在一些版本中的bug,Bug 7026158
如sql: xx where a=xx and id in(1);

This is happening because of the use of the IN operator.
The same queries are shared when using "=" instead.
Also when optimizer_dynamic_sampling is greater than 1, the above queries are
not shared.
When optimizer_dynamic_sampling =1 then they are shared.
如果列上有histogram也会影响share cursor,还有就是java 程序调用 sql时的各种bug,慢慢研究