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

Hi Dion,

  I'm sorry,I did not introduce clear,maybe you can reporduce in this order.
1. alter system set cursor_sharing=exact;
2.
declare 
  type numTab is table of pls_integer index by binary_integer;
  
  i pls_integer;
  nCount pls_integer;
  fld_object_id numTab;
begin
  --execute immediate 'alter session set cursor_sharing=force';
  select object_id bulk collect into fld_object_id from t where rownum<=1000;
  
  for i in 1..fld_object_id.count loop
    execute immediate
    'select count(*)
       from t
      where object_id = '||fld_object_id(i) into nCount;
  end loop;
end;
3.alter system flush shared_pool;
4.
declare 
  type numTab is table of pls_integer index by binary_integer;
  
  i pls_integer;
  nCount pls_integer;
  fld_object_id numTab;
begin
  execute immediate 'alter session set cursor_sharing=force';
  select object_id bulk collect into fld_object_id from t where rownum<=1000;
  
  for i in 1..fld_object_id.count loop
    execute immediate
    'select count(*)
       from t
      where object_id = '||fld_object_id(i) into nCount;
  end loop;
end;
5.select count(*) from v$sql t where t.SQL_TEXT like 'select count(*)        
from t%';
------------------                               
Lei Liu (sundog315)
2010-10-20

-------------------------------------------------------------
发件人:Dion Cho
发送日期:2010-10-20 13:19:22
收件人:sundog315
抄送:oracle-l@xxxxxxxxxxxxx
主题: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@xxxxxxxxx>

> 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?^

Other related posts: