Table of Contents

Part 1 – Foundations

1. Performance Problems

  • Do We Need To Plan Performance?
  • Do We Have Performance Problems?
  • How to Approach Performance Problems?

2. Key Concepts

  • Selectivity and Cardinality
  • Life Cycle of a Cursor
  • How Parsing Works
  • Reading and Writing Blocks

Part 2 – Identification

3. Identifying Performance Problems

  • Divide and Conquer
  • Analysis Roadmap
  • Instrumentation vs. Profiling Analysis
  • Instrumentation
  • Profiling Application Code
  • Tracing Database Calls
  • Profiling PL/SQL Code

Part 3 – Query Optimizer

4. System and Object Statistics

  • Overview of the Package dbms_stats
  • System Statistics
  • Object Statistics
  • Common Services

5. Configuring the Query Optimizer

  • To Configure or Not to Configure
  • Configuration Roadmap
  • Set the Right Parameter!

6. Execution Plans

  • Obtaining Execution Plans
  • Interpreting Execution Plans
  • Recognizing Inefficient Execution Plans

7. SQL Tuning Techniques

  • Altering the Access Structures
  • Altering the SQL Statement
  • Hints
  • Altering the Execution Environment
  • SQL Profiles
  • Stored Outlines
  • SQL Plan Baselines

Part 4 – Tuning

8. Parsing

  • Identifying Parsing Problems
  • Solving Parsing Problems
  • Working around Parsing Problems
  • Application Programming Interfaces

9. Optimizing Data Access

  • Identifying Suboptimal Access Paths
  • SQL Statements with Poor Selectivity
  • SQL Statements with Good Selectivity

10. Optimizing Joins

  • Definitions
  • Nested Loop Joins
  • Merge Joins
  • Hash Joins
  • Outer Joins
  • Choosing Join Method
  • Partition-Wise Joins
  • Transformations

11. Beyond Data Access and Join Optimization

  • Materialized View
  • Result Caching
  • Parallel Processing
  • Direct-path Insert
  • Row Prefetching
  • Array Interface

12. Optimizing the Physical Design

  • Optimal Column Order
  • Optimal Data Type
  • Row Migration and Row Chaining
  • Block Contention
  • Data Compression

Part 5 – Appendixes

A. Downloadable Files
B. Bibliography