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 >