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

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


  1. Was the SQL text converted to be used with the bind variable, like 'SYS_B_0'? No.

And I found some Interesting things.

if
  select object_id bulk collect into fld_object_id from t where rownum<=1000 then wrong. if
  select object_id bulk collect into fld_object_id from t where rownum<=963 then correct.



Lei Liu (sundog315)
2010-10-20

发件人:Dion Cho
发送日期:2010-10-20 13:19:22
收件人:sundog315
抄送:oracle-l_at_freelists.org
主题:Re: alter session set cursor_sharing=force not work in 11.2.0.1

Hi, Lei Liu.

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?

Dion Cho - Oracle Performance Storyteller
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

>
嗃i0龙込X+凕n枲{+i蒦 Received on Wed Oct 20 2010 - 00:35:47 CDT

Original text of this message