You don’t have to make any changes to your code to take advantage of two of the most important enhancements to Oracle Database 10g PL/SQL: the optimizing compiler and compile-time warnings.

The Optimizing Compiler

PL/SQL’s optimizing compiler can improve runtime performance dramatically, with a relatively slight cost at compile time. The benefits of optimization apply to both interpreted and natively compiled PL/SQL because optimizations are applied by analyzing patterns in source code.

The optimizing compiler is enabled by default. However, you may want to alter its behavior, either by lowering its aggressiveness or by disabling it entirely. For example, if, in the course of normal operations, your system must perform recompilation of many lines of code, or if an application generates many lines of dynamically executed PL/SQL, the overhead of optimization may be unacceptable. Keep in mind, though, that Oracle’s tests show that the optimizer doubles the runtime performance of computationally intensive PL/SQL.

In some cases, the optimizer may even alter program behavior. One such case might occur in code written for Oracle9i Database that depends on the relative timing of initialization sections in multiple packages. If your testing demonstrates such a problem, yet you wish to enjoy the performance benefits of the optimizer, you may want to rewrite the offending code or to introduce an initialization routine that ensures the desired order of execution.

The optimizer settings are defined through the PLSQL_OPTIMIZE_LEVEL initialization parameter (and related ALTER DDL statements), which can be set to 0, 1, or 2. The higher the number, the more aggressive is the optimization, meaning that the compiler will make a greater effort, and possibly restructure more of your code to optimize performance.

Set your optimization level according to the best fit for your application or program, as follows:

PLSQL_OPTIMIZE_LEVEL = 0

Zero essentially turns off optimization. The PL/SQL compiler maintains the original evaluation order of statement processing of Oracle9i Database and earlier releases. Your code will still run faster than in earlier versions, but the difference will not be so dramatic.

PLSQL_OPTIMIZE_LEVEL = 1

The compiler will apply many optimizations to your code, such as eliminating unnecessary computations and exceptions. It will not, in general, change the order of your original source code.

PLSQL_OPTIMIZE_LEVEL = 2

This is the default value and the most aggressive setting. It will apply many modern optimization techniques beyond level 1, and some of those changes may result in moving source code relatively far from its original location. Level 2 optimization offers the greatest boost in performance. It may, however, cause the compilation time in some of your programs to increase substantially. If you encounter this situation (or, alternatively, if you are developing your code and want to minimize compile time, knowing that when you move to production, you will apply the highest optimization level), try cutting back the optimization level to 1.

You can set the optimization level for the instance as a whole, but then override the default for a session or for a particular program. Here are some examples:

  ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 0;

Oracle retains optimizer settings on a module-by-module basis. When you recompile a particular module with nondefault settings, the settings will “stick,” allowing you to recompile later using REUSE SETTINGS. For example:

  ALTER PROCEDURE bigproc COMPILE PLSQL_OPTIMIZE_LEVEL = 0;

and then:

  ALTER PROCEDURE bigproc COMPILE REUSE SETTINGS;

To view all the compiler settings for your modules, including optimizer level, interpreted versus native, and compiler warning levels, query the
USER_PLSQL_OBJECT_SETTINGS view.

For lots more information on the optimizing compiler, see Chapter 23 and visit:

  http://www.oracle.com/technology/tech/pl_sql/htdocs/ new_in_10gr1.htm#faster

Compile-Time Warnings

Compile-time warnings can greatly improve the maintainability of your code and reduce the chance that bugs will creep into it. Compile-time warnings differ from compile-time errors; with warnings, your program will still compile and run. You may, however, encounter unexpected behavior or reduced performance as a result of running code that is flagged with warnings.

This section explores how compile-time warnings work and which issues are currently detected. Let’s start with a quick example of applying compile-time warnings in your session.

A quick example

A very useful compile-time warning is PLW-06002: Unreachable code. Consider the following program (available in the cantgothere.sql file on the book’s web site). Because I have initialized the salary variable to 10,000, the conditional statement will always send me to line 9. Line 7 will never be executed.

       /* File on web: cantgothere.sql * /
  1  CREATE OR REPLACE PROCEDURE cant_go_there
  2  AS
  3     l_salary NUMBER := 10000;
  4  BEGIN
  5      IF l_salary > 20000
  6      THEN
  7         DBMS_OUTPUT.put_line ('Executive');
  8     ELSE
  9       DBMS_OUTPUT.put_line ('Rest of Us');
 10     END IF;
  11  * END cant_go_there;

If I compile this code in any release prior to Oracle Database 10g Release 1, I am sim ply told “Procedure created.” If, however, I have enabled compile-time warnings in my session on the new release and then try to compile the procedure, I get this response from the compiler:

  SP2-0804: Procedure created with compilation warnings

  SQL> sho err
  Errors for PROCEDURE CANT_GO_THERE:

  LINE/COL ERROR 
  -------- ---------------------------------
  7/7      PLW-06002: Unreachable code

Given this warning, I can now go back to that line of code, determine why it is unreachable, and make the appropriate corrections.

If you see a “no message file” message

If you are running 10.1.0.2.0 on Windows, and try to reproduce what I showed in the previous section, you will see this message:

  7/7     PLW-06002: Message 6002 not found;
            No message file for 
          product=plsql, facility=PLW

The problem is that Oracle didn’t ship the message file, plwus.msb, with the Ora cle Database 10g software until 10.1.0.3.0, and the download available on OTN is 10.1.0.2.0. If you encounter this problem, you will need to contact Oracle Support to obtain this file (reference Bug 3680132) and place it in the \plsql\mesg subdirectory. You will then be able to see the actual warning message.

Verify your SQL*Plus version

If you are running a pre-Oracle Database 10g version of SQL*Plus, it will not be able to display warnings; because Oracle9i Database did not support compile-time warnings, commands like SHOW ERRORS don’t even try to obtain warning information.

Specifically, the ALL_ERRORS family of data dictionary views has two new columns in Oracle Database 10g: ATTRIBUTE and MESSAGE_NUMBER. The earlier SQL*Plus versions don’t know how to interpret these columns.

To determine if you are using a pre-Oracle Database 10g version of SQL*Plus, execute these commands in SQL*Plus:

  CREATE TABLE t (n BINARY_FLOAT )
  /
  DESCRIBE t

In such versions of SQL*Plus, you will see that “n” is characterized as “UNDE FINED.” Starting with Oracle Database 10g Release 1, SQL*Plus will properly show the type of this column to be “BINARY_FLOAT.”