The Best Oracle Resource on the Web

Advanced PL/SQL Code Tuning by Mike Ault and Eric Mortensen

Introduction

This article discusses several performance enhancements that are valuable in PL/SQL, but are not complex enough to merit individual articles. In this article, we will discuss the use of an alternative DUAL table, use of NOCOPY, use of HINTs, and several other coding techniques to make your PL/SQL code more efficient.

An alternative DUAL

There is a cost when selecting from DUAL, even if we're only selecting a constant value. To demonstrate:

SQL> select 1 from dual;

We see that each select from DUAL costs 3 consistent gets. Now, if you do a lot of selecting from DUAL your code might benefit from using an alternative DUAL, one that only requires 1 consistent get. Here's how it's done:

create table xdual (
   dummy varchar2(1) primary key
)
organization index;

The trick is to create the XDUAL as an index-organized table and analyze it properly. This allows the cost-based optimizer to generate the most efficient plan possible, which requires only 1 consistent get:

SQL> select 1 from xdual;

To see how these two compare, consider the following code example:

declare
    x number;
begin
    for i in 1 .. 10000 loop
        select 1 into x from dual;
    end loop;
end;

This required about 0.32 seconds on my machine using dual, whereas if I exchanged XDUAL with DUAL I had to wait only 0.25 seconds. Not a whole lot, but it's still about 20% reduction. Also keep in mind that consistent gets = CPU resources so if you reduce the required number of consistent gets you reduce the need for CPU.

Note that in Oracle 10g this situation disappears since the DUAL table now has become a special internal table that requires no consistent gets!

Passing Large Data Structures with NOCOPY

The PL/SQL runtime engine has two different methods for passing parameter values between stored procedures and functions, by value and by reference.

When a parameter is passed by value the PL/SQL runtime engine copies the actual value of the parameter into the formal parameter. Any changes made to the parameter inside the procedure has no effect on the values of the variables that were passed to the procedure from outside.

When a parameter is passed by reference the runtime engine sets up the procedure call so that both the actual and the formal parameters point (reference) the same memory location that holds the value of the parameter.

By default OUT and IN OUT parameters are passed by value and IN parameters are passed by reference. When an OUT or IN OUT parameter is modified inside the procedure the procedure actually only modifies a copy of the parameter value. Only when the procedure has finished without exception is the result value copied back to the formal parameter.

Now, if you pass a large collection as an OUT or an IN OUT parameter then it will be passed by value, in other words the entire collection will be copied to the formal parameter when entering the procedure and back again when exiting the procedure. If the collection is large this can lead to unnecessary CPU and memory consumption.

The NOCOPY hint alleviates this problem because you can use it to instruct the runtime engine to try to pass OUT or IN OUT parameters by reference instead of by value. For example:

procedure get_customer_orders(
    p_customer_id in number,
    p_orders out nocopy orders_coll
);

In the absence of the NOCOPY hint the entire orders collection would have been copied into the theorders variable upon exit from the procedure. Instead the collection is now passed by reference.

Keep in mind, however, that there is a downside to using NOCOPY. When you pass parameters to a procedure by reference then any modifications you perform on the parameters inside the procedure is done on the same memory location as the actual parameter, so the modifications are visible. In other words, there is no way to "undo" or "rollback" these modifications, even when an exception is raised midway. So if an exception is raised inside the procedure the value of the parameter is "undefined" and cannot be trusted.

Consider our get_customer_orders example. If the p_orders parameter was half-filled with orders when an exception was raised, then upon exit our theorders variable will also be half-filled because it points to the same memory location as the p_orders parameter. This downside is most problematic for IN OUT parameters because if an exception occurs midway then not only is the output garbage, but you've also made the input garbage.

To sum up, a NOCOPY hint can offer a small performance boost, but you must be careful and know how it affects program behavior, in particular exception handling.

Using Reusable SQL

Except for single run SQL (such as weekly reports or infrequently used procedures) you should attempt to use bind variables instead of literals in your PL/SQL code. Use of bind variables allows the code to be reused multiple times. The entire purpose of the shared pool is to allow reuse of SQL statements that have already been parsed.

