Troubleshooting Oracle Performance

Troubleshooting Oracle Performance

by Christian Antognini
Released June 2008
Publisher(s): Apress
ISBN: 9781590599174

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

What do you do when your database application isn't running fast enough? You troubleshoot, of course. Finding the slow part of an application is often the easy part of the battle. It's finding a solution that's difficult. Troubleshooting Oracle Performance helps by providing a systematic approach to addressing the underlying causes of poor database application performance.

  • Written for developers by an application developer who has learned by doing

  • Gives a systematic approach to solving database application performance problems

  • Helps you plan for performance as you would for any other application requirement

What you'll learn

  • See how to treat and plan for performance as a basic application requirement

  • Identify performance problems using a systematic and repeatable approach

  • Configure your query optimizer to meet your application performance goals

  • Optimize table accesses, joins, and physical table layout

  • Read and recognize inefficient SQL execution plans

  • Reduce inefficiencies from too much procedural code

Who this book is for

For application developers and database administrators involved in troubleshooting performance problems of Oracle-based applications.

Table of contents

  1. Title Page
  2. Dedication
  3. Contents at a Glance
  4. Contents
  5. Forewords
  6. About the Author
  7. About the Technical Reviewers
  8. Acknowledgments
  9. Introduction
    1. Structure of This Book
    2. Intended Audience
    3. Which Versions Are Covered?
    4. Online Resources
  10. About the OakTable Network
    1. The Members
    2. The Politics
    3. The Books
  11. PART 1: Foundations
    1. CHAPTER 1: Performance Problems
      1. Do You Need to Plan Performance?
      2. Do You Have Performance Problems?
      3. How Do You Approach Performance Problems?
      4. On to Chapter 2
    2. CHAPTER 2: Key Concepts
      1. Selectivity and Cardinality
      2. Life Cycle of a Cursor
      3. How Parsing Works
      4. Reading and Writing Blocks
      5. On to Chapter 3
  12. PART 2: Identification
    1. CHAPTER 3: Identifying Performance Problems
      1. Divide and Conquer
      2. Analysis Road Map
      3. Instrumentation vs. Profiling Analysis
      4. Instrumentation
      5. Profiling Application Code
      6. Tracing Database Calls
      7. Profiling PL/SQL Code
      8. On to Chapter 4
  13. PART 3: Query Optimizer
    1. CHAPTER 4: System and Object Statistics
      1. Overview of the Package dbms_stats
      2. System Statistics
      3. Object Statistics
      4. Common Services
      5. On to Chapter 5
    2. CHAPTER 5: Configuring the Query Optimizer
      1. To Configure or Not to Configure . . .
      2. Configuration Road Map
      3. Set the Right Parameter!
      4. On to Chapter 6
    3. CHAPTER 6: Execution Plans
      1. Obtaining Execution Plans
      2. Interpreting Execution Plans
      3. Recognizing Inefficient Execution Plans
      4. On to Chapter 7
    4. CHAPTER 7: SQL Tuning Techniques
      1. Altering the Access Structures
      2. Altering the SQL Statement
      3. Hints
      4. Altering the Execution Environment
      5. SQL Profiles
      6. Stored Outlines
      7. SQL Plan Baselines
      8. On to Chapter 8
  14. PART 4: Optimization
    1. CHAPTER 8: Parsing
      1. Identifying Parsing Problems
      2. Solving Parsing Problems
      3. Working Around Parsing Problems
      4. Using Application Programming Interfaces
      5. On to Chapter 9
    2. CHAPTER 9: Optimizing Data Access
      1. Identifying Suboptimal Access Paths
      2. SQL Statements with Weak Selectivity
      3. SQL Statements with Strong Selectivity
      4. On to Chapter 10
    3. CHAPTER 10: Optimizing Joins
      1. Definitions
      2. Nested Loop Joins
      3. Merge Joins
      4. Hash Joins
      5. Outer Joins
      6. Choosing the Join Method
      7. Partition-wise Joins
      8. Transformations
      9. On to Chapter 11
    4. CHAPTER 11: Beyond Data Access and Join Optimization
      1. Materialized View
      2. Result Caching
      3. Parallel Processing
      4. Direct-Path Insert
      5. Row Prefetching
      6. Array Interface
      7. On to Chapter 12
    5. CHAPTER 12: Optimizing the Physical Design
      1. Optimal Column Order
      2. Optimal Datatype
      3. Row Migration and Row Chaining
      4. Block Contention
      5. Data Compression
  15. PART 5: Appendixes
    1. APPENDIX A: Downloadable Files
      1. Test Environment
      2. Files Available for Download
    2. APPENDIX B: Bibliography
  16. Index

Product information

  • Title: Troubleshooting Oracle Performance
  • Author(s): Christian Antognini
  • Release date: June 2008
  • Publisher(s): Apress
  • ISBN: 9781590599174