本文共 2429 字，大约阅读时间需要 8 分钟。
[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 Productioncreate 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://ufhpo.baihongyu.com/