Oracle Database Problem Solving and Troubleshooting Handbook

Oracle Database Problem Solving and Troubleshooting Handbook

by Tariq Farooq, Mike Ault, Paulo Portugal, Mohamed Houri, Syed Jaffar Hussain, Jim Czuprynski, Guy Harrison
Released April 2016
Publisher(s): Addison-Wesley Professional
ISBN: 9780134429267

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

An Expert Guide for Solving Complex Oracle Database Problems

Oracle Database Problem Solving and Troubleshooting Handbook delivers comprehensive, practical, and up-to-date advice for running the Oracle Database reliably and efficiently in complex production environments. Seven leading Oracle experts have brought together an unmatched collection of proven solutions, hands-on examples, and step-by-step tips for Oracle Database 12c, 11g, and other recent versions of Oracle Database. Every solution is crafted to help experienced Oracle DBAs and DMAs understand and fix serious problems as rapidly as possible.

The authors cover LOB segments, UNDO tablespaces, high GC buffer wait events, poor query response times, latch contention, indexing, XA distributed transactions, RMAN backup/recovery, and much more. They also offer in-depth coverage of a wide range of topics, including DDL optimization, VLDB tuning, database forensics, adaptive cursor sharing, data pumps, data migration, SSDs, indexes, and how to go about fixing Oracle RAC problems.

