Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning

Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning

by Richmond Shee, Kirtikumar Deshpande, K Gopalakrishnan
Released July 2004
Publisher(s): McGraw Hill Computing
ISBN: 9780071483759

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

Troubleshoot, tune, and optimize your Oracle database efficiently and successfully every time. This book explains how to take full advantage of the revolutionary Oracle Wait Interface to quickly pinpoint--and solve--core problems and bottlenecks, and increase productivity exponentially.

Table of contents

  1. Cover Page
  2. Oracle Wait Interface
  3. Copyright Page
  4. Dedication
  5. Contents
  6. Foreword
  7. Acknowledgments
  8. Introduction
  9. 1 Introduction to Oracle Wait Interface
    1. The Old Fashion of Oracle Performance Optimization
    2. Why Are Cache-Hit Ratios Grossly Inefficient?
    3. The New Fashion of Oracle Performance Optimization
    4. The OWI Philosophy
    5. Database Response Time Tuning Model
    6. Paradigm Shift
    7. In a Nutshell
  10. 2 Oracle Wait Interface Components
    1. What Is a Wait Event?
    2. OWI Components
      1. V$EVENT_NAME View
      2. V$SYSTEM_EVENT View
      3. V$SESSION_EVENT View
      4. V$SESSION_WAIT View
      5. Trace Event 10046—The Extended SQL Trace
    3. New OWI Views in Oracle Database 10g Release 1
      1. V$SESSION_WAIT_HISTORY View
      2. V$SYSTEM_WAIT_CLASS View
      3. V$SESSION_WAIT_CLASS View
      4. V$EVENT_HISTOGRAM View
      5. Types of Wait Events
    4. OWI Limitations
      1. No CPU Statistics
      2. No End-to-End Visibility
      3. No Historical Data
      4. Inaccuracies
    5. In a Nutshell
  11. 3 Common Wait Events
    1. Introduction to Common Wait Events
      1. buffer busy waits
      2. control file parallel write
      3. db file parallel read
      4. db file parallel write
      5. db file scattered read
      6. db file sequential read
      7. db file single write
      8. direct path read
      9. direct path write
      10. enqueue
      11. free buffer waits
      12. latch free
      13. library cache pin
      14. library cache lock
      15. log buffer space
      16. log file parallel write
      17. log file sequential read
      18. log file switch (archiving needed)
      19. log file switch (checkpoint incomplete)
      20. log file switch completion
      21. log file sync
      22. SQL*Net message from client
      23. SQL*Net message to client
    2. Common Wait Events in Oracle Real Application Clusters Environment
      1. global cache cr request
      2. buffer busy global cache
      3. buffer busy global cr
      4. global cache busy
      5. global cache null to x
      6. global cache null to s
      7. global cache s to x
      8. global cache open x
      9. global cache open s
      10. row cache lock
    3. Tracking CPU and Other Statistics
    4. In a Nutshell
  12. 4 OWI Monitoring and Collection Methods
    1. Why Is Historical Performance Data Important?
    2. Fast and Accurate Root Cause Analysis
    3. Why Trace Event 10046 Is Not a Suitable Data Collector
    4. Why Statspack Is Not a Suitable Data Collector
    5. Database Logoff Trigger as a Data Collector
    6. Sampling for Performance Data Using PL/SQL Procedure
      1. Data Source
      2. Sampling Frequency
      3. Repositories
      4. Events to Monitor
      5. Pros and Cons
    7. Sampling for Performance Data with SQL-less SGA Access
    8. In a Nutshell
  13. 5 Interpreting Common I/O Related Wait Events
    1. db file sequential read
      1. Common Causes, Diagnosis, and Actions
    2. db file scattered read
      1. Common Causes, Diagnosis, and Actions
      2. Why Does db file sequential read event Show Up in a Full Table Scan Operation
      3. Why Does a Full Scan Operation Request Fewer Blocks than the MBRC
      4. Setting the DB_FILE_ MULTIBLOCK_ READ_ COUNT (MBRC)
      5. Why Physical I/Os Are Expensive
    3. direct path read
      1. Common Causes, Diagnosis, and Actions
      2. Initialization Parameters of Interest
    4. direct path write
      1. Common Causes, Diagnosis, and Actions
    5. db file parallel write
      1. Common Causes, Diagnosis, and Actions
    6. log file parallel write
      1. Common Causes, Diagnosis, and Actions
    7. control file parallel write
      1. Common Causes, Diagnosis, and Actions
    8. In a Nutshell
  14. 6 Interpreting Locks-Related Wait Events
    1. Latch Free
      1. What Is a Latch?
      2. Differences between a Latch and a Lock
      3. Latch Family
      4. Latch Acquisition
      5. Latch Classification
      6. What Does the Latch Free Wait Event Tell You?
      7. Latch Miss Locations
      8. Latches in Oracle Database 10g Release 1
      9. Common Causes, Diagnosis, and Actions
      10. Shared Pool and Library Cache Latches
      11. Cache Buffers Chains Latches
      12. Cache Buffers Lru Chain Latches
      13. Row Cache Objects Latches
    2. Enqueue
      1. What Is an Enqueue?
      2. What Is an Enqueue Resource?
      3. What Is an Enqueue Lock?
      4. Enqueue Architecture
      5. Decoding Enqueue Type and Mode
      6. Common Causes, Diagnosis, and Actions
    3. Buffer Busy Waits
      1. Common Causes, Diagnosis, and Actions
    4. In a Nutshell
  15. 7 Interpreting Common Latency-Related Wait Events
    1. log file sync
      1. Common Causes, Diagnosis, and Actions
    2. log buffer space
      1. Common Causes, Diagnosis, and Actions
    3. free buffer waits
      1. Common Causes, Diagnosis, and Actions
    4. write complete waits
      1. Common Causes, Diagnosis, and Actions
    5. log file switch completion
      1. Common Causes, Diagnosis, and Actions
    6. log file switch (checkpoint incomplete)
      1. Common Causes, Diagnosis, and Actions
    7. In a Nutshell
  16. 8 Wait Events in a Real Application Clusters Environment
    1. What’s So Special About Waits in Real Application Clusters (RAC)?
      1. Global Buffer Cache in Real Application Clusters
    2. Global Cache Waits
      1. global cache cr request
      2. global cache busy
    3. RAC Wait Event Enhancements in Oracle Database 10g
    4. Enqueue Waits
      1. Most Common Enqueues
    5. In a Nutshell
  17. 9 Performance Management in Oracle Database 10g
    1. Database Statistics
      1. Time Model Statistics
      2. Wait Model Statistics
      3. Operating System Statistics
      4. Additional SQL Statistics
      5. Database Metrics
    2. New Background Processes
    3. Automatic Workload Repository
      1. Repository Snapshots
      2. Snapshot Baselines
      3. Using EM to Manage AWR
      4. Manually Managing AWR
    4. Active Session History
      1. What Is an Active Session?
      2. Components of ASH
    5. Automatic Database Diagnostic Monitor (ADDM)
      1. ADDM Setup
      2. Using EM to Access ADDM
      3. Manually Running ADDM Report
      4. ADDM Views
    6. In a Nutshell
  18. A Oracle Database 10g Diagnostic Events
    1. Oracle Diagnostic Events
      1. Types of Diagnostic Events
      2. Setting Diagnostic Events
      3. Internal Workings of the Events
  19. B Enqueue Waits in Oracle Database 10g
  20. C Oracle Dumps and Traces
    1. Oradebug: The Ultimate Utility for Traces and Dumps
    2. Data Block Dump
      1. Syntax
    3. Buffers Dump
      1. Syntax
      2. Controlling the Dump Information
    4. Buffer Dump
      1. Syntax
      2. Controlling the Dump Information Using Levels
    5. File Headers Dump
      1. Syntax
      2. Controlling the Dump Information Using Levels
    6. Control File Dump
      1. Syntax
      2. Controlling the Dump Information Using Levels
    7. Heap Dump
      1. Syntax
      2. Controlling the Dump Information Using Levels
    8. Library Cache Dump
      1. Syntax
      2. Controlling the Dump Information Using Levels
    9. Processstate Dump
      1. Syntax
    10. Shared Server State Dump
      1. Syntax
      2. Controlling the Dump Information Using Levels
    11. Systemstate Dump
      1. Syntax
      2. Controlling the Dump Information Using Levels
    12. Redo Log Dump
      1. Syntax
      2. Controlling the Dump Information Using Levels
  21. D Direct SGA Access
    1. Overhead
    2. Security
    3. Speed
    4. Concurrency
    5. Get Hidden Information
    6. Introduction to X$ Views
    7. The Necessary Ingredients
      1. Find SGA ID
      2. Find SGA Base Address
      3. Find the Starting Address of X$KSUSECST
      4. Find the Record Size of the X$KSUSECST Structure
      5. Find Number of Records in the X$KSUSECST Structure
      6. Find the X$KSUSECST View Columns Offsets
    8. Attaching the SGA to a C Program
  22. E References
    1. Chapter 1
    2. Chapter 2
    3. Chapter 3
    4. Chapter 4
    5. Chapter 5
    6. Chapter 6
    7. Chapter 7
    8. Chapter 8
    9. Chapter 9
    10. Appendix A
    11. Appendix B
    12. Appendix C
    13. Appendix D
  23. Index

Product information

  • Title: Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning
  • Author(s): Richmond Shee, Kirtikumar Deshpande, K Gopalakrishnan
  • Release date: July 2004
  • Publisher(s): McGraw Hill Computing
  • ISBN: 9780071483759