[20160517]11GR2Cursor_Sharing=force的bug.txt

--链接https://jonathanlewis.wordpress.com/2016/05/16/cursor_sharing-problem/,重复测试:

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING         VERSION    BANNER
------------------- ---------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t1 ( n1 number(8,0), v1 varchar2(10), d1 date, t1 timestamp);
insert into t1 values(-1,'x',sysdate, systimestamp);
begin
dbms_stats.gather_table_stats(
ownname          => user,
tabname          =>'T1',
method_opt       => 'for all columns size 1'
);
end;
/

2.测试插入数据:
alter session set cursor_sharing = force;
insert into t1 values(1, 'A', date'2016-01-01', null);
insert into t1 values(1, 'A', date'2016-01-02', null);
insert into t1 values(1, 'A', date'2016-01-03', null);

--记下sql_id=58udhcm270bhn

insert into t1 values(1, 'A', null, timestamp'2016-01-01 00:00:00');
insert into t1 values(1, 'A', null, timestamp'2016-01-02 00:00:00');
insert into t1 values(1, 'A', null, timestamp'2016-01-03 00:00:00');

--记下sql_id=d6psz1h127xm5

SELECT sql_id, child_number, hash_match_failed
  FROM v$sql_shared_cursor
WHERE sql_id in ('58udhcm270bhn', 'gssz5cbnt7mgn')
order by sql_id, child_number ;

SQL_ID        CHILD_NUMBER H
------------- ------------ -
58udhcm270bhn            0 N
58udhcm270bhn            1 Y
58udhcm270bhn            2 Y
gssz5cbnt7mgn            0 N
gssz5cbnt7mgn            1 Y
gssz5cbnt7mgn            2 Y
6 rows selected.

--可以发现每次执行产生1个子光标,导致光标无法共享.
SCOTT@book> @ &r/share gssz5cbnt7mgn
SQL_TEXT                       = insert into t1 values(:"SYS_B_0", :"SYS_B_1", null, timestamp:"SYS_B_2")
SQL_ID                         = gssz5cbnt7mgn
ADDRESS                        = 000000007B8F7CE8
CHILD_ADDRESS                  = 000000007B5B1950
CHILD_NUMBER                   = 0
REASON                         =
--------------------------------------------------
SQL_TEXT                       = insert into t1 values(:"SYS_B_0", :"SYS_B_1", null, timestamp:"SYS_B_2")
SQL_ID                         = gssz5cbnt7mgn
ADDRESS                        = 000000007B8F7CE8
CHILD_ADDRESS                  = 000000007CF24D60
CHILD_NUMBER                   = 1
HASH_MATCH_FAILED              = Y
REASON                         =
--------------------------------------------------
SQL_TEXT                       = insert into t1 values(:"SYS_B_0", :"SYS_B_1", null, timestamp:"SYS_B_2")
SQL_ID                         = gssz5cbnt7mgn
ADDRESS                        = 000000007B8F7CE8
CHILD_ADDRESS                  = 000000007CC3B258
CHILD_NUMBER                   = 2
HASH_MATCH_FAILED              = Y
REASON                         =
--------------------------------------------------
PL/SQL procedure successfully completed.

--看来解决sql语句共享的***的方案还是"合理"地使用绑定变量.通过参数cursor_sharing = force往往是没有办法的办法.
--我在10g下使用dblink也遇到类似的问题.链接:http://blog.itpub.net/267265/viewspace-1985215/