Part 1 – Foundations

1. Performance Problems

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

2. Key Concepts

  • Selectivity and Cardinality
  • What Is a Cursor?
  • Life Cycle of a Cursor
  • How Parsing Works
  • Reading and Writing Blocks
  • Instrumentation

Part 2 – Identification

3. Analysis of Reproducible Problems

  • Tracing Database Calls
  • Profiling PL/SQL Code

4. Real-Time Analysis of Irreproducible Problems

  • Analysis Roadmap
  • Dynamic Performance Views
  • Analysis With Diagnostic and Tuning Pack
  • Analysis Without Diagnostic Pack

5. Postmortem Analysis of Irreproducible Problems

  • Repositories
  • Automatic Workload Repository
  • Statspack
  • Analysis With Diagnostic and Tuning Pack
  • Analysis Without Diagnostic Pack

Part 3 – Query Optimizer

6. Introducing the Query Optimizer

  • Fundamentals
  • Architecture
  • Query Transformations

7. System Statistics

  • The dbms_stats Package
  • What System Statistics Are Available?
  • Gathering System Statistics
  • Restoring System Statistics
  • Working with a Backup Table
  • Logging of Management Operations
  • Impact on the Query Optimizer

8. Object Statistics

  • The dbms_stats Package
  • What Object Statistics Are Available?
  • Gathering Object Statistics
  • Configuring the dbms_stats Package
  • Working with Global Temporary Tables
  • Working with Pending Object Statistics
  • Working with Partitioned Objects
  • Scheduling Object Statistics Gathering
  • Restoring Object Statistics
  • Locking Object Statistics
  • Comparing Object Statistics
  • Deleting Object Statistics
  • Exporting, Importing, Getting, and Setting Object Statistics
  • Logging of Management Operations
  • Strategies for Keeping Object Statistics Up-to-Date

9. Configuring the Query Optimizer

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

10. Execution Plans

  • Obtaining Execution Plans
  • The dbms_xplan Package
  • Interpreting Execution Plans
  • Recognizing Inefficient Execution Plans

Part 4 – Optimization

11. SQL Tuning Techniques

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

12. Parsing

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

13. Optimizing Data Access

  • Identifying Suboptimal Access Paths
  • SQL Statements with Weak Selectivity
  • SQL Statements with Strong Selectivity

14. Optimizing Joins

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

15. Beyond Data Access and Join Optimization

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

16. Optimizing the Physical Design

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

Appendix A – Bibliography