You can have the DBA set the CURSOR_SHARING parameter to FORCED in Oracle8i or to FORCED or SIMILAR in Oracle9i. In Oracle9i the hint CURSOR_SHARING_EXACT can be used for specific code that shouldn't be shared. In Oracle8i there is no CURSOR_SHARING_EXACT parameter and all literals in SELECT statements will be changed to bind variables. In Oracle9i Oracle uses bind variable peaking for the first time a SQL is parsed to allow more optimal code paths to be selected.

Identify SQL using Comments

It can be very difficult to pull your PL/SQL procedure code out of the background code in an instance shared pool. You can place a comment in each SQL statement that identifies the SQL within the shared pool. An example of this is:

CURSOR get_latch IS
SELECT /* DBA_UTIL.get_latch */
	a.name,100.*b.sleeps/b.gets 
FROM
   	v$latchname a, v$latch b 
WHERE
   	a.latch# = b.latch# and b.sleeps > 0;

Now to find all SQL code in the shared pool from the DBA_UTILITIES package you can simply query the V$SQLAREA or V$SQLTEXT to find code entries with ‘%DBA_UTIL%' in the SQL_TEXT column.

Using Hints

There are many hints available to the developer for use in tuning SQL statements that are embedded in PL/SQL. You should first get the explain plan of your SQL and determine what changes can be done to make the code operate without using hints if possible. However, hints such as ORDERED, LEADING, INDEX, FULL, and the various AJ and SJ hints can tame a wild optimizer and give you optimal performance.

Hints are enclosed within comments to the SQL commands DELETE, SELECT or UPDATE or are designated by two dashes and a plus sign. To show the format the SELECT statement only will be used, but the format is identical for all three commands.

  SELECT		/*+ hint --or-- text */
	statement body
		-- or --
	SELECT		--+ hint --or-- text
	statement body

Where:

• /*, */ — These are the comment delimiters for multi-line comments
• -- — This is the comment delimiter for a single line comment (not usually used for hints)
• + — This tells Oracle a hint follows, it must come immediately after the /*
• hint — This is one of the allowed hints
• text — This is the comment text

As you can see, a dilemma with a stubborn index can be easily solved using FULL or NO_INDEX hints. You must know the application to be tuned. The DBA can provide guidance to developers but in all but the smallest development projects, it will be nearly impossible for a DBA to know everything about each application. It is clear that responsibility for application tuning rests solely on the developer's shoulders with help and guidance from the DBA.

Using Global Hints

While hints normally refer to table in the query it is possible to specify a hint for a table within a view through the use of what are known as GLOBAL HINTS. This is done using the global hint syntax. Any table hint can be transformed into a global hint.

The syntax is:

/*+ hint(view_name.table_in_view) */

For example:

 /*+ full(sales_totals_vw.s_customer)*/

If the view is an inline view, place an alias on it and then use the alias to reference the inline view in the global hint.

Don't Over-specify Variable Length

Because a VARCHAR2 can be up to 32k in PL/SQL it is easy to fall into the trap of thinking that you can always specify the length to be many times what you feel you need. An example is setting VARCHAR2 to 2000 when you only need 80 characters.

The problem with over-specifying variable size is that the PL/SQL engine believes you and reserves the memory size you ask for in each variable declaration. Now this may not be a problem with one or two over-specified variables but if this is a PL/SQL table containing a thousand records it can place considerable memory overhead on your system.

Proper Use of Data Types

Improper use of datatypes can result in implicit type conversions. Implicit type conversions can result in the statement not being able to use appropritate indexes thus forcing a full table scan. Using %TYPE and %ROWTYPE to capture the types from either the specific table or cursor alleviates this.

Proper IF Statements

When using nested IF statements always place the IF logic that will be exercised most often first. An example would be the IF exit, usually programmers will place the various nested IF constructs that actually perform code operations first, even if they are rarely exercised, by placing the exit IF first the processing associated with the other IF processing is avoided.

