11G的BUG，cursor_sharing=force不好用了

11.2.0.1:
SUNDOG315>select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SUNDOG315>create table t as select * from dba_objects;

表已创建。

SUNDOG315>alter system flush shared_pool;

系统已更改。

SUNDOG315>select count(*) from v$sql t where t.SQL_TEXT like 'select count(*)
     from t%';

  COUNT(*)
----------
         0

SUNDOG315>declare
  2    type numTab is table of pls_integer index by binary_integer;
  3
  4    i pls_integer;
  5    nCount pls_integer;
  6    fld_object_id numTab;
  7  begin
  8    execute immediate 'alter session set cursor_sharing=force';
  9    select object_id bulk collect into fld_object_id from t where rownum<=100
0;
10
11    for i in 1..fld_object_id.count loop
12      execute immediate
13      'select count(*)
14         from t
15        where object_id = '||fld_object_id(i) into nCount;
16    end loop;
17  end;
18  /

PL/SQL 过程已成功完成。

SUNDOG315>select count(*) from v$sql t where t.SQL_TEXT like 'select count(*)
     from t%';

  COUNT(*)
----------
       995

10.2.0.4:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create table t as select * from dba_objects;

表已创建。

SQL> alter system flush shared_pool;

系统已更改。

SQL> select count(*) from v$sql t where t.SQL_TEXT like 'select count(*)
  2       from t%';

  COUNT(*)
----------
         0

SQL> declare
  2    type numTab is table of pls_integer index by binary_integer;
  3
  4    i pls_integer;
  5    nCount pls_integer;
  6    fld_object_id numTab;
  7  begin
  8    execute immediate 'alter session set cursor_sharing=force';
  9    select object_id bulk collect into fld_object_id from t where rownum<=100
0;
10
11    for i in 1..fld_object_id.count loop
12      execute immediate
13      'select count(*)
14         from t
15        where object_id = '||fld_object_id(i) into nCount;
16    end loop;
17  end;
18  /

PL/SQL 过程已成功完成。

SQL> select count(*) from v$sql t where t.SQL_TEXT like 'select count(*)
from t%';

  COUNT(*)
----------
         1

SQL> select sql_text from v$sql t where t.SQL_TEXT like 'select count(*)
from t%';

SQL_TEXT
--------------------------------------------------------------------------------

select count(*)        from t       where object_id = :"SYS_B_0"


10G运行的好好的，在11Gcursor_sharing=force就不起作用了。郁闷

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19423/viewspace-676331/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19423/viewspace-676331/