sql - 为什么 PLSQL 优化级别没有提供预期的结果?
问题描述
我试图深入了解 plsql 中的编译器优化。理论上,默认优化级别PLSQL_OPTIMIZE_LEVEL
设置为 2。为了获得更好的性能,我们可以将其设置为 3。为了解释这一点,我使用一个过程调用另一个过程的示例,以便 3 级的优化特性 (内联程序)可以使用。
这是第一个过程:
create or replace procedure p1
is
n number:=0;
begin
for i in 1..500000000
loop
n:=n+1;
end loop;
end;
这是第二个:
create or replace procedure CALL_PROC_ARITH
IS
BEGIN
for i in 1..10
loop
P1;
end loop;
END;
这是plsql_code_type
两个程序的INTERPRETED
.
所以最初两个程序的优化级别都是 2。当我执行程序CALL_PROC_ARITH
时,大约需要 05:00.866 分钟。
后来,我在会话级别将优化级别修改为 3。当我执行程序CALL_PROC_ARITH
时,大约需要 00:05:05.011 分钟,这增加了 5 秒。
有人可以告诉我为什么我看到这种与预期行为的偏差吗?
如果我使用 NATIVE 编译,我会看到不同的结果吗?
注意:我是从 IDE 运行它,而不是直接从 SQLPlus CLI 运行。
数据库:Oracle 18c XE
解决方案
您应该使用更好的资源来理解PLSQL_OPTIMIZE_LEVEL
,并且应该确保以正确的方式测试正确的东西。
1、PLSQL_OPTIMIZE_LEVEL是如何工作的?
了解任何参数的最佳方式是使用官方文档中的数据库参考。该参数PLSQL_OPTIMIZE_LEVEL
经常更改,因此请确保您参考准确的版本。网络上有很多非官方的过时信息,但这里是 18c 的相关文本:
0
维护评估顺序,从而维护 Oracle9i 和更早版本的副作用、异常和包初始化的模式。还删除了 BINARY_INTEGER 和 PLS_INTEGER 的新语义标识,并恢复了早期的整数表达式求值规则。尽管代码的运行速度会比在 Oracle9i 中快一些,但使用级别 0 将失去 Oracle 数据库 10g 中 PL/SQL 的大部分性能提升。
1
对 PL/SQL 程序进行广泛的优化,包括消除不必要的计算和异常,但通常不会将源代码移出其原始源顺序。
2
应用超出级别 1 的广泛现代优化技术,包括可能将源代码相对远离其原始位置的更改。
3
应用超出级别 2 的各种优化技术,自动包括未特别要求的技术。
这种描述使得很难判断何时会发生内联。听起来内联可能发生在级别 1,也可能发生在级别 2。我下面的测试显示,从 0 到 1 的内联性能差异很大,从 1 到 2 的差异非常小,从 2 到 3 没有差异。
但是很多行为都没有记录,所以很难判断什么时候会发生哪种优化。
2、设置关卡后是否重新编译代码?
仅设置会话值是不够的,还必须重新编译程序,如下所示:
alter session set plsql_optimize_level=3;
alter procedure call_proc_arith compile;
alter procedure p1 compile;
3. 你真的在测试内联吗?
您的程序包含很多循环和程序调用,但我认为您的数字倒退了。要测试内联,您必须让大循环调用该过程,而小循环进行计数。您永远不会注意到只有 10 个过程调用的编译器差异。
我使用这些程序进行测试:
create or replace procedure p2 is
n number:=0;
begin
for i in 1..5 loop
n:=n+1;
end loop;
end;
/
create or replace procedure CALL_PROC_ARITH2 is
begin
for i in 1..10000000 loop
p2;
end loop;
end;
/
--Check the PL/SQL optimize level for the objects.
select name, plsql_optimize_level, plsql_code_type
from all_plsql_object_settings
where owner = user
and name like 'CALL_PROC%' or name like 'P_';
4. 您的测试方法是否足够稳健?
您的测试应该尝试补偿消耗 CPU 的其他活动。以交替顺序运行多个小测试,抛出高值和低值,并比较平均值。与两次运行五分钟测试的五秒差异并不显着。
我使用下面的 PL/SQL 块来测试运行时间。(您可以构建一个 PL/SQL 程序以随机顺序运行块并记录时间。我手动完成了该部分。)级别 3 和 2 运行速度相同,级别 1 慢一点,级别 0 显着慢点。
--Level 3: 3.331, 3.403, 3.419
alter session set plsql_optimize_level = 3;
alter procedure call_proc_arith2 compile;
alter procedure p2 compile;
begin
call_proc_arith2;
end;
/
--Level 2: 3.383, 3.470, 3.444
alter session set plsql_optimize_level = 2;
alter procedure call_proc_arith2 compile;
alter procedure p2 compile;
begin
call_proc_arith2;
end;
/
--Level 1: 3.867, 3.859, 3.873
alter session set plsql_optimize_level = 1;
alter procedure call_proc_arith2 compile;
alter procedure p2 compile;
begin
call_proc_arith2;
end;
/
--Level 0: 6.286, 6.296, 6.315
alter session set plsql_optimize_level = 0;
alter procedure call_proc_arith2 compile;
alter procedure p2 compile;
begin
call_proc_arith2;
end;
/
5. 你是否关心 PL/SQL 优化?
在大多数现实世界的 PL/SQL 程序中,内联过程不会产生有意义的差异。最佳实践是尽可能多地使用 SQL 进行繁重的工作。但无论您的逻辑在哪里,请确保您使用的是分析器,并且只调整需要大量时间的程序部分。在调整 PL/SQL 程序的一部分之前,您应该有一些硬数字,例如“如果我优化第 X 行,程序的运行速度可以提高 Y%”。
推荐阅读
- c# - Json 对象 Asp.net Core 3.1 的最大长度
- node.js - 如何使用 express、jest 和 supertest 修复返回 404 状态代码而不是 200 的端点测试
- javascript - 节点 JS/localhost 服务器未显示图像
- java - 回收站视图过滤器在 android 中没有给出想要的结果
- java - 运行 Jar 的工作目录文件夹
- sql - 在 T-SQL 中获取不同的逗号分隔字符串
- angular - 从 Angular TestBed 中访问 DOM 正文元素
- python - Python + Crontab 的反应很奇怪
- python - Python Telegram bot - 第二个 InLineKeyboard 不起作用
- python-2.7 - 将变量写为函数的输入/输出的更短的方法