alter session set cursor_sharing=force not work in 11.2.0.1
- From: "Lei Liu (sundog315)" <sundog315@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 20 Oct 2010 11:47:09 +0800
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
Other related posts:
