A Guide to High-Performance PL/SQL
Everything can be sound with your database, but, you haven't optimized your SQL, it will still run slow. Learn how to mitigate this annoyance!
Join the DZone community and get the full member experience.Join For Free
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
Published at DZone with permission of Steven Feuerstein, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.