Beware of PL/SQL Performance Problems!!

Author: Mike Mortensen

In this article I will give some useful tips that will help you avoid PL/SQL performance problems. Most of work in PL/SQL programs is done by SQL statements and hence slow SQL statements are the main reason for slow execution. The loops and inside code must be optimized to gain performance boost. Badly written subprograms can also harm performance of your application.

Indexing:

Use appropriate indexes depending on the sizes of various tables in a query, the distribution of data in each query, and the columns used in the WHERE clauses.

Statistics:

There should be up-to-date statistics on all the tables. You can use the subprograms in the DBMS_STATS package for this purpose.

Function-based Index:

You can create a function-based index on the table in the query if a function is called within a SQL query. The CREATE INDEX statement might take a while but it can make your queries much faster.

Filtering:

The query cannot use regular indexes on that column and the function might be called for every row in a potentially very large table if a column is passed to a function within an SQL query. You can nest one query inside another to do the filtering and sorting in multiple stages. Instead of calling a PL/SQL function in the inner WHERE clause you can filter the result set to a small set of rows in the inner query, and call the function in the outer query.

Inefficient Approach	Efficient Approach
Calls function for every row	Only calls function once for each distinct value.
BEGIN FOR item IN (SELECT DISTINCT(SQRT(department_id)) col_alias FROM employees) LOOP DBMS_OUTPUT.PUT_LINE(item.col_alias); END LOOP ;	FOR item IN ( SELECT SQRT(department_id) col_alias FROM ( SELECT DISTINCT department_id FROM employees) ) LOOP DBMS_OUTPUT.PUT_LINE(item.col_alias); END LOOP ; END; /

Analysis:

You can analyze the execution plans and performance of the SQL statements using EXPLAIN PLAN statement and SQL Trace facility with TKPROF utility.

Query Hints:

Rewrite the SQL statements if necessary. Query hints can avoid problems such as unnecessary full-table scans.

Enhanced query for exact results:

You can enhance the original query to give you exactly the results you want by using UNION , INTERSECT , MINUS , and CONNECT BY clauses when you have to loop through a result set more than once or issue other queries as you loop through a result set.

Built-In String Functions:

PL/SQL provides many highly optimized string functions such as REPLACE , TRANSLATE , SUBSTR , INSTR , RPAD , and LTRIM that are more efficient than regular PL/SQL.

Regular Expressions:

Regular Expressions are an efficient tool for string matching. You can use PL/SQL string functions to search for regular expressions. You can search for regular expressions using the SQL operator REGEXP_LIKE . You can test or manipulate strings using the built-in functions REGEXP_INSTR , REGEXP_REPLACE , and REGEXP_SUBSTR .

Oracle Database regular expression features use characters like ‘.’, ‘*’, ‘^’, and ‘$’. There are also extensions such as ‘[:lower:]‘ to match a lowercase letter, instead of ‘[a-z]‘ which does not match lowercase accented letters.

FORALL Statement:

You can use FORALL statement as a way to replace loops of INSERT , UPDATE , and DELETE statements.

BULK COLLECT Clause:

You can bring the entire result set into memory in a single operation by using BULK COLLECT clause of the SELECT INTO statement while using looping through the result set of a query.

NOCOPY keyword:

PL/SQL adds some performance overhead to ensure correct action in case of exceptions if you use OUT or IN OUT parameters. If your program does not depend on OUT parameters then you can add the NOCOPY keyword to the parameter declarations so the parameters are declared OUT NOCOPY or IN OUT NOCOPY . This technique can give significant speedup if you are passing back large amounts of data in OUT parameters such as collections, big VARCHAR2 values, or LOBs.

SELF IN OUT NOCOPY:

You can explicitly declare the first parameter of the member method as SELF IN OUT NOCOPY instead of SELF IN OUT . This will avoid the overhead when the methods modify attributes of the object type and all the attributes are copied when the method ends.

Short-Circuit Evaluation:

PL/SQL uses functionality called short-circuit evaluation which stops evaluating a logical expression as soon as the result can be determined. When evaluating multiple conditions separated by AND or OR , put the least expensive ones first. For example, check the values of PL/SQL variables before testing function return values, because PL/SQL might be able to skip calling the functions.

Use Packages:

When you call a packaged subprogram for the first time, the whole package is loaded into the shared memory pool. Subsequent calls to related subprograms in the package require no disk I/O, and your code executes faster. If the package is aged out of memory, it must be reloaded if you reference it again. You can improve performance by sizing the shared memory pool correctly. Make sure it is large enough to hold all frequently used packages but not so large that memory is wasted.

Pinning the Packages:

You can pin frequently accessed packages in the shared memory pool using the supplied package DBMS_SHARED_POOL . When a package is pinned, it is not aged out by the least recently used (LRU) algorithm and remains in memory no matter how full the pool gets or how frequently you access the package.

Minimize Implicit Data Type Conversions:

PL/SQL converts between different data types automatically at run time. Assigning a PLS_INTEGER variable to NUMBER variable results in a conversion as their internal representations are different.

The data types must be chosen carefully to minimize implicit conversions. Use literals of the appropriate types. Character literals must be used in character expressions and decimal numbers in number expressions. The conversion from INTEGER to PLS_INTEGER data type might improve performance because of the use of more efficient hardware arithmetic.

Integer Arithmetic:

Use the data type PLS_INTEGER if the value of a local integer variable might be NULL or if the variable needs overflow checking. If the value of the variable will never be NULL , and the variable does not need overflow checking then use the data type SIMPLE_INTEGER .

Avoid constrained subtypes such as INTEGER , NATURAL , NATURALN , POSITIVE , POSITIVEN , and SIGNTYPE in performance-critical code. Variables of these types require extra checking at run time each time they are used in a calculation.

Floating-Point Arithmetic:

The BINARY_FLOAT and BINARY_DOUBLE types can use native hardware arithmetic instructions and are more efficient for applications such as scientific processing. They also require less space in the database.

If the value of the variable will never be NULL then use the subtype SIMPLE_FLOAT or BINARY_FLOAT instead of the base type SIMPLE_DOUBLE or BINARY_DOUBLE . Without the overhead of checking for nullness, SIMPLE_FLOAT and SIMPLE_DOUBLE provide significantly better performance than BINARY_FLOAT and BINARY_DOUBLE when PLSQL_CODE_TYPE=’NATIVE’ , because arithmetic operations on SIMPLE_FLOAT and SIMPLE_DOUBLE values are done directly in the hardware. When PLSQL_CODE_TYPE=’INTERPRETED’ , the performance improvement is smaller. However these types are less suitable for financial code where accuracy is critical.

Memory Conservation:

You can conserve memory by declaring VARCHAR2 variables with large sizes such as 32000 rather than estimating just a little on the high side 256 or 1000. PL/SQL has an optimization that makes it easy to avoid overflow problems and still conserve memory. PL/SQL waits until you assign the variable and then only allocates as much storage as needed.

Follow Compiler Warnings:

The PL/SQL compiler issues warnings. These warnings are about the issues that might lead to poor performance. Follow such warnings and change the code accordingly to be more efficient.