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 II: 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
  • Analysis Without Diagnostic Pack

Part III: 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
  • Concurrent Statistics Gathering
  • Working with Pending Object Statistics
  • Working with Global Temporary Tables
  • 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 IV: Optimization

Chapter 11. SQL Optimization Techniques

  • Altering the Access Structures
  • Altering the SQL Statement
  • Hints
  • Altering the Execution Environment
  • SQL Plan Management
  • SQL Profiles
  • SQL Patches

Chapter 12. Parsing

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

Chapter 13. Optimizing Data Accesses

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

Chapter 14. Optimizing Joins

  • Definitions
  • Nested Loops Joins
  • Merge Joins
  • Hash Joins
  • Outer Joins
  • Choosing Join Method
  • Star Queries

Chapter 15. Parallel Execution

  • How It Works
  • When to Use It
  • Pitfalls and Fallacies

Chapter 16. Precomputing and Caching Data

  • Materialized Views
  • Result Caches

Chapter 17. Optimizing Network Round Trips

  • Row Prefetching
  • Array Interface

Chapter 18. Optimizing the Physical Design

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