Beware of PL/SQL Performance Problems!!

 Author: Mike Mortensen

Page: 1 2

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.

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. Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Expert Tips on Drop Temporary Tablespace Hangs!!
   Secrets of Resources with AWR Reports, Great!!
   Secrets of tracking historical changes made to Oracle Tables!!
   Oracle DBMS: Fundamental Security Precautions