Oracle 10g : Top 10 Oracle PL/SQL Performance Tips


1. Avoid NOT EQUAL Operators ‘<>’ and ‘!=”

When these operators are used, the indexes on columns referenced cannot be used. If you need to use the ‘<>’ operator, you can alternatively use the OR clause to distinguish the <> conditions.

2. Avoid ‘IS NULL’ and ‘IS NOT NULL’

The value NULL is undefined, therefore an index on the referenced column will not be used. Creating defaults for NULL values as part of table creation can help with this issue. (i.e.. column VARCHAR2(1) NOT NULL DEFAULT ‘N”)

3. Avoid FUNCTIONS in SQL Where Clause

The optimizer will not use an index when a function is used on an indexed column.

4. Comparing datatypes that are not similar

The Oracle database converts a VARCHAR2 column to a numeric when comparing a string to a number. When this happens, the referenced index is not used. (i.e account_number = 100100).

5. Use the EXISTS function when possible

Utilize the EXISTS function rather than the IN function. The EXIST function looks for a single row match. The IN function has to return all qualifying rows.

6. Use BIND variables

With the use of bind variables, SQL statements can be reused in memory rather than having to be re-parsed with each call.

7. Where are you referencing form fields?

Avoid referencing form fields within the body of a trigger or program unit. Developer 2000 Forms has a PL/SQL engine independent of the database. Occasionally, the engine has to pass SQL off to the database for parsing. To minimize the communication between the client and server, pass form fields via an argument list.

8. Utilize the FORALL statement rather than a FOR LOOP.

When inserting/updating/deleting a large number of rows, you can do the update collectively, rather then one record at a time. This is quicker because only one context switch has to occur between PL/SQL and SQL.

9. Utilize the BULK COLLECT statement

BULK COLLECT works similar to a FORALL statement. It will fetch all rows from the database rather than one row at a time.

10. Using NOCOPY

The PL/SQL engine will pass the parameter by reference rather than by value. This means the values do not have to be copied back and forth. The greatest advantage is seen when passing large records or collections. Can only be used with OUT or IN OUT parameters.

References:

Richard J. Niemiec, Oracle Performance Tuning, Osborne/McGraw-Hill Book Co., 1999

Scott Urman, Oracle8i Advanced PL/SQL Programming, Osborne/McGraw-Hill Book Co., 2000

About these ads

3 responses to “Oracle 10g : Top 10 Oracle PL/SQL Performance Tips

  1. Pingback: Top Referred Articles on KnowOracle | Shivmohan Purohit's Oracle Applications Blog

  2. Its nice for PL/SQL developer add more advance would be better… thanks

  3. Its nice

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s