Expert Oracle RAC Performance Diagnostics and Tuning

Book description

"

Expert Oracle RAC Performance Diagnostics and Tuning provides comprehensive coverage of the features, technology and principles for testing and tuning RAC databases. The book takes a deep look at optimizing RAC databases by following a methodical approach based on scientific analysis rather than using a speculative approach, twisting and turning knobs and gambling on the system.

The book starts with the basic concepts of tuning methodology, capacity planning, and architecture. Author Murali Vallath then dissects the various tiers of the testing implementation, including the operating system, the network, the application, the storage, the instance, the database, and the grid infrastructure. He also introduces tools for performance optimization and thoroughly covers each aspect of the tuning process, using many real-world examples, analyses, and solutions from the field that provide you with a solid, practical, and replicable approach to tuning a RAC environment. The book concludes with troubleshooting guidance and quick reference of all the scripts used in the book.

Expert Oracle RAC Performance Diagnostics and Tuning covers scenarios and details never discussed before in any other performance tuning books. If you have a RAC database, this book is a requirement. Get your copy today.

  • Takes you through optimizing the various tiers of the RAC environment.
  • Provides real life case studies, analysis and solutions from the field.
  • Maps a methodical approach to testing, tuning and diagnosing the cluster
  • "

    Table of contents

    1. Cover
    2. Title
    3. Copyright
    4. Dedication
    5. Contents at a Glance
    6. Contents
    7. About the Author
    8. About the Technical Reviewer
    9. Acknowledgments
    10. Introduction
    11. Chapter 1: Methodology
      1. Performance Requirements
      2. Tuning the System
        1. Step 1: Optimizing Workload
        2. Step 2: Finding and Eliminating Contention
        3. Step 3: Reduce Physical I/O
        4. Step 4: Optimize Logical I/O
      3. Methodology
        1. Performance Tuning Methodology
        2. Getting to the Obvious
        3. Divide Into Quadrants
        4. Looking at Overall Database Performance
      4. Oracle Unified Method
        1. Testing and Performance Management
      5. RAP Testing
        1. RAP Testing Phase I—Stability Testing of the Cluster
        2. RAP Testing Phase II—Availability and Load Balancing
        3. RAP Testing Phase III—High Availability
        4. RAP Testing Phase IV—Backup and Recovery
        5. RAP Testing Phase V—Hardware Scalability
        6. RAP Testing Phase VI—Database Scalability
        7. RAP Testing Phase VII—Application Scalability
        8. RAP Testing Phase VIII—Burnout Testing
      6. Creating an Application Testing Environment
      7. How Much to Tune?
      8. Conclusion
    12. Chapter 2: Capacity Planning and Architecture
      1. Analyzing the Stack
      2. Capacity Planning
        1. How to Measure Scaling
        2. Estimating Size of Database Objects
      3. Architecture
        1. Oracle Single-Instance vs. Clustered Configuration
        2. RAC Architecture
      4. Conclusion
    13. Chapter 3: Testing for Availability
      1. Points of Failure (Gaps)
        1. Interconnect Failure
        2. Node Failure
        3. Instance Failure
        4. Oracle Component Failure
      2. Media Failure
        1. Protecting the Database
      3. Testing Hardware for Availability
        1. RAP Phase I
      4. Testing Application for Availability
        1. RAP Phase II—Availability and Load Balancing
        2. RAP Phase III—HA
        3. RAP Phase IV—Backup and Recovery
      5. Conclusion
    14. Chapter 4: Testing for Scalability
      1. Scale-Up or Scale-Out
        1. Scale-Up
        2. Scale-Out
      2. Scalable Components
        1. Interconnect
        2. ASM
        3. Instance
        4. SQL*Net
      3. Testing Hardware for Scalability
        1. RAP Phase V Hardware Scalability
      4. Testing the Database for Scalability
      5. Testing Application for Scalability
        1. RAP Phase VII Application Scalability
      6. End-to-End Testing
      7. RAP Testing Phase VIII Burnout Tests
      8. Conclusion
    15. Chapter 5: Real Application Testing
      1. Testing Methods
        1. Method I—Using Homegrown Utilities
        2. Method II—Using Real Application Testing
      2. Conclusion
    16. Chapter 6: Tools and Utilities
      1. Oracle Enterprise Manager
        1. Performance Manager
        2. SQL Advisory
        3. Automatic Workload Repository
        4. Automatic Workload Repository Warehouse
        5. Automatic Database Diagnostic Monitor
        6. Active Session History
      2. Tools and Utilities from Oracle Support
        1. OSWATCHER
        2. Light Onboard Monitor (LTOM)
      3. Cluster Health Monitor (CHM)
        1. Architecture
        2. CHM Statistics
      4. EXPLAIN PLAN
      5. DBMS_SQLTUNE
      6. SQL Trace
      7. Event 10046
        1. Level 1
        2. Level 4
        3. Level 8
        4. Level 12
      8. Event 10053
      9. Service-Module-Action
      10. The trcsess Utility
      11. Oracle’s Wait Interface
      12. Conclusion
    17. Chapter 7: SQL Tuning
      1. SQL Execution Life Cycle
        1. Step 1: Create a Cursor
        2. Step 2: Parse the Statement
        3. Step 3: Describe the Results
        4. Step 4: Define Query Output
        5. Step 5: Bind Any Variables
        6. Step 6: Parallelize the Statement
        7. Step 7: Execute the Statement
        8. Step 8: Fetch Rows
        9. Step 9: Close the Cursor
      2. Capturing Execution Times
        1. TIMED_STATISTICS
        2. STATISTICS_LEVEL
        3. V$STATISTICS_LEVEL
        4. TIMED_OS_STATISTICS
      3. What Are Consistent Gets?
        1. Logical Reads
        2. Physical Reads
      4. Why Tune?
        1. Optimizer
        2. Optimizer Statistics
      5. Conclusion
    18. Chapter 8: Parallel Query Tuning
      1. Why Parallel Processing?
      2. Oracle and Parallel Processing
        1. Parallel Query Architecture
        2. Methods of Applying Parallelism
        3. Parallel Processing in RAC
        4. Parallel Recovery
      3. Optimizing Parallel Operations
        1. Data Dictionary Views to Monitor Parallel Operations
        2. Wait Events Related to Parallel Operations
        3. Troubleshooting Using Oracle Event Interface
      4. Conclusion
    19. Chapter 9: Tuning the Database
      1. Data Partitioning
      2. Partitioned Indexes
        1. Local Indexes
        2. Global Indexes
      3. Benefits of Partitioning
        1. Partition Pruning
        2. Partition-wise Joins
      4. Sequence Numbers
        1. Sequences and Index Contention
      5. Undo Block Considerations
      6. Hard Parses
      7. Result Cache
        1. Query Result Cache
        2. Function Result Cache
        3. Limitations of the Result Cache
      8. In-Memory Cache
        1. How Does This Work?
        2. Workshop
      9. Conclusion
    20. Chapter 10: Tuning Recovery
      1. Instance Recovery
        1. How Does Oracle Know That Recovery Is Required for a Given Data File?
        2. The Instance Recovery Process
        3. Tuning Instance Recovery
        4. Redo Log Sizing Advisory
        5. Crash Recovery
        6. Thread Recovery
        7. Online Block Level Recovery
      2. Media Recovery
      3. Fast Recovery Area
      4. Conclusion
    21. Chapter 11: Tuning Oracle Net
      1. Making the Connection
        1. Connectivity Drivers
        2. Oracle Net Foundation Layer (ONFL)
        3. Oracle Protocol Support Layer
        4. Listeners
        5. Load Balancing
        6. Tracing the Load Metric Capture
      2. Connection Throughput
        1. Workshop
        2. Wait Events related to SQL*Net
      3. Conclusion
    22. Chapter 12: Tuning the Storage Subsystem
      1. Choosing Storage
        1. Transaction-Based Workload
        2. Throughput-Based Workload
        3. Mixed Workload
      2. Choosing the Storage Array
      3. Storage-Wide Considerations for Performance
        1. Disk Drive Performance
        2. Storage Contention
      4. Oracle Files and RAID
        1. Datafiles
        2. Redolog Files
      5. Testing to Determine Performance
        1. Types of I/O Operations
        2. Oracle and I/O Characteristics
      6. Oracle-Supported Access Types
        1. Clustered File System
        2. Automatic Storage Management
        3. ASM Clustered File System
      7. ASM Architecture
      8. Allocation Units
      9. How Many Diskgroups?
      10. Monitoring ASM
        1. Data Dictionary Views to Monitor Storage
        2. Enterprise Manager to Monitor Storage
      11. Conclusion
    23. Chapter 13: Tuning Global Cache
      1. Global Cache
      2. RAC Specific Background Processes
        1. LMSn
        2. LMON
        3. LMDn
        4. LCK0
        5. LMHB
        6. ACMS
        7. RMSn
        8. RSMN
        9. PING
        10. IPC0
        11. LDDn
        12. Resource Availability
      3. Think Outside the Interconnect
        1. Scenario 1: Block Request Involving Two Instances
        2. Scenario 2: Block Request Involving Three Instances
        3. Prepare Phase
        4. Transfer Phase
        5. Workshop
      4. Mastering and Remastering
        1. Monitoring Remastering
        2. Manual Remastering
      5. Global Cache Optimization
        1. Queries with High Cluster Overhead
        2. Blockers and Deadlocks
        3. Identifying Hot Blocks
        4. Data Dictionary Views to Monitor Global Cache
        5. Enterprise Manager to Monitor Global Cache
        6. Troubleshooting Using Oracle Event Interface
      6. Conclusion
    24. Chapter 14: Tuning the Cluster Interconnect
      1. Cluster Interconnect
        1. Block Transfer
        2. Types of Interconnects
        3. Network Throughput and Bandwidth
      2. Verifying the Interconnect
        1. Check 1
        2. Check 2
        3. Check 3
        4. Check 4
        5. Check 5
      3. Think Inside the Interconnect
        1. Prepare Phase
        2. Transfer Phase
        3. Workshop
        4. Linear Scalability of Private Interconnect
      4. Interconnect Monitoring
        1. Workshop
        2. Data Dictionary Views to Monitor Cluster Interconnect
        3. Enterprise Manager to Monitor Cluster Interconnect
      5. Conclusion
    25. Chapter 15: Optimize Distributed Workload
      1. Service Framework
        1. Manageability
        2. Availability
        3. Performance
      2. ServerPools
      3. Distributed Workload Management (DWM)
        1. Resource Manager
        2. Fast Application Notification (FAN)
        3. Fast Connect Failover (FCF)
      4. Load Balancing
        1. Client Load Balancing
        2. Connection Load Balancing
        3. Applications Using Connection Pooling
      5. Locating the Problem
        1. Workshop
        2. Troubleshooting Using Oracle Event Interface
      6. Conclusion
    26. Chapter 16: Oracle Clusterware Diagnosis
      1. Oracle Clusterware
        1. The Oracle Cluster Registry (OCR)
        2. The Oracle Local Registry (OLR)
        3. High Availability Service (HAS)
        4. The Cluster Synchronization Service (CSS)
        5. Cluster/Node Failures/Evictions
      2. OCR Administration Utilities
        1. OCR Verification (ocrcheck) Utility
        2. OCR Configuration (ocrconfig) Utility
        3. OCR Dump (ocrdump) Utility
        4. Workshop
      3. EVMD Verification
      4. Grid Plug and Play
      5. Monitoring Resource Utilization in the Cluster
        1. Step 1
        2. Step 2
        3. Step 3
      6. Conclusion
    27. Chapter 17: Waits, Enqueues, and Latches
      1. Latches
        1. Willing-to-Wait Mode
        2. No-Wait Mode
        3. Cache Buffers Chains
        4. gc element
        5. Redo Allocation
        6. Classes
      2. Enqueues
        1. TX—Transaction
        2. TM—DML (Table)
        3. ST—Space Management Transaction
        4. HW—Segment High Water Mark
        5. SQ—Sequence Number
        6. CF—Control File Transaction
      3. Waits
        1. Consistent Read vs. Current
        2. Cluster Waits
        3. Mutex Waits
      4. Conclusion
    28. Chapter 18: Problem Diagnosis
      1. Health Monitor
        1. V$HM_CHECK
        2. Running Checks
        3. HM Using EM
      2. Automatic Diagnostic Repository
        1. ADR Actions
        2. ADR Configuration
        3. Retention Policy
        4. Workshop—Monitoring Incidents and Problems
        5. SQL Test Case Builder
      3. ORADEBUG Utility
      4. Critical ORA Errors
        1. ORA-600: Internal error code, arguments: [...], [...]
        2. ORA-7445: exception encountered core dump [...][...]
      5. DBA Support Utilities
        1. Remote Diagnostic Agent (RDA)
        2. RAC DIAG
        3. ORACHK
        4. OLS
      6. Conclusion
    29. Appendix A: The SQL Scripts Used in This Book
      1. Chapter 3—Testing for Availability
      2. Chapter 4—Testing for Scalability
      3. Chapter 5—Real Application Testing
      4. Chapter 6—Tools and Utilities
      5. Chapter 9—Tuning the Database
      6. Chapter 11—Tuning Oracle Net
      7. Chapter 12—Tuning the Storage Subsystem
      8. Chapter 13—Tuning Global Cache
      9. Chapter 14—Tuning the Cluster Interconnect
      10. Chapter 15—Optimizing Distributed Workload
      11. Chapter 17—Waits, Enqueues, and Latches
      12. Chapter 18—Problem Diagnostics
    30. Bibliography
    31. Index

    Product information

    • Title: Expert Oracle RAC Performance Diagnostics and Tuning
    • Author(s): Murali Vallath
    • Release date: October 2014
    • Publisher(s): Apress
    • ISBN: 9781430267102