Using the Online Code Depot
Oracle Script Collection
Conventions Used in this Book
Are you ready to tune?
Acknowledgements
Preface
Chapter 1: Introduction to Oracle Tuning
An Introduction to Oracle Tuning
Throughput vs. Response Time
Top-down Tuning vs. Bottom-up Tuning
Proactive Tuning vs. Reactive Tuning
Reactive Oracle Tuning
Proactive Oracle Tuning
Know the Limits: Things that We Can Not Tune
Oracle Application-level Tuning
A Historical Review of DBA Job Duties
Oracle Tuning and Server Consolidation
Hardware Technology Drives Database Technology
The Changing Role of an Oracle Tuning Professional
The Causes of Poor Oracle Performance
The Oracle Tuning Hierarchy
External Hardware Performance Review
Finding Database Bottlenecks
Oracle Instance Tuning
AWR Licensing Options
Tracking your Oracle Option Usage
Oracle SQL Tuning
Conclusion
Chapter 2: Time-Series Oracle Tuning
Managing the Complexity of Oracle
The Pros and Cons of Time Series Tuning
Data Quality
Oracle’s Approach to Workload Thresholds
Signatures, Workloads and Exceptions
Workloads and Predictive Analysis
What is an Oracle Workload?
Using Adaptive Thresholds for Predictive Modeling
Using OEM for Predictive Modeling
Time Series Tuning Guidelines
Scheduling an SGA Reconfiguration
Trend-based Oracle Reconfiguration
When to Trigger a Dynamic Reconfiguration
Approaches to Self-tuning Oracle Databases
Tuning a Constantly Changing Database
Can Oracle Possess Psychic Abilities?
Capturing Time Series Metrics
Customized AWR Tuning Reports
Exception Reporting
Exception Reporting with the AWR
Exception Reporting with dba_hist_filestatxs
Trend Identification with the AWR
Conclusion
References
Chapter 3: The Time Model Tuning Approach
Inside the Oracle Time Model Views
Displaying Session Details with Time Model Data
Real-time Reporting
Time Model Tables in AWR
Time Model Statistics
Conclusion
Chapter 4: Predictive Modeling
Predicting the Future with Oracle
Oracle Data Mining and Predictive Analytics
The Evolution toward Data Mining
Oracle Data Mining and Predictive Analytics
Components of the Oracle Data Miner
Predictive Models Made Easy
Exception Reporting with the AWR
General Trend Identification with the AWR
Correlation Analysis with AWR and ASH
Predictive Modeling with dba_hist_filestatxs
Conclusion
Chapter 5: Oracle Troubleshooting
Introduction to Troubleshooting
Emergency Troubleshooting Methods
Why Oracle Shops Lose Their Databases
The Limitations of the DBA
Case Studies in Oracle Troubleshooting
Accurately Measuring Oracle Performance
Using the BCHR for Performance Tuning
The Danger of Relying on Blanket Ratios
Oracle Bottleneck Analysis
Prerequisites for Bottleneck Analysis
Combining Bottleneck and Ratio Analysis
The Number One Oracle Performance Myth
Troubleshooting Oracle Disk Performance
Automatic Growth
Troubleshooting Critical Storage Problems
Detecting Tablespace Fragmentation
Detecting Object Fragmentation
Correcting Object Fragmentation
Troubleshooting Table Problems
Index Diagnostics
Correcting Space-related Object Performance Problems
Troubleshooting Memory Problems
Getting a Handle on Memory Usage
Understanding the SGA
Gaining Insight Into Memory Use
The Buffer Cache Hit Ratio – Still Worthwhile?
Troubleshooting the Shared Pool
When Does Less Become More?
Troubleshooting the Log Buffer
Log Buffer Related Parameter Issues
Investigating Sorts
Troubleshooting Oracle Sorting
buffer busy waits
Troubleshooting Latches
Troubleshooting I/O Hotspots
Global Basic Queries
Locating Hot I/O Objects
Examining Background Processes
Troubleshooting Rollback Activity
Troubleshooting Problem Sessions
Finding Storage Hogs
Locating Top Resource Sessions
Pinpointing Sessions with Problem SQL
Troubleshooting Problem SQL
What is Bad SQL?
Proactive SQL Troubleshooting
Tricks for SQL Troubleshooting
Troubleshooting Triage
Using Active Session History (ASH) for Troubleshooting
Collecting ASH Wait Information
Using ASH for Troubleshooting
Inside v$active_session_history
Display SQL Wait Details in ASH
Event Wait Analysis with ASH
The Performance Overhead of Collecting ASH Data
Inside the v$session_wait_history
Signature Analysis of ASH Wait Events
Using ASH in OEM
Conclusion
Chapter 6: Inside STATSPACK and AWR
Introduction to Tuning with STATSPACK
Oracle vs. STATSPACK
Inside the AWR tables
Using STATSPACK for Oracle Tuning
The structure of the STATSPACK tables
How STATSPACK Works
Trend Reports with STATSPACK
Inside the AWR Tables
The AWR Data Collection Mechanism
Customizing AWR snapshots
The Mysterious AWR Performance Tables
Inside the AWR Tables
Inside the dba_hist Tables
Database Wait Events in AWR
The AWR Metric Tables
AWR System Statistics
Operating System Statistics in AWR
SQL Statistics in AWR
Segment Statistics in AWR
Datafile I/O Statistics in AWR
Conclusion
Chapter 7: AWR and STATSPACK Configuration
The Differences between STATSPACK and AWR
Installing STATSPACK
Install Prerequisites
STATSPACK vs. AWR
Statistic Management in AWR and STATSPACK
The snap_level Parameter
The session_id
The num_sql
What SQL is Captured?
STATSPACK SQL Collection Thresholds
AWR SQL Collection Thresholds
The stats$sql_summary Table
Installing AWR
The dbms_workload_repository Package
Creating an AWR Report
Report Naming
Conclusion
Chapter 8: Reading an AWR or STATSPACK Report
The Evolution of the Elapsed Time Report
What’s New in STATSPACK and AWR Reports
Generating a STATSPACK Report
Generating an AWR Report
Reading the STATSPACK / AWR Report
STATSPACK / AWR Report Summary
Cache Sizes Section
Load Profile
Instance Efficiency Percentage
All about Oracle Ratios
Top 5 Timed Events Section of a STATSPACK / AWR Report
The Wait Events Section of the AWR / STATSPACK Report
Wait Event Histogram Section
Instance Activity Section in the STATSPACK / AWR Report
Instance Recovery Statistics Section of a STATS/AWR Report
PGA Section
Process Memory Summary Section
Enqueue Statistics
Rollback Segments
Undo Segments
Latch Activity Section of a STATSPACK / AWR report
Dictionary and Library Cache Stats
The Shared Pool Advisory Section
The Time Model Statistics section
The Operating System Statistics Section
The Top SQL Section
The Instance Activity Section
The I/O Reports Section
The Advisory Sections
Buffer Pool Advisory
The Buffer Wait Statistics Section
Conclusion
Chapter 9: Oracle Metrics and v$ Tuning Views
Classifying v$ Views and Metrics
Inside the Oracle Metrics
Inside the v$metric Tables
Database Wait Metrics
Oracle File Metrics
Oracle Service Metrics
The Secret World of the v$ Views
The Active Session History v$ View
Conclusion
Chapter 10: Oracle Wait Event Tuning
The Oracle Wait Event Model
The AWR Wait Event Tables
Why Wait Event Tuning for Oracle?
Addressing Wait Bottlenecks
Systemwide Wait Event Tuning
Not All Events Are Created Equal
Inside the Real-time V$ Wait Events
Inside v$session_wait
Inside v$session_event
Conclusion
Chapter 11: Oracle Tuning Tools
The Evolution of Oracle Tuning Tools
The Spirit of Independence
A Best Practices Approach to Oracle Tuning
The History of Oracle Tuning Techniques
An Automated Approach to SQL Tuning
How Fully Automated SQL Tuning Works
Fully Automated SQL Tuning is not a Panacea
Oracle Trace Analyzer (sqltxplain)
Oracle Lightweight Onboard Monitor (LTOM)
LTOM Features
Oracle Trace Utility
Inside Oracle Event Tracing
Setting an Oracle Trace Event
Accessing Oracle Trace Records with External Tables
Exception Reporting within a Trace File
Loading and Analyzing the 10046 Event
Inside the Oracle 10046 Trace File Output
Conclusions on Tracing
Generating Oracle Test Data
Gathering a Sample Workload in Oracle 11g
Creating Data for Performance Testing
Conclusion
Chapter 12: Server & Network Tuning
Oracle Server Tuning
Outside the Oracle Instance
Oracle Server Bottlenecks
Oracle Server Monitoring
Capturing Server-side Metrics
OS Statistics for the Cost-based Optimizer
OS data inside Oracle views
The Oracle OS Watcher utility
Starting Oracle OS Watcher
Oracle CPU Tuning
Viewing CPU Utilization for Oracle
Identifying High CPU Usage with vmstat
Storing Information from vmstat
Disk I/O and Oracle
Moore’s Law and Disk Speed
Server RAM and Oracle
Oracle and the 64-bit Server Technology
The New Age of Oracle Server Consolidation
Oracle Enterprise Manager and Server Metrics
Server Metrics and SQL Execution
Oracle Network Tuning
Conclusion
Chapter 13: Tuning the I/O Subsystem
Inside Oracle Disk Architecture
The Plague of Large Oracle Disks
Disk Architectures of the 21st Century
RAID Technology and Oracle
RAID 5 is Not for Every Database
Oracle and Direct I/O
Enabling Oracle Direct I/O
Calibrating Disk I/O
Monitoring External Disk I/O
Capturing External iostat Information
Generating iostat Reports
Solutions to Physical Read Waits
Choosing a default blocksize
Using Oracle Multiple Blocksizes
Reducing Data Buffer Waste with multiple blocksizes
Reducing Logical I/O with Multiple Blocksizes
Improving Buffer Efficiency with Multiple Blocksizes
Improving SQL Execution with Multiple Blocksizes
Real World Applications of Multiple Blocksizes
The db_file_multiblock_read_count Parameter
Oracle Blocksize & Index I/O
The Latest Consensus on Using Multiple Blocksizes
Vendor Notes on Oracle Multiple Blocksizes
Reducing Disk I/O with SSD
2010 Market Survey of SSD Vendors for Oracle
Oracle Disk Monitoring
Examining Real-time Disk Statistics
Examining Global I/O
Tracking I/O for Specific Tables
Analyzing Real Time I/O Waits
Collecting Real-Time Disk Wait Events
Find the Current Disk I/O Session Bandits
Measuring Disk I/O Speed
Time Series I/O Wait Analysis
Time Series Monitoring of the Data Buffers
Monitoring Disk I/O with AWR
Conclusion
|
Chapter 14: Oracle Instance Tuning
Inside Instance Tuning
Instance Tuning Comes First
Instance Configuration for High Performance
Automatic Memory Management
Manual RAM allocation vs. AMM
Sizing the Oracle SGA and PGA Regions
Viewing Server RAM Resources
Sizing your SGA
SGA Sizing on a Dedicated Server
RAM and Virtual Memory for Oracle
Finding the High Water Mark of Oracle User Connections .
Determining the Optimal PGA Size
Display PGA Area Sizes
A Script for Estimating Total PGA RAM
Optimizing pga_aggregate_target
Rules for adjusting
Important caveats in PGA management
Sizing your PGA for hash joins
The 11g full hash join
Sizing the PGA for Batch Processing
A case study RAM hash joins
Hidden Parameters for Oracle PGA Regions
Supersizing the PGA
Monitoring Server Resources in MS Windows
OS Kernel Parameters
Server Settings for Windows Servers
Kernel Setting for UNIX and Linux Servers
Oracle Parameter Tuning
Oracle Hidden Parameters
Oracle Parallel Query Parameters
Hidden Parallel Parameters
SQL Optimizer Parameters
Data Buffer Cache Hidden Parameters
Instance Wait Event Tuning
Tuning the Oracle Data Buffer Pools
The Problem of Duplicitous RAM Caches
Why is Oracle Logical I/O So Slow?
Data Block Caching in the SGA
Full Table Caching in Oracle
Oracle Data Buffer Metrics
Using AWR for Buffer Pool Statistics
Oracle’s Seven Data Buffer Hit Ratios
Viewing Information about SGA Performance
AMM and Oracle Instance Tuning
Internals of the Oracle Data Buffers
Finding Hot Blocks inside the Oracle Data Buffers
Viewing the Data Buffer Contents
The Downside of Mega Data Buffers
Allocating Oracle Objects into Multiple RAM data Buffers
Sizing the KEEP Pool
Automating KEEP Pool Assignment
Tuning the RECYCLE Pool
Large Blocks and Oracle Instance Caching
Finding Baselines
Learning Instance Tuning from Performance Benchmarks
Rules for adjusting shared_pool_size
Sizing the Shared Pool with the Oracle Advisory Utility
Rules for Adjusting the Data Buffer Sizes
Monitoring RAM usage
Tracking hash joins
Viewing RAM usage for hash joins in SQL
Conclusion
Chapter 15: Tablespace & Object Tuning
Oracle Tablespace Tuning
Inside Oracle Tablespace Tuning
The Issue of pctfree
The Freelist Unlink Process
The Issue of pctused
Setting Pctfree and Pctused
Freelists and Performance
ASSM and Tablespace Performance
Internal Freelist Management
Characteristics of Bitmap Segment Management
New High Watermark Pointers
Extent Control Header Block
Using ASSM with RAC
Potential Performance Issues with ASSM
Faster SQL with Database Reorganizations
Managing Row Chaining in Oracle
A Summary of Object Tuning Rules
Reorganizing Tables for High Performance
Online Reorganization
Segment Space Growth Prediction
Tuning SQL Access with clustering_factor
Not all Indexes are Used in Range Scans
Rebuilding Indexes
When to Rebuild Indexes
Oracle Parallel DDL
Invoking Parallelism
Parallel DBA Operations
Conclusion
Chapter 16: Inside Oracle SQL Tuning
Introduction to Oracle SQL
The Origin of SQL
Understanding SQL Tuning
Holistic Oracle SQL Tuning
Dealing with Time Constraints
Best Practices for SQL Optimization
Proper Development Environment
Maintaining A SQL Infrastructure
A Release-centric Approach to Holistic Optimization
Oracle 6 – Oracle 7 enhancements
Oracle 8 – Oracle 8i enhancements
Oracle 9i enhancements
Oracle 10g enhancements
Oracle 10g Release 2 enhancements
Oracle 11g enhancements
What is the Best Optimizer Philosophy?
The Persistent SQL Philosophy
The Dynamic SQL Philosophy
Goals of SQL Tuning
Determine Optimal Table Join Order
Remove Unnecessary Large-table Full-table Scans
Locating full-scan operations
Tuning large-table full-table scans
Tuning small-table full-table scans
Optimizing SQL RAM Resources
Cache Small-table Full-table Scans
Verify Optimal Index Usage
Verify Optimal Join Techniques
Tuning by Simplifying SQL Syntax
Roadblocks to SQL Tuning
SQL Profiles
Tracing SQL Execution History
Oracle SQL as a Database Access Method
The rule Hint is still Popular in Oracle 11g
The Library Cache and Oracle SQL Performance
Using cursor_sharing=force
Oracle Cursor Sharing Enhancements
Oracle ISO 99 Table Syntax
Outer Join Syntax
External Tables and SQL
Defining an External Table
Internals of External Tables
Tuning Distributed SQL Queries
Subqueries and SQL
Basic SQL Subqueries
Scalar Subqueries
In-line Views (Subqueries in the from Clause)
Inside Oracle Views
Benefits of Oracle Views
The Downside to Using Views
Combining Hints and Views
Parsing SQL Syntax
Create Executable
Interrogating SQL Execution Plans
Oracle SQL Optimizer Statistics
The Oracle dbms_stats Package
Managing Schema Statistics with dbms_stats
Column Skew and histograms
Automating Histogram Sampling with dbms_stats
Oracle Workload Statistics and SQL Performance
External Costing with the Optimizer
Tuning SQL with Histograms
Determining the Optimal Table Join Order
How is Join Cardinality Estimated?
Oracle Join Elimination
Using Dynamic Sampling
Sampling Table Scans
Oracle Tuning with Hints
When hints appear to be ignored
Oracle Indexes – Is Maintenance Required?
Identifying Problem SQL
Find the Problem Sessions
AWR and SQL Tuning
Viewing Table and Index Access with AWR
Towards Automated SQL Tuning
The Goals of Holistic SQL Tuning
The SQL Tuning Advisor
Using SQL Tuning Advisor Session
Inside the 11g SQL Performance Analyzer
Inside the Oracle 11g SQL Performance Analyzer
Gathering the SQL Tuning Set
Setting the SQL Optimizer Cost Model
Turning on CPU Costing
Tuning SQL with “rownum” Filters
Using rownum for top-n queries
Using rownum with range bound queries
Alternatives to rownum
Using OPQ in SQL
Optimizing Oracle SQL Insert Performance
Blocksize and Insert Performance
Oracle Delete Tuning
Using Bulking for Delete Performance
Oracle Update Tuning
CTAS vs. SQL Update statements
Bulking SQL Updates
Bulking SQL Inserts
Oracle tuning with indexes
SQL Tuning with Indexes
The types of Oracle indexes
The Oracle b-tree index
Creating a b-tree index
Does block size matter?
Tuning SQL with bitmapped indexes
Distinct key values and bitmap indexes!
SQL Tuning with bitmap join indexes
How bitmap join indexes work
Bitmap join index example
When Oracle SQL chooses the wrong index
Beware of the fast fix
Forcing index usage
Why doesn’t Oracle use my index?
Using nls_date_format with date indexes
Managing complex date comparisons in SQL
Using the months_between date function
Using the add_months date function
Using the last_day date function
Using the next_day date function
Using the round date function
Using the trunc date function
Index usage and built-in functions
Finding BIF’s
Tuning SQL with Function-based Indexes (FBI)
Using case statements with a function-based index
Indexing on complex functions
Statistics and function-based indexes
Conclusions on function-based indexes
SQL tuning with regular expression indexes
Indexing on regular expressions
Doing case sensitive searches with indexes
SQL Tuning with Oracle*Text Indexes
Oracle Text Index re-synchronization
Tuning SQL with Index Organized Tables
Testing new Oracle indexes
Testing SQL workloads with invisible indexes
Monitoring index usage
Monitoring for Index Range Scans
Monitoring SQL workload activity
Verifying optimal index usage
Finding indexing opportunities
Find SQL that uses sub-optimal indexes
Finding SQL with excessive I/O
Finding sub-optimal SQL in the library cache
Finding index opportunities in AWR
Locating un-used indexes
Finding un-used indexes in Oracle 8i and earlier
Finding un-used indexes in Oracle 9i
Finding un-used indexes in Oracle 10g and beyond
Dropping un-used indexes
Locating infrequently used indexes
The problem of too many indexes
Determining which index to delete
Large Multi-column Indexes
Row clustering and SQL Performance
Index reorganization and SQL Performance
When rebuilding indexes may help SQL performance
When rebuilding indexes will hurt performance
Choosing candidates for index maintenance
Conclusion
Chapter 17: Oracle Data Warehouse Tuning
Oracle Data Warehouse Tuning
What Does a Data Warehouse Need?
Oracle star transformations and SQL
Bad star transformation Plan
Why Oracle for the Data Warehouse?
Scaling the Oracle Data Warehouse
Parallel Query for Data Warehouses
Oracle Data Warehouse Tuning TPC-H Benchmarks
Tuning Tricks for Oracle Data Warehouse Configuration
Data Warehouse Design for High Performance
Oracle Data Warehouse Evolution
End-user Query Approach
Data Warehouse Tuning Skills
Data Warehouse Project Manager
The Data Warehouse Informaticist
The Warehouse Statistician
The Data Warehouse Oracle Tuning Professional
Conclusion
Chapter 18: OEM Tuning
Introduction to OEM
The New OEM
Tuning with Metrics and Exceptions
Active Session History in Enterprise Manager
Easy Customization of OEM Alerts
Instance Efficiency Metrics
Alerts Notification and Setup
Overview of dbms_scheduler Functions
Throughput Metrics in OEM
OEM Outside the Instance
Exception Tuning Inside Enterprise Manager
Advisor Central in OEM
ADDM Main Screen
ADDM Recommendations
Understanding SQL Advisor Recommendations
The SQL Tuning Advisor Links
The Top SQL Screen
Viewing SQL Details in OEM
The Execution Plan Tab
Current Statistics Tab
Execution History Tab
Tuning History Tab
Oracle SQL Tuning Sets
Creating a SQL Tuning Set
Viewing SQL Tuning Set Details
Using the SQL Access Advisor
New Features of the SQL Advisors
Inside the SQL Access Advisor
The SQL Access Advisor Workload Definition
The SQL Access Advisor Recommendation Options
The SQL Access Advisor Schedule Advisor
The SQL Access Advisor Review
SQL Access Advisor Recommendations
Using the Memory Advisor through OEM
Persistence of Automatically Tuned Values
Automated Maintenance Tasks
Resource Management
Introduction to Online Oracle Tuning Tools
Using Custom Scripts for Oracle Tuning
Shortcomings of OEM
Conclusion
Chapter 19: Oracle RAC and Grid Tuning
Introduction to Tuning with RAC
Oracle RAC in a Nutshell
Oracle Scalability and Grid Technology
First Scale Up with SMP Servers
Next Scale Out with Multiple SMP Servers
Oracle Grid in a Nutshell
Blade Servers and Oracle RAC Tuning
Blade Servers and Oracle App Servers
The Revolution of Cache Fusion
Overview of RAC and Grid Tuning
RAC Load Balancing
Managing Inter-instance Data Block Transfers
Block Spreading
Blocksize Adjustment
Read-only Tablespaces
Parallel Processing and RAC Performance
Conclusion
Index |
