A Guide to High-Performance PL/SQL

PL/SQL is a key enabling technology in Oracle Database. You should make sure that you are aware of and take advantage of appropriate key features of the PL/SQL language focused on performance.

I offer a short list of those features below, along with links to related resources. Most of this is also capture in this slide deck:

Key Performance Features

All of these are covered in the slide deck above; the links will take you to the documentation on these features. Click here for the overall section in the doc on performance and optimization.

• Bulk processing with FORALL and BULK COLLECT
• The function result cache
• Optimized execution of user-defined functions from SQL
• The NOCOPY Hint
• Automatic compiler optimization

But Don't Forget About SQL Optimization

Chances are you could take full advantage of all the features listed above and more in PL/SQL, and still end up with a slow application. That's because at the heart of every application built on Oracle Database are your SQL statements, and if those are not optimized, you can pretty much forget everything else.

Here are some links you might find helpful in this regard:

(list under construction - please offer your suggestions in Comments)

• Optimizing SQL Performance - an AskTOM Office Hours session by Chris Saxon
• Analytic SQL for Developers - an Oracle Dev Gym class by Connor McDonald