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.
Table of Contents:
Preface xix
Acknowledgments xxi
About the Authors xxv
About the Technical Reviewers and Contributors xxix
Chapter 1: Troubleshooting and Tuning LOB Segment Performance 1
Introduction to the LOB Datatype 1
BASICFILE versus SECUREFILE LOBs 8
The Impact of PCTFREE on LOBs 14
Overcoming Poor INSERT Performance 17
Summary 17
Chapter 2: Overcoming Undo Tablespace Corruption 19
Overview of Undo Management 19
DTP, XA, and Rollback Segments 22
Recovering from Undo Tablespace Corruption 24
Summary 32
Chapter 3: Handling GC Buffer Busy Wait Events 35
Overview of Buffer Busy Wait Events 35
Leveraging the ORAchk Utility 36
Isolating GC Buffer Busy Waits 40
Isolating GC Buffer Busy Wait Event Issues 45
Fixes for GC Buffer Busy Waits 49
Summary 50
Chapter 4: Adaptive Cursor Sharing 51
ACS Working Algorithm 52
ACS in Action 58
ACS Bind-Awareness Monitoring 61
The Bind-Aware Cursor 73
A Practical Case 76
Summary 81
Chapter 5: Stabilizing Query Response Time Using SQL Plan Management 83
Getting Started 83
Creating a SQL Plan Baseline 87
Faking Baselines 92
Oracle Optimizer and SPM Interaction 96
SQL Plan Baseline Reproducibility 108
NLS_SORT and SQL Plan Baseline Reproducibility 114
ALL_ROWS versus FIRST_ROWS 117
Adaptive Cursor Sharing and SPM 122
Summary 131
Chapter 6: DDL Optimization Tips, Techniques, and Tricks 133
DLL Optimization Concept 133
The DDL Optimization Mechanism 136
DDL Optimization for NULL Columns 147
Summary 152
Chapter 7: Managing, Optimizing, and Tuning VLDBs 153
Overview of Very Large Databases 153
Optimal Basic Confi guration 154
VLDB Performance Tuning Principles 162
Gathering Optimizer Statistics 166
Backup and Recovery Best Practices 170
Summary 172
Chapter 8: Best Practices for Backup and Recovery with Recovery Manager 173
A Perfect Backup and Recovery Plan 173
An Overview of RMAN 174
Tips for Database Backup Strategies 175
Validating RMAN Backups 186
Backup Optimization and Tuning 187
Using RMAN for RAC Databases 189
Retaining Data in a Recovery Catalog 191
Having a Robust Recovery Strategy 192
Leveraging the Data Recovery Advisor 193
Summary 194
Chapter 9: Database Forensics and Tuning Using AWR Analysis: Part I 197
What Is AWR? 197
Knowing What to Look For 199
Header Section 199
RAC-Specific Pages 208
Time Model Statistics 211
Operating System Statistics 212
The SQL Sections 217
Instance Activity Statistics 221
Summary 233
Chapter 10: Database Forensics and Tuning Using AWR Analysis: Part II 235
Tablespace I/O Statistics 235
Buffer Pool Statistics 237
PGA Statistics 240
Shared Pool Statistics 244
Other Advisories 245
Buffer Waits Statistics 247
Enqueue Statistics 248
Undo Segment Statistics 250
Latch Statistics 251
Segment Access Areas 255
Library Cache Activity Sections 257
Dynamic Memory Components Sections 260
Process Memory Sections 262
Streams Component Sections 264
Resource Limits Statistics 266
Initialization Parameter Changes 267
Global Enqueue and Other RAC Sections 268
Summary 273
Chapter 11: Troubleshooting Problematic Scenarios in RAC 275
Troubleshooting and Tuning RAC 276
A Well-Oiled RAC Ecosystem 279
Troubleshooting RAC with OEM 12c 282
Utilities and Commands for Troubleshooting 283
Summary 288
Chapter 12: Leveraging SQL Advisors to Analyze and Fix SQL Problems 289
OEM 12c—SQL Advisors Home 290
SQL Tuning Advisor 290
SQL Access Advisor 295
SQL Repair Advisor 300
SQL Performance Analyzer 301
Summary 302
Chapter 13: Extending Data Pump for Data and Object Migration 303
Using Data Pump 303
Working with Private and Public Objects 306
Finding Valid INCLUDE and EXCLUDE Values 309
Exporting Subsets of Data 310
Changing Object Properties 313
Using PL/SQL API with Data Pump 317
Monitoring and Altering Resources 319
Improving Performance 320
Upgrading Databases 321
Summary 322
Chapter 14: Strategies for Migrating Data Quickly between Databases 323
Why Bother Migrating? 324
Determining the Best Strategy 324
Considering What Data to Migrate 326
Data Migration Methods 327
Summary 352
Chapter 15: Diagnosing and Recovering from TEMPFILE I/O Issues 353
Overview of Temporary Tablespaces 353
Correcting TEMPFILE I/O Waits 359
Summary 365
Chapter 16: Dealing with Latch and Mutex Contention 367
Overview of Latch and Mutex Architecture 367
Measuring Latch and Mutex Contention 371
Latch and Mutex Scenarios 375
Intractable Latch Contention 383
Summary 385
Chapter 17: Using SSDs to Solve I/O Bottlenecks 387
Disk Technologies: SSD versus HDD 388
The Oracle Database Flash Cache 395
Comparing SSD Options 402
Storage Tiering 410
Flash and Exadata 414
Summary 418
Chapter 18: Designing and Monitoring Indexes for Optimal Performance 421
Types of Indexes 421
Multiple Indexes on Identical Columns 431
Index Performance Issues 432
Summary 442
Chapter 19: Using SQLT to Boost Query Performance 445
Installing SQLT 446
Using the XTRACT Method 447
Using the XECUTE Method 448
Leveraging Other SQLT Methods 451
A Real-World Example 452
Summary 453
Chapter 20: Dealing with XA Distributed Transaction Issues 455
Repairing Common Distributed Transaction Issues 456
Repairing Ghost Distributed Transactions 457
Monitoring Distributed Transactions 462
Summary 464
Index 465
