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