alter session set cursor_sharing=force not work in 11.2.0.1

From: Lei Liu (sundog315) <"Lei>
Date: Wed, 20 Oct 2010 11:47:09 +0800
Message-ID: <201010201147049997795_at_gmail.com>



Hi all,

  after upgrade to 11G, alter session set cursor_sharing=force does not work correctly. any suggestion?                                  

  In 11g:
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 --not work

  In 10g work correctly:
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                     --correctly

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"

regards



Lei Liu (sundog315)
2010-10-20
†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Tue Oct 19 2010 - 22:47:09 CDT

Original text of this message