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

With tools for gaining as many as 30 times the execution speed, this handbook educates database administrators on how to rewrite SQL into PL/SQL and how to use advanced Oracle bulk array processing techniques to achieve high performance. Ready-to-use tips for saving hardware costs by running applications at peak efficiencies are provided for senior Oracle DBAs and developers, and powerful PL/SQL techniques are outlined.

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

About the Author :
Dr. Timothy Hall is an Oracle Certified Professional DBA who has published more than 200 Oracle-related articles on www.oracle-base.com.