Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Tuning PL/SQL

Oracle Database Tips by Donald Burleson

Tune SQL First

The entire reason for PL/SQL is to encapsulate SQL into a procedural context. The procedural context allows set processing to be pieced out into the procedural logic of a PL/SQL program. Therefore it is of primary importance to tune all encapsulated SQL statements to perform at an optimal level. There are many techniques that can be used to tune SQL statements, let's look at a few of the heavy hitters.


When using SQL of the structure:

select id
from s_warehouse
where id in
     (select warehouse_id from s_inventory);

Can usually be improved by converting it into a statement that uses EXISTS:

select id
from s_warehouse W
where EXISTS
     (select 'X' from s_inventory S
       where s.warehouse_id =;

the reason EXISTS usually improves performance is that the IN clause will need to process a complete result set while the EXISTS only proceeds until the first positive result is reached.

In a similar technique the NOT IN clause form a statement similar to:

  from  s_product p
 where not in
                 (select product_id
                    from s_inventory);

Can be altered to use NOT EXISTS:

  from s_product p
 where not exists
                (select 'X'
                   from s_inventory i
                  where i.product_id =
   and name like 'W%';

Notice how the use of an index is forced by the use of a select against the name column that cannot be satisfied (based on preknowledge of distribution in the name column) if you don't have knowledge about the distribution in an index (or want to use a better technique) just use the INDEX hint to specify the index to be used:

select /*+ INDEX (p, name_s_product_lu) */,
  from s_product p
 where not exists
                (select 'X'
                   from s_inventory i
                  where i.product_id =; 


Of course the proper use, or non-use of indexes is also critical to proper SQL performance. If a SELECT processes more than 5% of a tables rows (up to 20% on 9i) then a full table scan may be more efficient. The best way to force index usage is via the INDEX hint as shown above, there is also the NO_INDEX and FULL hints which should be used to force specific use or non-use of indexes.


One sign of a beginning or inexperienced PL/SQL and SQL programmer is over use of the DISTINCT clause. There should be n-1 joins in a SQL statement where n is the number of tables involved. If there are not n-1 joins (involving n-1 tables) then a Cartesian join will result that will produce duplicate rows. A beginning or inexperienced  developer will get to the point where a result set is returned, see duplicate rows and insert a DISTINCT to reduce this to a final result. Of course the proper technique is to add the appropriate join condition. A DISTINCT will always generate a SORT that is a performance hit.

Learn More about Oracle Tuning:


This is an excerpt from the top selling book "Oracle PL/SQL Tuning" by Dr. Tim Hall.

You can buy it direct from the publisher for 30%-off and get instant access to the online  code depot of working PL/SQL examples.




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

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.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.