Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL_OPTIMIZE_LEVEL (oracle10g)
PLSQL_OPTIMIZE_LEVEL [message #354118] Thu, 16 October 2008 08:16 Go to next message
shrinika
Messages: 252
Registered: April 2008
Senior Member
Hello,

I have few clarification in PLSQL_OPTIMIZE_LEVEL parameter. The valid values are 0,1,2.

Here is the information i found from doc.

PLSQL_OPTIMIZE_LEVEL = 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.

Question1. It does not move source code out of its original source order.
What does it mean? Can you please explain?

PLSQL_OPTIMIZE_LEVEL = 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.

Question2. When we should we use PLSQL_OPTIMIZE_LEVEL = 1
and when should we use PLSQL_OPTIMIZE_LEVEL = 2 ?

Any help is appreicated.
Re: PLSQL_OPTIMIZE_LEVEL [message #431261 is a reply to message #354118] Mon, 16 November 2009 06:30 Go to previous message
sajith741
Messages: 11
Registered: November 2009
Location: Singapore
Junior Member
The plsql_optimize_level defines the following:-

· 2 Most aggressive, maximum possible code transformations, biggest impact on compile time. [default]

· 1 Smaller scale change, less impact on compile times

· 0 Pre-10g compilation without optimization

Why Change?

2 is the default value and is applicable in most environments. But, sometimes certain Oracle bugs have caused the code transformation to run into errors/ unstable output. Example:
We had an interesting issue with a stored procedure using dbms_sql.describe_columns which intermittently returned wrong results.

Later, Oracle confirmed that it was due to bug 4619731:
Quote:


PLSQL may see wrong (old) value for a package variable. The PLSQL global optimizer incorrectly optimizes away a read of a field of a package variable after the field had been updated in a subroutine. Code subsequent to the subroutine call thus does not see the updated variable of the field.

Workaround:
Set plsql_optimize_level = 0 or 1.

PS: This has been fixed in 11g and 10.2.0.4



How to change?

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 0;

[Updated on: Mon, 16 November 2009 06:38] by Moderator

Report message to a moderator

Previous Topic: how to check blob.....
Next Topic: Materialized view across network
Goto Forum:
  


Current Time: Thu Sep 29 22:44:44 CDT 2016

Total time taken to generate the page: 0.10188 seconds