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.
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.
