[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/