嵌套块在PL / SQL过程中是否具有任何性能影响？ [英] Do nested blocks have any performance impact in PL/SQL procedures?
问题描述
我想知道的是，如果这些附加块有任何性能影响。我知道提升异常有性能影响，所以我从不依赖异常作为控制流机制。但是块本身是否有任何影响？
例如，这个表现之间是否有区别：
procedure do_something
as
declare
some_var number;
begin
从some_table中将some_value选择为some_var;
exception
当其他人
raise_application_error（-20000，'Exception：'|| sqlerrm，true）;
end do_something;
此处：
procedure do_something
as
declare
some_var number;
begin
begin
从some_table中将some_valar选择为some_var;
exception
当no_data_found然后
some_var：= -23; - 默认值
结束;
exception
当其他人
raise_application_error（-20000，'Exception：'|| sqlerrm，true）;
end do_something;
（我知道这段代码是荒谬的，但我希望它能说明我的意思！）
我真正关心的是没有异常引发的性能 - 当有异常时，我可以接受性能下降。
他们似乎没有：
设置时间为
设置serveroutput
声明
x number：= 0;
begin
dbms_output.put_line（'无内部块'）;
for i in 1..1000000 loop
x：= x + 1;
结束循环;
dbms_output.put_line（x）;
结束
/
匿名块已完成
已过期：00：00：00.095
无内部块
1000000
同时运行，每种方式都有一些变化，如：
declare
x number：= 0;
begin
dbms_output.put_line（'嵌套内部块'）;
我在1..1000000循环
开始
开始
开始
开始
x：= x + 1;
异常
当别人然后
加注;
结束
异常
当别人然后
加注;
结束
异常
当别人然后
加注;
结束
异常
当别人然后
加注;
结束
结束循环;
dbms_output.put_line（x）;
结束
/
匿名块已完成
已过期：00：00：00.090
嵌套内块
1000000
当然，编译器可能会删除冗余层，但是我不知道这可能会与异常处理程序在一起，因为它会影响结果
我没有看到嵌套块的深度有多大限制 - 文档只是说块可以嵌套。您正在使用的模型，捕捉特定错误并让其他人传播，这是很好的，很漂亮的标准 - 虽然显然在您的设计示例中并不是必需的，但您知道。
In PL/SQL procedures, I often wrap statements in a block (i.e. begin...end) so that I can isolate exceptions from that statement. For example, if I'm doing a select that might raise "no_data_found", I can handle that in the knowledge that the exception was raised by that statement, but let other exceptions propagate to the main exception handler for the procedure.
What I'm wondering is if these additional blocks have any performance impact. I know raising exceptions has a performance impact, so I never rely on exceptions as a control flow mechanism. But does the block itself have any impact?
For example, should there be any difference between the performance of the this:
procedure do_something
as
declare
some_var number;
begin
select some_value into some_var from some_table;
exception
when others then
raise_application_error(-20000, 'Exception: ' || sqlerrm, true);
end do_something;
and this:
procedure do_something
as
declare
some_var number;
begin
begin
select some_value into some_var from some_table;
exception
when no_data_found then
some_var := -23; --some default value
end;
exception
when others then
raise_application_error(-20000, 'Exception: ' || sqlerrm, true);
end do_something;
(I know this code is nonsensical, but I hope it illustrates what I mean!)
All I really care about is the performance when no exceptions are raised - I can accept performance degradation when there is an exception.
They don't appear to:
set timing on
set serveroutput on
declare
x number := 0;
begin
dbms_output.put_line('No inner blocks');
for i in 1..1000000 loop
x := x + 1;
end loop;
dbms_output.put_line(x);
end;
/
anonymous block completed
Elapsed: 00:00:00.095
No inner blocks
1000000
Runs in the same time, with a bit of variation each way, as:
declare
x number := 0;
begin
dbms_output.put_line('Nested inner blocks');
for i in 1..1000000 loop
begin
begin
begin
begin
x := x + 1;
exception
when others then
raise;
end;
exception
when others then
raise;
end;
exception
when others then
raise;
end;
exception
when others then
raise;
end;
end loop;
dbms_output.put_line(x);
end;
/
anonymous block completed
Elapsed: 00:00:00.090
Nested inner blocks
1000000
Of course it's possible the compiler is removing the redundant layers, but I'm not sure it really can with the exception handlers there as it would affect the result.
I haven't seen any limitation on how deep nested blocks can go - the documentation just says 'blocks can be nested'. The model you're using, catching a specific error and letting others propagate, is fine and pretty standard - though obviously in your contrived example it isn't necessary, but you know that.
这篇关于嵌套块在PL / SQL过程中是否具有任何性能影响？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