This is especially true of IF constructs contained within LOOP structures. Using the PROFILER_CONTROL procedure as an example look at Figure 1.

PROCEDURE profiler_control(
start_stop IN VARCHAR2, run_comm IN VARCHAR2, ret OUT BOOLEAN) AS
ret_code INTEGER;
BEGIN
 ret_code:=dbms_profiler.internal_version_check;
  IF ret_code !=0 THEN
   ret:=FALSE;
  ELSIF start_stop NOT IN ('START','STOP') THEN
   ret:=FALSE;
  ELSIF start_stop = 'START' THEN
   ret_code:=DBMS_PROFILER.START_PROFILER(run_comment1=>run_comm);
   IF ret_code=0 THEN
    ret:=TRUE;
   ELSE
    ret:=FALSE;
   END IF;
  ELSIF start_stop = 'STOP' THEN
   ret_code:=DBMS_PROFILER.FLUSH_DATA;
   ret_code:=DBMS_PROFILER.STOP_PROFILER;
   IF ret_code=0 THEN
    ret:=TRUE;
   ELSE
    ret:=FALSE;
   END IF; 
  END IF;
END profiler_control;

Figure 1: The PROFILER_CONTROL Package

Notice that most of time the procedure will exit without generating an action, however, the exit is at the bottom of the IF-ELSIF stack. By rearranging the code and using a BOOLEAN as the go-no go we get a simplified, more efficient program as shown in Figure 2.

PROCEDURE profiler_control(
start_stop IN BOOLEAN, run_comm IN VARCHAR2, ret OUT BOOLEAN) AS
ret_code INTEGER;
BEGIN
  IF start_stop IS NULL THEN
  Ret:=TRUE;
  ELSIF start_stop THEN 
   ret_code:=dbms_profiler.internal_version_check;
   IF ret_code !=0 THEN
    ret:=FALSE;
   END IF;
  ELSIF start_stop AND ret_code=0 THEN
   ret_code:=DBMS_PROFILER.START_PROFILER(run_comment1=>run_comm);
   IF ret_code=0 THEN
    ret:=TRUE;
   ELSE
    ret:=FALSE;
   END IF;
  ELSIF NOT start_stop THEN
   ret_code:=DBMS_PROFILER.FLUSH_DATA;
   ret_code:=DBMS_PROFILER.STOP_PROFILER;
   IF ret_code=0 THEN
    ret:=TRUE;
   ELSE
    ret:=FALSE;
   END IF; 
  END IF;
END profiler_control;

Figure 2: New and Improved PROFILER_CONTROL

We make the code more efficient and reduce the amount of CPU cycles for all other packages that call PROFILER_CONTROL. If the IF construct involved in contained within a LOOP structure the savings can be substantial.

Using Temporary Tables

If the amount of data to be processed or utilized from your PL/SQL procedure is too large to fit comfortably in a PL/SQL table, use a GLOBAL TEMPORARY table rather than a normal table. A GLOBAL TEMPORARY table has a persistent definition but data is not persistent and the global temporary table generates no redo or rollback information. For example if you are processing a large number of rows, the results of which are not needed when the current session has ended, you should create the table as a temporary table instead:

The "on commit preserve rows" clause tells the SQL engine that when a transaction is committed the table should not be cleared.

The global temporary table will be created in the users temporary tablespace when the procedure populates it with data and the DIRECT_IO_COUNT will be used to govern the IO throughput (this usually defaults to 64 blocks).

Use Native compilation in 9i

The native compilation option in Oracle9i allows you to convert internal stored packages and procedures into compiled shared C libraries on the host server. This native compilation can reduce code execution time by up to a factor of four. The packages and procedures are compiled as native C routines and linked into the Oracle code. This new option in Oracle9i is most effective for computational intensive PL/SQL and shouldn't be used for PL/SQL that does a great deal of SQL execution.

To speed up one or more procedures using this technique follow this procedure:
Update the supplied makefile and enter the appropriate paths and other values for your system. The path of this makefile is:

     $ORACLE_HOME/plsql/spnc_makefile.mk.          

