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