Learn how to

  • Choose the quickest path to solve high-impact problems

  • Use modern best practices to make your day more efficient and predictable

  • Construct your “Call 9-1-1 plan” for future database emergencies

  • Proactively perform maintenance to improve your environment’s stability

  • Save time with industry-standard tools and scripts

    • Register your product at informit.com/register for convenient access to downloads, updates, and corrections as they become available.

    Table of contents

    1. About This E-Book
    2. Title Page
    3. Copyright Page
    4. Contents
    5. Preface
    6. Acknowledgments
      1. Tariq Farooq
      2. Mike Ault
      3. Paulo Portugal
      4. Mohamed Houri
      5. Syed Jaffar Hussain
      6. Jim Czuprynski
      7. Guy Harrison
      8. Biju Thomas
    7. About the Authors
    8. About the Technical Reviewers and Contributors
    9. 1. Troubleshooting and Tuning LOB Segment Performance
      1. Introduction to the LOB Datatype
        1. Fixing a LOB Problem: A Real-World Example
        2. Another Real-World Example: HW Resolution
        3. BASICFILE LOB Issues: Toward a More Perfect Fix
      2. BASICFILE versus SECUREFILE LOBs
        1. LOB New and Old Type Differences
        2. Migrating BASICFILE LOBs to SECUREFILE LOBs
      3. The Impact of PCTFREE on LOBs
      4. Overcoming Poor INSERT Performance
      5. Summary
    10. 2. Overcoming Undo Tablespace Corruption
      1. Overview of Undo Management
        1. The Importance of UNDO_RETENTION
        2. Tuning UNDO_RETENTION
      2. DTP, XA, and Rollback Segments
        1. Other Unusual Rollback and Undo Segment Issues
      3. Recovering from Undo Tablespace Corruption
        1. Preventing, Detecting, and Repairing Corruption
        2. Handling Memory Corruption
        3. Handling Logical Corruption
        4. Overcoming Media Corruption
      4. Summary
    11. 3. Handling GC Buffer Busy Wait Events
      1. Overview of Buffer Busy Wait Events
      2. Leveraging the ORAchk Utility
        1. Installing ORAchk
        2. Results of ORAchk Execution: Sample Output
      3. Isolating GC Buffer Busy Waits
        1. Using ADDM to Find Event Information
        2. Using AWR to Find Event Information
        3. Using ASH to Find Event Information
      4. Isolating GC Buffer Busy Wait Event Issues
        1. Using ASH Views to Find Waiting Sessions
        2. Quickly Isolating Performance Bottlenecks
      5. Fixes for GC Buffer Busy Waits
      6. Summary
    12. 4. Adaptive Cursor Sharing
      1. ACS Working Algorithm
        1. Bind Sensitiveness with Range Predicate
        2. Bind Sensitiveness with Equality Predicate and Histogram
        3. Bind Sensitiveness with Partition Keys
      2. ACS in Action
      3. ACS Bind-Awareness Monitoring
        1. BUCKET_ID and COUNT Relationship
        2. Marking Cursors Bind Aware
      4. The Bind-Aware Cursor
      5. A Practical Case
      6. Summary
    13. 5. Stabilizing Query Response Time Using SQL Plan Management
      1. Getting Started
      2. Creating a SQL Plan Baseline
        1. Capturing Plans Automatically
        2. Loading Plans from the Cursor Cache
      3. Faking Baselines
      4. Oracle Optimizer and SPM Interaction
        1. When the CBO Plan Matches the SQL Plan Baseline
        2. When the CBO Plan Doesn’t Match the SQL Plan Baseline
        3. When SQL Plan Baseline Is Not Reproducible
      5. SQL Plan Baseline Reproducibility
        1. Renaming the Index
        2. Changing the Index Type
        3. Adding Trailing Columns to the Index
        4. Reversing the Index
      6. NLS_SORT and SQL Plan Baseline Reproducibility
      7. ALL_ROWS versus FIRST_ROWS
      8. Adaptive Cursor Sharing and SPM
        1. ACS and SPM in Oracle 11g Release 11.2.0.3.0
        2. ACS and SPM in Oracle Database 12c Release 12.1.0.1.0
      9. Summary
    14. 6. DDL Optimization Tips, Techniques, and Tricks
      1. DLL Optimization Concept
      2. The DDL Optimization Mechanism
        1. Table Cardinality Estimation
        2. C_DDL Column in a Virtual Column
        3. C_DDL Column in a Column Group Extension
        4. When the Default Value of C_DDL Changes
        5. C_DDL Column and Indexes
      3. DDL Optimization for NULL Columns
      4. Summary
    15. 7. Managing, Optimizing, and Tuning VLDBs
      1. Overview of Very Large Databases
      2. Optimal Basic Configuration
        1. Data Warehouse Template
        2. Optimal Data Block Size
        3. Bigfile Tablespaces
        4. Adequate SGA and PGA
        5. Temporary Tablespace Groups
        6. Data Partitioning
        7. Index Partitioning: Local versus Global
        8. Data Compression
        9. Table Compression
        10. Heat Map and Automatic Data Optimization
        11. Advanced Index Partition Compression
      3. VLDB Performance Tuning Principles
        1. Real-World Scenario
        2. Limiting the Impact of Indexes on Data Loading
        3. Maximizing Resource Utilization
      4. Gathering Optimizer Statistics
        1. Incremental Statistics Synopsis
        2. Gathering Statistics Concurrently
        3. Setting the ESTIMATE_PERCENT Value
      5. Backup and Recovery Best Practices
        1. Exadata Solutions
        2. Utilizing a Data Guard Environment
      6. Summary
    16. 8. Best Practices for Backup and Recovery with Recovery Manager
      1. A Perfect Backup and Recovery Plan
      2. An Overview of RMAN
      3. Tips for Database Backup Strategies
        1. Full Backups and Incremental Backups
        2. Compressed Backups
        3. Incremental Backups
        4. Faster Incremental Backups
        5. Rewinding in Oracle Flashback Technology
        6. Disk-Based Backup Solutions
        7. Recover Forward Forever
      4. Validating RMAN Backups
      5. Backup Optimization and Tuning
        1. Tuning Disk-Based Backup Performance
      6. Using RMAN for RAC Databases
      7. Retaining Data in a Recovery Catalog
      8. Having a Robust Recovery Strategy
      9. Leveraging the Data Recovery Advisor
      10. Summary
    17. 9. Database Forensics and Tuning Using AWR Analysis: Part I
      1. What Is AWR?
      2. Knowing What to Look For
      3. Header Section
        1. Load Profile
        2. Instance Efficiencies
        3. Shared Pool Memory
        4. Wait Events
        5. Load Average
        6. Instance CPU
        7. Memory Statistics
      4. RAC-Specific Pages
        1. RAC Statistics (CPU)
        2. Global Cache Load Statistics
        3. Global Cache and Enqueue Services
        4. Cluster Interconnects
      5. Time Model Statistics
      6. Operating System Statistics
        1. Foreground Wait Events
        2. Background Wait Events
        3. Wait Event Histograms
        4. Service-Related Statistics
      7. The SQL Sections
        1. Total Elapsed Time
        2. Total CPU Time
        3. Total Buffer Gets
        4. Total Disk Reads
        5. Total Executions
        6. Parse Calls
        7. Shareable Memory
        8. Version Count
        9. Cluster Wait Time
      8. Instance Activity Statistics
        1. Consistent Get Statistics
        2. DB Block Get Statistics
        3. Dirty Block Statistics
        4. Enqueue Statistics
        5. Execution Count
        6. Free Buffer Statistics
        7. Global Cache (GC) Statistics
        8. Index Scan Statistics
        9. Leaf Node Statistics
        10. Open Cursors
        11. Parse Statistics
        12. Physical Read and Write Statistics
        13. Recursive Statistics
        14. Redo-Related Statistics
        15. Session Cursor Statistic
        16. Sort Statistics
        17. Summed Dirty Queue Length
        18. Table Fetch Statistics
        19. Transaction Rollback
        20. Undo Change Vector Statistic
        21. User Statistics
        22. Work Area Statistics
        23. Instance Activity Statistics—Absolute Values
        24. Instance Activity Statistics—Thread Activity
      9. Summary
    18. 10. Database Forensics and Tuning Using AWR Analysis: Part II
      1. Tablespace I/O Statistics
      2. Buffer Pool Statistics
        1. Buffer Pool Statistics
        2. Instance Recovery Statistics
        3. Buffer Pool Advisory Section
      3. PGA Statistics
        1. PGA Aggregate Summary
        2. PGA Aggregate Target Statistics
        3. PGA Aggregate Target Histogram
        4. PGA Memory Advisor
      4. Shared Pool Statistics
      5. Other Advisories
        1. SGA Target Advisory
        2. Streams Pool Advisory
        3. Java Pool Advisory
      6. Buffer Waits Statistics
      7. Enqueue Statistics
      8. Undo Segment Statistics
      9. Latch Statistics
        1. Latch Activity
        2. Latch Sleep Breakdown
        3. Latches and Spin Count
        4. Latch Miss Sources
        5. Mutex Sleep Summary
        6. Parent and Child Latches
      10. Segment Access Areas
      11. Library Cache Activity Sections
      12. Dynamic Memory Components Sections
      13. Process Memory Sections
        1. Process Memory Summary
        2. SGA Memory Summary
        3. SGA Breakdown Difference
      14. Streams Component Sections
      15. Resource Limits Statistics
      16. Initialization Parameter Changes
      17. Global Enqueue and Other RAC Sections
        1. Global Enqueue Statistics
        2. Global CR Served Statistics
        3. Global Current Served Statistics
        4. Global Cache Transfer Statistics
        5. Global Cache Transfer Times
        6. Global Cache Transfer (Immediate)
        7. Global Cache Times (Immediate)
        8. Interconnect Ping Latency Statistics
        9. Interconnect Throughput by Client
        10. Interconnect Device Statistics
      18. Summary
    19. 11. Troubleshooting Problematic Scenarios in RAC
      1. Troubleshooting and Tuning RAC
        1. Start with ORAchk
        2. Employ the TFA Collector Utility
        3. Utilize the Automatic Diagnostic Repository
        4. Check the Alert and Trace Log Files
        5. Employ the Three A’s
        6. Check the Private Cluster Interconnect
        7. Enable Tracing and Inspect the Trace Logs
        8. Utilize the Cluster Health Monitor
        9. Miscellaneous Tools and Utilities
        10. Useful My Oracle Support Resources
      2. A Well-Oiled RAC Ecosystem
        1. Maximum Availability Architecture
        2. Optimal and Efficient Databases in RAC
      3. Troubleshooting RAC with OEM 12c
      4. Utilities and Commands for Troubleshooting
      5. Summary
    20. 12. Leveraging SQL Advisors to Analyze and Fix SQL Problems
      1. OEM 12c—SQL Advisors Home
      2. SQL Tuning Advisor
        1. Running SQL Tuning Advisor in OEM 12c
        2. Running SQL Tuning Advisor Manually in SQL*Plus
      3. SQL Access Advisor
        1. Running SQL Access Advisor in OEM 12c
        2. Running SQL Access Advisor Manually in SQL*Plus
      4. SQL Repair Advisor
      5. SQL Performance Analyzer
      6. Summary
    21. 13. Extending Data Pump for Data and Object Migration
      1. Using Data Pump
        1. Copying Objects
        2. Data Pump Modes
      2. Working with Private and Public Objects
        1. Saving and Restoring Database Links
        2. Exporting Public Database Links and Synonyms
        3. Verifying Content of the Export Dump File
      3. Finding Valid INCLUDE and EXCLUDE Values
      4. Exporting Subsets of Data
      5. Changing Object Properties
        1. Importing Partitioned Tables as Nonpartitioned
        2. Importing Table Partitions as Individual Tables
        3. Masking Data
        4. Renaming Tables or Different Tablespaces
        5. Using Default Storage Parameters
        6. Resizing Tablespaces during Import
        7. Consolidating Multiple Tablespaces
      6. Using PL/SQL API with Data Pump
      7. Monitoring and Altering Resources
      8. Improving Performance
      9. Upgrading Databases
      10. Summary
    22. 14. Strategies for Migrating Data Quickly between Databases
      1. Why Bother Migrating?
      2. Determining the Best Strategy
        1. Real-Time versus Near Real-Time Migration
        2. Read-Only Tolerance
        3. Reversibility
      3. Considering What Data to Migrate
      4. Data Migration Methods
        1. Transactional Capture Migration Methods
        2. Nontransactional Migration Methods
        3. Piecemeal Migration Methods
      5. Summary
    23. 15. Diagnosing and Recovering from TEMPFILE I/O Issues
      1. Overview of Temporary Tablespaces
        1. Read-Only Databases
        2. Locally Managed Temporary Tablespaces
        3. Temporary Tablespace Groups
        4. Global Temporary Tables
      2. Correcting TEMPFILE I/O Waits
        1. Undersized PGA
        2. Inappropriate TEMPFILE Extent Sizing
        3. Inappropriate Use of GTTs
      3. Summary
    24. 16. Dealing with Latch and Mutex Contention
      1. Overview of Latch and Mutex Architecture
        1. What Are Latches?
        2. What Are Mutexes?
        3. Latch and Mutex Internals
      2. Measuring Latch and Mutex Contention
        1. Identifying Individual Latches
        2. Drilling into Segments and SQLs
      3. Latch and Mutex Scenarios
        1. Library Cache Mutex Waits
        2. Library Cache Pin
        3. Shared Pool Latch
        4. Cache Buffers Chains Latch
        5. Other Latch Scenarios
      4. Intractable Latch Contention
        1. Fine Tuning Latch Algorithms
      5. Summary
    25. 17. Using SSDs to Solve I/O Bottlenecks
      1. Disk Technologies: SSD versus HDD
        1. The Rise of Solid-State Flash Disks
        2. Flash SSD Latency
        3. Economics of SSD
        4. SLC, MLC, and TLC Disks
        5. Write Performance and Endurance
        6. Garbage Collection and Wear Leveling
        7. SATA versus PCIe SSD
        8. Using SSD Devices in an Oracle Database
      2. The Oracle Database Flash Cache
        1. Free Buffer Waits
        2. Configuring and Monitoring DBFC
        3. Using the FLASH_CACHE Clause
        4. Flash Cache Performance Statistics
      3. Comparing SSD Options
        1. Indexed Reads
        2. OLTP Read/Write Workload
        3. Full Table Scan Performance
        4. SSD Native Caches and Full Table Scans
        5. Disk Sort and Hash Operations
        6. Redo Log Optimization
      4. Storage Tiering
        1. Using Partitions to Tier Data
      5. Flash and Exadata
        1. Creating Flash-Backed ASM Disk Groups on Exadata
      6. Summary
    26. 18. Designing and Monitoring Indexes for Optimal Performance
      1. Types of Indexes
        1. B-Tree Indexes
        2. Bitmap Indexes
        3. Partitioned Indexes
        4. Other Index Types
      2. Multiple Indexes on Identical Columns
      3. Index Performance Issues
        1. Index Statistics
        2. The Impact of a Low Clustering Factor
        3. Operational Considerations for Indexes
        4. Hiding Unselective Indexes
        5. Index Performance Issues in RAC Databases
      4. Summary
    27. 19. Using SQLT to Boost Query Performance
      1. Installing SQLT
      2. Using the XTRACT Method
      3. Using the XECUTE Method
      4. Leveraging Other SQLT Methods
      5. A Real-World Example
      6. Summary
    28. 20. Dealing with XA Distributed Transaction Issues
      1. Repairing Common Distributed Transaction Issues
      2. Repairing Ghost Distributed Transactions
        1. Information Exists, but Transaction Missing
        2. ORA-1591 Has No Corresponding Information
        3. Transaction Hangs after COMMIT or ROLLBACK
      3. Monitoring Distributed Transactions
      4. Summary
    29. Index
    30. Code Snippets

    Product information

    • Title: Oracle Database Problem Solving and Troubleshooting Handbook
    • Author(s): Tariq Farooq, Mike Ault, Paulo Portugal, Mohamed Houri, Syed Jaffar Hussain, Jim Czuprynski, Guy Harrison
    • Release date: April 2016
    • Publisher(s): Addison-Wesley Professional
    • ISBN: 9780134429267