Use the ALTER SYSTEM or alter session command, or update your initialization file, to set the parameter PLSQL_COMPILER_FLAGS to include the value NATIVE. The default setting includes the value INTERPRETED, and you must remove this keyword from the parameter value.

Compile one or more procedures, using one of these methods:

• Use the ALTER PROCEDURE or ALTER PACKAGE command to recompile the procedure or the entire package.

• Drop the procedure and create it again.

• Use CREATE OR REPLACE to recompile the procedure.

• Run one of the SQL*Plus scripts that sets up a set of Oracle-supplied packages.

• Create a database using a preconfigured initialization file with PLSQL_COMPILER_FLAGS=NATIVE.

• During database creation, the UTLIRP script is run to compile all the Oracle-supplied packages.

To be sure that the process worked, you can query the data dictionary to see that a procedure is compiled for native execution. To check whether an existing procedure is compiled for native execution or not, you can query the data dictionary views USER_STORED_SETTINGS, DBA_STORED_SETTINGS, and ALL_STORED_SETTINGS. For example, to check the status of the procedure MY_PROC, you could enter:

SELECT param_value FROM user_stored_settings WHERE
  param_name = 'PLSQL_COMPILER_FLAGS'
  and object_name = 'MY_PROC';

The PARAM_VALUE column has a value of NATIVE for procedures that are compiled for native execution, and INTERPRETED otherwise.
After the procedures are compiled and turned into shared libraries, they are automatically linked into the Oracle process. You do not need to restart the database, or move the shared libraries to a different location. You can call back and forth between stored procedures, whether they are all compiled in the default way (interpreted), all compiled for native execution, or a mixture of both.

Because the PLSQL_COMPILER_FLAGS setting is stored inside the library unit for each procedure, procedures compiled for native execution are compiled the same way when the procedure is recompiled automatically after being invalidated, such as when a table that it depends on is recreated.

You can control the behavior of PL/SQL native compilation through the ALTER SYSTEM or alter session commands or by setting or changing these parameters in the initialization file:

• PLSQL_COMPILER_FLAGS

• PLSQL_NATIVE_LIBRARY_DIR (cannot be set by alter session for security reasons)

• PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT

• PLSQL_NATIVE_MAKE_UTILITY

• PLSQL_NATIVE_MAKE_FILE_NAME

An example of compiling a PL/SQL Procedure for Native Execution is shown in Figure 3.

connect scott/tiger;
set serveroutput on;
alter session set plsql_native_library_dir='/home/orauser/lib';
alter session set plsql_native_make_utility='gmake';
alter session set plsql_native_make_file_name='/home/orauser/spnc_makefile.mk';
alter session set plsql_compiler_flags='NATIVE';
create or replace procedure hello_native_compilation
as
begin
  dbms_output.put_line('Hello world');
  select sysdate from dual;
end;

Figure 3: Example use of Native Compilation

As the procedure is compiled, you see the various compilation and link commands being executed. The procedure is immediately available to call, and runs as a shared library directly within the Oracle process.

Limitations of Native Compilation

When a package specification is compiled for native execution, the corresponding body should be compiled using the same settings.
The Oracle provided debugging tools for PL/SQL do not handle procedures compiled for native execution.

When many procedures and packages (typically, over 5000) are compiled for native execution, having a large number of shared objects in a single directory might affect system performance. In this case, you can have the DBA set the initialization parameter PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT in the initialization file before creating the database or compiling the PL/SQL packages or procedures. Set this parameter to a value that makes sense for your environment and operating system, Oracle suggests 1000 but this seems like overkill to me. Once the parameter is set and the DB restarted, create subdirectories underneath the directory specified in the PLSQL_NATIVE_LIBRARY_DIR parameter. The subdirectories must be named d0, d1, d2 ... d999, up to the value specified for the subdirectory count. When the procedures are compiled for native execution, the DLLs will be automatically distributed among these subdirectories by the PL/SQL compiler.

--

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.