Re: alter session set cursor_sharing=force not work in 11.2.0.1

I couldn't reproduce your result on my local 11gR2 database. So let me know some additional infos.

1. Was the SQL text converted to be used with the bind variable, like 'SYS_B_0'?
2. In case of 'YES', what does V$SQL_SHARED_CURSOR view complain about the reason of unshareability?

   ---

http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
http://sites.google.com/site/otpack (tpack)
================================

2010/10/20 Lei Liu (sundog315) <sundog315_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
>

--
http://www.freelists.org/webpage/oracle-l