>>
You're reading...
Oracle, Oracle 11g

PLSQL_OPTIMIZE_LEVEL: The optimization strategy of Oracle

Attribution: This article is a complimentary excerpt from Oracle Advanced PL/SQL Developer Professional Guide

Oracle initialization parameter PLSQL_OPTIMIZE_LEVEL

The parameter was introduced in Oracle 10g to decide the level of optimization applicable for a program unit during compilation. The optimization during compilation means the removal of dead code, code movements to optimize iterative constructs, inline the calls if required, and choosing the best philosophy to optimally compile the program.

It is set by the DBA at system, session and object level. It is the object compilation parameter which is retained by the object library unit. It can be queried in USER_PLSQL_OBJECT_SETTINGS view for a given object.

Prior to 11g release, the parameter could accommodate only three valid values i.e. 0, 1, and 2. Oracle 11g introduced an additional optimization level 3. By default, the parameter value is 2. The compiler’s effort is directly proportional to the parameter value i.e. more the value, more the compiler’s effort.

It can be set using ALTER [SYSTEM | SESSION] command as shown below

/*To view the current parameter setting*/

Conn / as SYSDBA
Connected.

SQL> show parameter plsql_optimize_level

NAME TYPE VALUE
———————————— ———– —————–
plsql_optimize_level integer 2

/*Modify the setting for the current session*/
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1
/

Session altered.

The description for each value is as below

Case 1: PLSQL_OPTIMIZE_LEVEL = 0

This level represents ‘No Optimization’ mode of the compiler. The compiler only maintains the code evaluation order and executes it without any efforts on code optimization.

Let us write a small program and compare the same with other optimization levels also

Scenario: In your application, you have to give insert a delay of approx 1 second during some user operations. You create a program which runs a large loop to delay the control transfer.

Conn / as sysdba
Connected.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 0;

Session altered.

DECLARE
L_START_TIME NUMBER;
L_END_TIME NUMBER;
A NUMBER; B NUMBER; C NUMBER;
BEGIN
L_START_TIME := DBMS_UTILITY.GET_TIME();
FOR I IN 1..1000000000
LOOP
A := 1;
B := 1;
C := A+1;
END LOOP;
L_END_TIME := DBMS_UTILITY.GET_TIME();
DBMS_OUTPUT.PUT_LINE(‘Execution time:’||TO_CHAR(L_END_TIME – L_START_TIME));
END;
/

Execution time:19294

PL/SQL procedure successfully completed.

Case 2: PLSQL_OPTIMIZE_LEVEL = 1

This level represents ‘Elementary and eliminative Optimization’ level by maintaining the same code order. The compiler optimizes the code by eliminating the code of irrelevant logic.

We shall re execute the previous PL/SQL block and check the difference.

Conn / as sysdba
Connected.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;

Session altered.

DECLARE
L_START_TIME NUMBER;
L_END_TIME NUMBER;
A NUMBER; B NUMBER; C NUMBER;
BEGIN
L_START_TIME := DBMS_UTILITY.GET_TIME();
FOR I IN 1..1000000000
LOOP
A := 1;
B := 1;
C := A+1;
END LOOP;
L_END_TIME := DBMS_UTILITY.GET_TIME();
DBMS_OUTPUT.PUT_LINE(‘Execution time:’||TO_CHAR(L_END_TIME – L_START_TIME));
END;
/

Execution time:7112

PL/SQL procedure successfully completed.

Note that the execution time has reduced by 60% during this optimization level.

Case 3: PLSQL_OPTIMIZE_LEVEL = 2

This is the standard level of optimization where the compiler intelligently does the code refactoring and uses advanced optimization techniques to modify the code structure in order to achieve the best.

Re executing the previous PL/SQL block to compare the ‘Execution time’ results.

Conn / as sysdba
Connected.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;

Session altered.

DECLARE
L_START_TIME NUMBER;
L_END_TIME NUMBER;
A NUMBER; B NUMBER; C NUMBER;
BEGIN
L_START_TIME := DBMS_UTILITY.GET_TIME();
FOR I IN 1..1000000000
LOOP
A := 1;
B := 1;
C := A+1;
END LOOP;
L_END_TIME := DBMS_UTILITY.GET_TIME();
DBMS_OUTPUT.PUT_LINE(‘Execution time:’||TO_CHAR(L_END_TIME – L_START_TIME));
END;
/

Execution time:0

PL/SQL procedure successfully completed.

Just observe the incredible intelligence of the PL/SQL compiler. The complete code has been re-structured. Analytically, the code inside the FOR loop is not relevant to the loop. So it has been moved outside the loop and now, the loop is completely ignored by the compiler. Therefore, the execution time comes out to be zero.

Case 4: PLSQL_OPTIMIZE_LEVEL = 3

This is the latest optimization level introduced in Oracle 11g. It is a sort of ‘Forced’ optimization level, which Oracle terms as ‘High Priority’ optimization. The code optimizer does the code refactoring and automatically inlines all program calls in the program unit.

