Oracle PL/SQL Tuning

Oracle PL/SQL is a very powerful language used extensively in the real world. This book will help you extract the best performance out of your PL/SQL code using tips and techniques. The examples are real and the performance gains are real too. The techniques like using bind variables, use of caching of result cache, reduce the redundant functions calls, etc will help you optimize your code and take your PL/SQL skill to the next level.



Chapter 1: Tune Functions

Reduce Function Calls

Use Result Cache Function

Tune Function Queries

Create Function Index

Use Oracle Supplied Function

Chapter 2: Tune Procedures

Use Bind Variables

Dynamic Query with Bind Variables

Deleting the Data

Delete Duplicate Data

Delete using ALTER TABLE TRUNCATE PARTITION

Cache Sequence

Bulk Insert using Direct Path

Table and Row Locking and Performance



Chapter 3 – Tune Loops

Commit in Loop

Use BULK COLLECT and FORALL

Don’t Forget EXIT Condition

Avoid Loop When Possible

Chapter 4: Tune IF Statements

Short Circuit Evaluation

CASE Statement

Chapter 5: Tune Triggers

Avoid Triggers When Possible

Chapter 6: Other Miscellaneous Things

Run in parallel using DBMS_PARALLEL_EXECUTE package

DBMS_PROFILER package

PL/SQL Native Compilation

Oracle PL/SQL Manual



This book is a culmination of author's 20 years of experience of using Oracle SQL and PL/SQL.