参数 PLSQL_OPTIMIZE_LEVEL

最新推荐文章于 2021-04-03 03:16:12 发布

xiadingling

最新推荐文章于 2021-04-03 03:16:12 发布

阅读量2.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： OCP

本文链接：https://blog.youkuaiyun.com/xiadingling/article/details/8330870

OCP 专栏收录该内容

72 篇文章

订阅专栏

PLSQL_OPTIMIZE_LEVEL参数设定为2时，能够提供更好的运行时性能，但可能会稍微降低编译器性能。该参数影响PL/SQL库单元的优化级别，值越高，编译器优化程度越高。在Oracle数据库中，设置为2通常会在执行性能上带来提升，但在特定情况下可能导致编译器运行变慢。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

196. QUESTION 32
In your database server, the parameter PLSQL_OPTIMIZE_LEVEL has been set to 2.
What would this setting achieve?

A> It degrades the run time and compiler performance.
B> It provides better run time and compiler performance.
C> It provides better optimization of the statement during parse time.
D> It forces the rule based optimizer to be used for statement optimization.
E> It provides better run time performance but slightly degraded compiler performance.
F> It provides better run time performance but slightly degraded run time performance.

ANSWER:E

PLSQL_OPTIMIZE_LEVEL

该初始化参数用于指定当编译PL/SQL库单元时要使用的优化级别，其数值范围为0~2，默认值为0。该参数设置越大，编译器会更好地优化PL/SQL库单元。该初始化参数是动态参数，可以使用ALTER SESSION或ALTER SYSTEM命令进行修改。示例如下：

SQL>ALTER SESSION SET Plsql_optimize_level=2;

附甲骨文官方说明：

属性	说明
参数类型	Integer
默认值	`2`
允许动态修改	`ALTER SESSION`, `ALTER SYSTEM`
取值范围	`0` To `2`

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.

取值说明:

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_INTEGER AndPLS_INTEGER And 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.

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.