Oracle Tuning


 

ISBN
0-9797951-9-2
ISBN 13:
978-0-9797951-9-0
Library of Congress Number: 2010900994
1,150 pages: Hardcover –  8×10 PD 511
Shelving: Database/Oracle Oracle in-Focus Series:  # 32
Oracle Tuning
The Definitive Reference
Second Edition


Donald K. Burleson
Retail Price $69.95 USD/  £45.95 / $75.95 Canadian

Order now for 40% off ! Only $41.95
(40% off)

Table of Contents:

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

One thought on “Oracle Tuning

  1. Pingback: Oracle Technology Stack

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s