Literal SQL is not shared in PL/SQL block with cursor_sharing set to Force or Similar (Doc ID 285447.1)

Last updated on APRIL 15, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.5 to 11.2.0.3 [Release 9.2 to 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.

Symptoms

Statements containing literals in pl/sql blocks do not share when cursor_sharing = force or cursor_sharing = force similar.

Literal replacement does not occur.
If the same statements are executed in standard SQL or via execute immediate then they are shared.

e.g.

Using the following table:

create table table1 (
a number,
b number
);


PL/SQL example:


alter system flush shared_pool;

declare
c number(10);
begin
c:=124;
insert into table1 values(1,192);
insert into table1 values(c,193);
insert into table1 values(c,194);
insert into table1 values(1,195);
execute immediate 'insert into table1 values(2,2)';
execute immediate 'insert into table1 values(2,3)';
commit;
end;
/

set lines 132 pages 999
column text format a51
select EXECUTIONS, USERS_EXECUTING, LOADS , substr(SQL_TEXT,1,50) Text from v$sqlarea
where SQL_TEXT like '%table1%' OR SQL_TEXT like '%TABLE1%';

EXECUTIONS USERS_EXECUTING      LOADS TEXT
---------- --------------- ---------- ------------------------------------------------
1 0 1 INSERT INTO TABLE1 VALUES(1,192)
1 0 1 INSERT INTO TABLE1 VALUES(1,195)
1 0 1 INSERT INTO TABLE1 VALUES(:B1,193)
1 0 1 INSERT INTO TABLE1 VALUES(:B1,194)
2 0 1 insert into table1 values(:"SYS_B_0",:"SYS_B_1")


Notice only the execute immediate statements are shared and the literals are not replaced



SQL example:


alter system flush shared_pool;

variable c number;
begin
:c:=124;
end;
/
insert into table1 values(1,192);
insert into table1 values(:c,193);
insert into table1 values(:c,194);
insert into table1 values(1,195);
commit;

EXECUTIONS USERS_EXECUTING      LOADS TEXT
---------- --------------- ---------- ------------------------------------------------
2 0 1 insert into table1 values(:c,:"SYS_B_0")
2 0 1 insert into table1 values(:"SYS_B_0",:"SYS_B_1")


Notice that the example with the literals in and the example with the literal and bind in are shared
as different cursors since the replacement name for the bind variables is different.

NB The wholly literal example would share with the execute immediate statements

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms