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:

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:

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:

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:

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:

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.

-- —
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

Hint

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) ASret_code INTEGER;BEGINret_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 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:

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.

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.

Note:This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.

Verify
experience!Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.

Errata? Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just e-mail:
and include the URL for the page.