Advanced PL/SQL Code Tuning

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;Elapsed: 00:00:00.01
Execution plan
---------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (FULL) OF 'DUAL'

Statistics
---------------------------------------------------
0 recursive calls0 db block gets3 consistent gets
0 physical reads0 redo size380 bytes sent via SQL*Net to client499 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

SQL>
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;
insert into xdual varchar2(9);
analyze table xdual compute statistics for table for all indexes for all indexed columns;
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;Elapsed: 00:00:00.01
Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1) 1
0 INDEX (FULL SCAN) OF 'SYS_IOT_TOP_33973' (UNIQUE) (Cost=1Card=1)
Statistics
----------------------------------------------------------
0 recursive calls0 db block gets1 consistent gets0 physical reads0 redo size380 bytes sent via SQL*Net to client499 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed

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);theorders orders_coll;get_customer_orders(124, theorders);

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 ISSELECT /* 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:
a. /*, */ — These are the comment delimiters for multi-line comments b. -- — This is the comment delimiter for a single line comment (not usually used for hints) c. + — This tells Oracle a hint follows, it must come immediately after the /* d. hint — This is one of the allowed hints e. text — This is the comment text

Hint and Meaning

+
Must be immediately after comment indicator, tells Oracle this is a list of hints.

ALL_ROWS
Use the cost based approach for best throughput.

CHOOSE
Default, if statistics are available will use cost, if not, rule.

FIRST_ROWS
Use the cost based approach for best response time.

RULE
Use rules based approach; this cancels any other hints specified for this statement.

Access Method Hints:

CLUSTER(table)
This tells Oracle to do a cluster scan to access the table.

FULL(table)
This tells the optimizer to do a full scan of the specified table.

HASH(table)
Tells Oracle to explicitly choose the hash access method for the table.

HASH_AJ(table)
Transforms a NOT IN subquery to a hash anti-join.

ROWID(table)
Forces a rowid scan of the specified table.

INDEX(table [index])
Forces an index scan of the specified table using the specified index(s). If a list of indexes is specified, the optimizer chooses the one with the lowest cost. If no index is specified then the optimizer chooses the available index for the table with the lowest cost.

INDEX_ASC (table [index])
Same as INDEX only performs an ascending search of the index chosen, this is functionally identical to the INDEX statement.

INDEX_DESC(table [index])
Same as INDEX except performs a descending search. If more than one table is accessed, this is ignored.
INDEX_COMBINE(table index)Combines the bitmapped indexes on the table if the cost shows that to do so would give better performance.

INDEX_FFS(table index)
Perform a fast full index scan rather than a table scan.

MERGE_AJ (table)
Transforms a NOT IN subquery into a merge anti-join.

AND_EQUAL(table index index [index index index])
This hint causes a merge on several single column indexes. Two must be specified, five can be.

NL_AJ
Transforms a NOT IN subquery into a NL anti-join (nested loop).

HASH_SJ(t1, t2)
Inserted into the EXISTS subquery; This converts the subquery into a special type of hash join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.

MERGE_SJ (t1, t2)
Inserted into the EXISTS subquery; This converts the subquery into a special type of merge join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.

NL_SJ
Inserted into the EXISTS subquery; This converts the subquery into a special type of nested loop join between t1 and t2 that preserves the semantics of the subquery. That is, even if there is more than one matching row in t2 for a row in t1, the row in t1 is returned only once.

Hints for join orders and transformations:

ORDERED
This hint forces tables to be joined in the order specified. If you know table X has fewer rows, then ordering it first may speed execution in a join.

STAR
Forces the largest table to be joined last using a nested loops join on the index.

STAR_TRANSFORMATION
Makes the optimizer use the best plan in which a start transformation is used.

FACT(table)
When performing a star transformation use the specified table as a fact table.

NO_FACT(table)
When performing a star transformation do not use the specified table as a fact table.

PUSH_SUBQ
This causes nonmerged subqueries to be evaluated at the earliest possible point in the execution plan.

REWRITE(mview)
If possible forces the query to use the specified materialized view, if no materialized view is specified, the system chooses what it calculates is the appropriate view.

NOREWRITE
Turns off query rewrite for the statement, use it for when data returned must be concurrent and can’t come from a materialized view.

USE_CONCAT
Forces combined OR conditions and IN processing in the WHERE clause to be transformed into a compound query using the UNION ALL set operator.

NO_MERGE (table)
This causes Oracle to join each specified table with another row source without a sort-merge join.

NO_EXPAND
Prevents OR and IN processing expansion.

Hints for Join Operations:

USE_HASH (table)
This causes Oracle to join each specified table with another row source with a hash join.

USE_NL(table)
This operation forces a nested loop using the specified table as the controlling table.

USE_MERGE(table,[table,…])
This operation forces a sort-merge-join operation of the specified tables.

DRIVING_SITE
The hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization.

LEADING(table)
The hint causes Oracle to use the specified table as the first table in the join order.

Hints for Parallel Operations:

[NO]APPEND
This specifies that data is to be or not to be appended to the end of a file rather than into existing free space. Use only with INSERT commands.

NOPARALLEL (table)
This specifies the operation is not to be done in parallel.

PARALLEL(table, instances)
This specifies the operation is to be done in parallel.

PARALLEL_INDEX
Allows parallelization of a fast full index scan on any index.

Other Hints:

CACHE
Specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list when the table is full table scanned.

NOCACHE
Specifies that the blocks retrieved for the table in the hint are placed at the least recently used end of the LRU list when the table is full table scanned.

[NO]APPEND
For insert operations will append (or not append) data at the HWM of table.

UNNEST
Turns on the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to FALSE.

NO_UNNEST
Turns off the UNNEST_SUBQUERY option for statement if UNNEST_SUBQUERY parameter is set to TRUE.

PUSH_PRED
Pushes the join predicate into the view.
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) ASret_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 TablesIf 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:
create global temporary table results_temp (...)on commit preserve rows;
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 9iThe 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_compilationasbegin 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 CompilationWhen 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.