ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Literal SQL is not shared in PL/SQL block

Literal SQL is not shared in PL/SQL block

原创 Linux操作系统 作者:mkcvictor 时间:2019-05-24 14:09:06 0 删除 编辑

Article-ID: Note 285447.1
Circulation: PUBLISHED (EXTERNAL)
Folder: ST.Server.Performance.Database
Topic: Tuning Shared Pool
Title: Literal SQL is not shared in PL/SQL block with
cursor_sharing set to Force or Similar
Document-Type: PROBLEM
Impact: LOW
Skill-Level: NOVICE
Updated-Date: 07-DEC-2004 17:08:45
References: Bug 2175934
Authors: SDIXON.UK
Attachments: NONE
Content-Type: TEXT/X-HTML
Products: 5/RDBMS.DBPERF (9.2.0.5);
Platforms: 0;

@ (AuthWiz 1.4) Created from SR 4139888.995.
@ Click here to edit in wizard.  

The information in this document applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.5
This problem can occur on 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 hte 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

Not sharing the statements in a pl/sql block is a design decision and is not a bug.
It is also non-trivial to change
If this needs to be changed then it would be an enhancement.

Fix

Rewrite the query.
Do not expect these statements to be shared.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11083/viewspace-374626/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oradism_issue
请登录后发表评论 登录
全部评论

注册时间:2002-05-07

  • 博文量
    29
  • 访问量
    21954