Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL_OPTIMIZE_LEVEL (12.x)
PLSQL_OPTIMIZE_LEVEL [message #642880] Tue, 22 September 2015 04:40 Go to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Set it from 0 to 3, default is 2. From the docs:
Quote:

0

Maintains 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 and PLS_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.

1

Applies 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.

2

Applies 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.

The only feature of 3 that I can find is this, where the compiler replaces calls to subprograms with copies of them:Quote:
With PLSQL_OPTIMIZE_LEVEL=3, the PL/SQL compiler seeks opportunities to inline subprograms. You need not specify subprograms to be inlined. However, you can use the INLINE pragma (with the preceding syntax) to give a subprogram a high priority for inlining, and then the compiler inlines it unless other considerations or limits make the inlining undesirable.

If a particular subprogram is inlined, performance almost always improves. However, because the compiler inlines subprograms early in the optimization process, it is possible for subprogram inlining to preclude later, more powerful optimizations.
I would think that must remove lots of jumps. Anyone using this level? Should it be considered "best practice"?

Thank you for any insight.

Re: PLSQL_OPTIMIZE_LEVEL [message #642882 is a reply to message #642880] Tue, 22 September 2015 04:46 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well the obvious questions are:
what is inlineable?
what more powerful optimizations can be precluded by inlining?
Previous Topic: Get Max Value of Date Column With Condition
Next Topic: Rownum
Goto Forum:
  


Current Time: Fri Mar 29 08:31:41 CDT 2024