参数 PLSQL_OPTIMIZE_LEVEL

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.