Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming

Description

Oracle experts know that PL/SQL tuning makes a huge difference in execution speed. As one of the world’s most popular and respected experts, Dr. Tim Hall shares his secrets for tuning Oracle PL/SQL.

This indispensable book shows how to hypercharge Oracle applications gaining as much as 30x improvement in execution speed using under-documented code tricks.  Packed with working examples, learn how to re-write SQL into PL./SQL and how to use advanced Oracle bulk array processing techniques to achieve super high performance. You can save your company millions of dollars in hardware costs by making your applications run at peak efficiency.

Targeted at the Senior Oracle DBA and developer, this advanced book illustrates powerful techniques that can make PL/SQL run faster than ever before. This book is not for beginners and should only be purchased by seasoned Oracle professionals who must turbocharge their applications. Your time savings from a single script is worth the price of this great book.

 

Table of Contents

Chapter 1 - Introducing PL/SQL

Introduction
What is PL/SQL and Why Should I use It?
PL/SQL Architecture
Overview of PL/SQL Elements
Blocks
Variables and Constants
Using SQL in PL/SQL
Branching and Conditional Control
Looping Statements
Goto
Procedures, Functions and Packages
Records
Object Types
Collections
Associative Arrays (Index-By Tables)
Nested Table Collections
Varrays
Collection Methods
Triggers
Error Handling
My Ideal Environment
Conclusion


Chapter 2 - Writing Efficient PL/SQL

Introduction
Bind Variables
Using Bind Variables
The cursor_sharing Parameter
Dynamic Binds using Contexts
SQL Injection
Bulk Binds
Using Rowids when Updating
Short-circuit Evaluations and Ordering Logic
Implicit vs. Explicit Cursors
Declarations, Blocks, Functions and Procedures in Loops
Duplication of Built-in String Functions
Minimize Datatype Conversions
The Trigger Compilation Myth
Efficient Function Calls
Using the NOCOPY Hint
Using PLS_INTEGER and BINARY_INTEGER Types
Using BINARY_FLOAT and BINARY_DOUBLE Types
Native Compilation of PL/SQL
Decoupling (cheating) for Performance
Conclusion


Chapter3 - Arrays and Bulk Binds

Introduction
Populating Collections Using Bulk Operations
Bulk collect
Bulk Collect from an Explicit Cursor
Chunking Bulk Collections Using the LIMIT Clause
Manually Limiting Bulk Collection Volumes
Bulk Collection of DML Results
FORALL
Bulk INSERT Operations
Bulk UPDATE Operations
Bulk DELETE Operations
Sparse Collections
Host Arrays in Bulk Operations
BULK_ROWCOUNT
Handling Exceptions in Bulk Operations
Unhandled Exceptions
Handled Exceptions
Bulk Operations that Complete
Dynamic SQL and Bulk Operations
Conclusion

Chapter 4 - Caching Session Data

Introduction
Using Arrays for Lookup Tables
Using Package Variables to Store Global Data
Using Contexts to Store Global Data
Conclusion


Chapter 5 - Memory Management

Introduction
Bind Variables and the Shared Pool
The NOCOPY Hint and Memory Usage
Bigger is Better for VARCHAR2 Variables
Using Packages Correctly
Pinning Packages in the Shared Pool
Conclusion


Chapter 6 - Cursor Variables and REF CURSOR Types
 

Introduction
Defining Cursor Variables
Cursor Variables as Parameters
Cursor Attributes and Cursor Variable Usage
Host Variables as Cursor Variables
Dynamic SQL and Variant Resultsets
Restrictions When Using Cursor Variables
Cursor Expressions
Conclusion


Chapter 7 - Table Functions and Pipelining

Introduction
Pipelining Table Functions
Parallel Enabled Table Functions
Creating Transformation Pipelines
Deterministic
Miscellaneous Information
Conclusion


Chapter 8 - Monitoring and Profiling PL/SQL

Introduction
Producing Performance Baselines
Monitoring Specific Code
Code Instrumentation (application tracing)
The DSP Package
dbms_application_info
dbms_session
dbms_system
dbms_profiler
dbms_trace
SQL trace, trcsess and tkprof
Generating SQL Trace Files
trcsess
tkprof
Trace Example
Execution Plans
plan_table
autotrace
Explain Plan
utlxpls.sql
dbms_xplan
Identifying the Impact of Code at the Database Level
Dynamic Performance Views (V$)
sessions.sql
top_sessions.sql
top_sql.sql
longops.sql
session_waits.sql
session_events_by_sid.sql and system_events.sql
session_stats.sql and system_stats.sql
session_io.sql
open_cursors_by_sid.sql
locked_objects.sql
STATSPACK
Automatic Workload Repository (AWR)
ADDM
Using Oracle Enterprise Manager
Conclusion
Book Conclusion