玉面飞龙的BLOG

I tested in 10gR2 as well as 9205. It’s the same.

Janathan’s CBO book talks about this on p.159. He says using the call procedure_name() syntax could avoid the problem. But it didn’t work for me:

SQL> conn yong/yong
Connected.

SQL> alter system flush shared_pool;
System altered.

SQL> select EXECUTIONS, USERS_EXECUTING, LOADS , substr(SQL_TEXT,1,50) Text from v$sqlarea
2 where SQL_TEXT like ‘%table1%’ OR SQL_TEXT like ‘%TABLE1%’;

EXECUTIONS USERS_EXECUTING LOADS TEXT
———- ————— ———- —————————————————
1 1 1 select EXECUTIONS, USERS_EXECUTING, LOADS , substr

SQL> alter session set cursor_sharing = force;
Session altered.

SQL> call p();
Call completed.

SQL> select EXECUTIONS, USERS_EXECUTING, LOADS , substr(SQL_TEXT,1,50) Text from v$sqlarea
2 where SQL_TEXT like ‘%table1%’ OR SQL_TEXT like ‘%TABLE1%’;

EXECUTIONS USERS_EXECUTING LOADS TEXT
———- ————— ———- —————————————————
1 1 1 INSERT INTO TABLE1 VALUES(1,192)
1 1 1 INSERT INTO TABLE1 VALUES(1,195)
1 1 1 INSERT INTO TABLE1 VALUES(:B1,193)
1 1 1 INSERT INTO TABLE1 VALUES(:B1,194)
2 0 1 insert into table1 values(:”SYS_B_0″,:”SYS_B_1″)
2 1 1 select EXECUTIONS, USERS_EXECUTING, LOADS , substr
6 rows selected.

My procedure is created earlier as follows:

SQL> create or replace procedure p as
2 c number(10);
3 begin
4 c:=124;
5 insert into table1 values(1,192);
6 insert into table1 values(c,193);
7 insert into table1 values(c,194);
8 insert into table1 values(1,195);
9 execute immediate ‘insert into table1 values(2,2)’;
10 execute immediate ‘insert into table1 values(2,3)’;
11 commit;
12 end;
13 /

Procedure created.

An unrelated issue is that outlines and cursor_sharing=force combined need some special treatment.
See Jonathan’s book pp.157-158.

Yong Huang