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

From: Yong Huang <yong321_at_yahoo.com>
Date: Wed, 20 Oct 2010 07:32:35 -0700 (PDT)
Message-ID: <99597.30682.qm_at_web80605.mail.mud.yahoo.com>

Like Dion, I can't reproduce, i.e., cursor_sharing=force works fine for me.

SQL> alter session set cursor_sharing = exact;

Session altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select count(*) from v$sql t where lower(t.SQL_TEXT) like 'select count(*) from t%';

    COUNT(*)

---

           0

SQL> declare

  2     type numTab is table of pls_integer index by binary_integer;
  3     i pls_integer;
  4     nCount pls_integer;
  5     fld_object_id numTab;
  6   begin
  7     execute immediate 'alter session set cursor_sharing=force';
  8     select object_id bulk collect into fld_object_id from t where rownum<=100;
  9    for i in 1..fld_object_id.count loop
 10      execute immediate
 11      'select count(*) from t where object_id = '||fld_object_id(i) into nCount;

 12 end loop;
 13 end;
 14 /

PL/SQL procedure successfully completed.

SQL> select count(*) from v$sql t where lower(t.SQL_TEXT) like 'select count(*) from t%';

    COUNT(*)

---

           1

SQL> select sql_text from v$sql t where lower(t.SQL_TEXT) like 'select count(*) from t%';

SQL_TEXT

---

select count(*) from t where object_id = :"SYS_B_0"

SQL> select * from v$version where rownum = 1;

BANNER

---

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Cursor_sharing=force will not work in PL/SQL *if* the SQL is indeed in PL/SQL context (see Note:285447.1). But after execute immediate, cursor_sharing=force should work.

Yong Huang       

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

Received on Wed Oct 20 2010 - 09:32:35 CDT