Conn / as sysdba
Connected.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;

Session altered.

As mentioned earlier, the parameter is treated as the object compilation factors. Hence it is stored in the object library unit and can be queried in
[ALL | DBA | USER]_PLSQL_OBJECT_SETTINGS.

Refer Oracle 11g Documentation on the topic

About these ads

About Saurabh K. Gupta

Product Manager at Oracle, Author and blogger

Discussion

11 thoughts on “PLSQL_OPTIMIZE_LEVEL: The optimization strategy of Oracle

  1. just wondering whether you have factored the result cache in the SGA while doing multiple runs of the same piece of code?

    Nice info, i am reminded of this wonderful piece of nugget.

    Posted by Allen | October 20, 2011, 12:31 am
  2. You are correct. Thanks for the reply. I was fascinated by the compilation setting rather than the code itself.

    What do you suppose this setting would have on SQLs inside a stored unit of code like packages, procedures or functions? I am guessing that it would have little or no effect; I think it is time I hit back to the documentation :)

    Posted by oraclesqlplay | October 20, 2011, 9:29 am
    • Obviosuly, SQL inside the block will make difference. Even with result caching enabled, it will consume time in hitting the cache. I tested it by inserting a simple SELECT statement, but the execution time rolls to 10 folds.

      I have set up result cache in my system and the below blocks have been tested at PLSQL_OPTIMIZE_LEVEL = 1. with optimization level at 2 also, the proportional remains almost same.

      SQL> DECLARE
      2 L_START_TIME NUMBER;
      3 L_END_TIME NUMBER;
      4 A NUMBER; B NUMBER; C NUMBER;
      5 L_SAL NUMBER;
      6 BEGIN
      7 L_START_TIME := DBMS_UTILITY.GET_TIME();
      8 FOR I IN 1..10000
      9 LOOP
      10 A := 1;
      11 B := 1;
      12 C := A+1;
      13 END LOOP;
      14 L_END_TIME := DBMS_UTILITY.GET_TIME();
      15 DBMS_OUTPUT.PUT_LINE(‘Execution time:’||TO_CHAR(L_END_TIME – L_START_TIME));
      16 END;
      17 /
      Execution time:53

      PL/SQL procedure successfully completed.

      SQL> ED
      Wrote file afiedt.buf

      1 DECLARE
      2 L_START_TIME NUMBER;
      3 L_END_TIME NUMBER;
      4 A NUMBER; B NUMBER; C NUMBER;
      5 L_SAL NUMBER;
      6 BEGIN
      7 L_START_TIME := DBMS_UTILITY.GET_TIME();
      8 FOR I IN 1..10000
      9 LOOP
      10 SELECT /*+RESULT_CACHE*/ SAL INTO L_SAL from emp_details WHERE EMPNO=7900;
      11 A := 1;
      12 B := 1;
      13 C := A+1;
      14 END LOOP;
      15 L_END_TIME := DBMS_UTILITY.GET_TIME();
      16 DBMS_OUTPUT.PUT_LINE(‘Execution time:’||TO_CHAR(L_END_TIME – L_START_TIME));
      17* END;
      SQL> /
      Execution time:539

      PL/SQL procedure successfully completed.

      Posted by sbhoracle | October 20, 2011, 10:02 pm
  3. Thanks, Nice information.

    I think just one small typo correction on case:1 . it should be level=0 not 2. (1st example on very top)

    Thanks for sharing very useful information.

    Mohammed.

    Posted by Mohammed | October 6, 2012, 7:32 pm
  4. Little addition – I wrote about loops optimizations with deterministic functions in 11g: http://orasql.org/2013/03/13/optimization-of-loops-in-plsql-part-1/

    Posted by Sayan Malakshinov | March 13, 2013, 3:54 pm
  5. Hi Sayan, nice addition to the topic.

    Posted by Saurabh K. Gupta | June 2, 2013, 12:19 am
  6. Do you mind if I quote a couple of your articles as long as I provide credit and sources back to your webpage?
    My blog site is in the exact same niche as yours and my users
    would certainly benefit from some of the information you provide here.
    Please let me know if this ok with you. Appreciate it!

    Posted by palm beach hurricane windows | September 25, 2014, 11:24 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Saurabh K. Gupta

Saurabh K. Gupta

Product Manager at Oracle, Author and blogger

Personal Links

View Full Profile →

Twitter Profile

My Book

Oracle Advanced PL/SQL Developer Professional Guide

Disclaimer

SBHOracle is an independent blog and all the posts are based on my self experience and hands on with the technologies. It shares no relations with any of my current projects or from those in the past.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 371 other followers

SbhOracle Blog stats

  • 43,492 hits
Aggregated by OraNA
Follow

Get every new post delivered to your Inbox.

Join 371 other followers

%d bloggers like this: