Question

我们发出一份准备的声明,需要就 d采取多项行动,因此所有这些q言都附在像BEGIN-END一样的BEGIN-END栏中。

BEGIN
 DELETE FROM...WHERE A=?..
 UPDATE TABLE...WHERE B=?..
END;

But many reviewers said this will result in a hard parse. From my understanding hard parse is when an sql is not found in the shared pool, then the syntax, execution plan ..everything needs to be calculated again, but here shouldn t Oracle treat them as individual sql statements. How can I find out that oracle will do a hard parse or not on a given sql statement?

Answer 1

Bin变数在PL/SQL区进行工作,在K.表中也是如此。

可以通过在休息室做简单发言来测试这一点,然后看v$sesstat。

建立一个简单的表格,用于插入和删除。 2. 初步 par计。

create table test1(a number);

--Flush the pool, or else this test won t be repeatable.
alter system flush shared_pool;

select value, name
from v$sesstat natural join v$statname
where sid = sys_context( userenv ,  sid )
    and name in ( parse count (total) ,  parse count (hard) );

47  parse count (total)
5   parse count (hard)

这正是我们所期望的:

begin
    for i in 1 .. 10000 loop
        execute immediate  insert into test1 values( ||i|| ) ;
    end loop;
    commit;
end;
/

select value, name
from v$sesstat natural join v$statname
where sid = sys_context( userenv ,  sid )
    and name in ( parse count (total) ,  parse count (hard) );

10072   parse count (total)
10007   parse count (hard)

具有变量的PL/SQL区并非总是硬木。请注意, par计是累积的,在此仅略有增加。

begin
    for i in 1 .. 10000 loop
        execute immediate 
         begin
            delete from test1 where a = :i;
        end; 
        using i;
    end loop;
    commit;
end;
/

select value, name
from v$sesstat natural join v$statname
where sid = sys_context( userenv ,  sid )
    and name in ( parse count (total) ,  parse count (hard) );

10106   parse count (total)
10019   parse count (hard)

Answer 2

追踪贵会议执行该守则的情况。停机坪,检查区数。

你们是否使用具有约束力的变数,还是使用字面?

建议的变数是,当你使用——改变——字面,这是造成 par平的因素之一。改变字面字面,确实贬低了你的表现,使用了约束性。

http://www.you Programme.com/watch?v=1oddFEyUAjs” rel=“nofollow” OLTP Performance - The Trouble with Parsing

友情链接