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
Table of Contents
