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 14:33:19 +0800
Message-ID: <201010201433149199911_at_gmail.com>

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_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
>

†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Wed Oct 20 2010 - 01:33:19 CDT