Oracle 参数 PLSQL_OPTIMIZE_LEVEL 官方解释，作用，如何配置最优化建议
本站中文解释
PLSQL_OPTIMIZE_LEVEL是oracle数据库PL/SQL执行引擎的参数，PLSQL_OPTIMIZE_LEVEL定义PL/SQL编译器的编译级别，用于对程序的性能进行改进。PLSQL_OPTIMIZE_LEVEL的值可以是0, 1, 2，或者是2值。
当设置PLSQL_OPTIMIZE_LEVEL=0时，PL/SQL编译器不会进行任何优化，输出的代码仍然会保持原样，可能不能得到最佳执行效果。
当设置PLSQL_OPTIMIZE_LEVEL=1时，PL/SQL编译器会进行少量的优化，使得程序的执行效率能够有较大的提升，但其内的代码依然保持原来的结构，只是有重新编译的特征。
当设置PLSQL_OPTIMIZE_LEVEL=2时，PL/SQL编译器会进行大量的优化，将会应用各种高效的技术，如宏展开，复位，自适应等，达到最佳的性能效果。
建议设置PLSQL_OPTIMIZE_LEVEL=2时，由于适用高效的优化会带来一定安全风险，因此在生产环境中慎用。
同时，在更改PLSQL_OPTIMIZE_LEVEL参数后，最好重新编译更改了参数的PL/SQL对象，以获得最佳的性能效果。
官方英文解释
PLSQL_OPTIMIZE_LEVEL specifies the optimization level that will be used to compile PL/SQL library units. The higher the setting of this parameter, the more effort the compiler makes to optimize PL/SQL library units.
| Property | Description |
|---|---|
|
Parameter type |
Integer |
|
Default value |
|
|
Modifiable |
|
|
Modifiable in a PDB |
Yes |
|
Range of values |
|
|
Basic |
No |
Values
-
0Maintains the evaluation order and hence the pattern of side effects, exceptions, and package initializations of Oracle9i and earlier releases. Also removes the new semantic identity of
BINARY_INTEGERandPLS_INTEGERand restores the earlier rules for the evaluation of integer expressions. Although code will run somewhat faster than it did in Oracle9i, use of level 0 will forfeit most of the performance gains of PL/SQL in Oracle Database 10g. -
1Applies a wide range of optimizations to PL/SQL programs including the elimination of unnecessary computations and exceptions, but generally does not move source code out of its original source order.
-
2Applies a wide range of modern optimization techniques beyond those of level 1 including changes which may move source code relatively far from its original location.
-
3
Applies a wide range of optimization techniques beyond those of level 2, automatically including techniques not specifically requested.
Generally, setting this parameter to 2 pays off in better execution performance. If, however, the compiler runs slowly on a particular source module or if optimization does not make sense for some reason (for example, during rapid turnaround development), then setting this parameter to 1 will result in almost as good a compilation with less use of compile-time resources.
The value of this parameter is stored persistently with the library unit.
See Also:
-
Oracle Database PL/SQL
Language Reference for more information about this parameter -
Oracle Database
Development Guide for an example of using this parameter
