Oracle Performance Survival Guide
A Systematic Approach to Database Optimization
Published on 22. October 2009
Book
Paperback/Softback
768 pages
978-0-13-701195-7 (ISBN)
Description
Oracle Performance Survival Guide
A Systematic Approach to Database Optimization
The fast, complete, start-to-finish guide to optimizing Oracle performance
Oracle Performance Survival Guide offers a structured, systematic, start-to-finish methodology for optimizing Oracle performance as efficiently as possible. Leading Oracle expert Guy Harrison shows how to maximize your tuning investment by focusing on causes rather than symptoms, and by quickly identifying the areas that deliver the greatest "bang for the buck."
Writing for DBAs and developers with all levels of experience, Harrison covers every area of Oracle performance management, from application design through SQL tuning, contention management through memory and physical IO management. He also presents up-to-the-minute guidance for optimizing the performance of the Oracle 11g Release 2.
You'll start by mastering Oracle structured performance tuning principles and tools, including techniques for tracing and monitoring Oracle execution. Harrison illuminates the interaction between applications and databases, guides you through choosing tuning tools, and introduces upfront design techniques that lead to higher-performance applications. He also presents a collection of downloadable scripts for reporting on all aspects of database performance.
Coverage includes
* "Tuning by layers," the most effective, highest-value approach to Oracle performance optimization
* Making the most of Oracle's core tools for tracing, monitoring, and diagnosing performance
* Highly efficient database logical and physical design, indexing, transaction design, and API use
* SQL and PL/SQL tuning, including the use of parallel SQL techniques
* Minimizing contention for locks, latches, shared memory, and other database resources
* Optimizing memory and physical disk IO
* Tuning Real Application Cluster (RAC) databases
guyharrison.net
informit.com/ph
A Systematic Approach to Database Optimization
The fast, complete, start-to-finish guide to optimizing Oracle performance
Oracle Performance Survival Guide offers a structured, systematic, start-to-finish methodology for optimizing Oracle performance as efficiently as possible. Leading Oracle expert Guy Harrison shows how to maximize your tuning investment by focusing on causes rather than symptoms, and by quickly identifying the areas that deliver the greatest "bang for the buck."
Writing for DBAs and developers with all levels of experience, Harrison covers every area of Oracle performance management, from application design through SQL tuning, contention management through memory and physical IO management. He also presents up-to-the-minute guidance for optimizing the performance of the Oracle 11g Release 2.
You'll start by mastering Oracle structured performance tuning principles and tools, including techniques for tracing and monitoring Oracle execution. Harrison illuminates the interaction between applications and databases, guides you through choosing tuning tools, and introduces upfront design techniques that lead to higher-performance applications. He also presents a collection of downloadable scripts for reporting on all aspects of database performance.
Coverage includes
* "Tuning by layers," the most effective, highest-value approach to Oracle performance optimization
* Making the most of Oracle's core tools for tracing, monitoring, and diagnosing performance
* Highly efficient database logical and physical design, indexing, transaction design, and API use
* SQL and PL/SQL tuning, including the use of parallel SQL techniques
* Minimizing contention for locks, latches, shared memory, and other database resources
* Optimizing memory and physical disk IO
* Tuning Real Application Cluster (RAC) databases
guyharrison.net
informit.com/ph
More details
Other editions
Additional editions
E-Book
10/2009
1st Edition
Pearson
€28.99
Available for download
Person
Guy Harrisonhas worked with Oracle databases as a developer, administrator, and performance expert for more than two decades. He is the author of many articles and several books on database technology, including Oracle SQL High Performance Tuning(Prentice Hall) and MySql Stored Procedure Programming(with Steven Feuerstein, O'Reilly). Guy is currently a director of development at Quest Software and is the chief architect of Quest's popular Spotlight product family. He lives in Melbourne, Australia, with his wife Jenni and children Chris, Kate, Mike, and William. You can find Guy on the Web at http://www.guyharrison.net
Content
Preface
Part I: Methods, Concepts, and Tools
Chapter 1. Oracle Performance Tuning: A Methodical Approach
A Brief History of Oracle Performance Tuning
Moving Beyond a Symptomatic Approach
Stage 1: Minimizing the Application Workload
Stage 2: Reducing Contention and Bottlenecks
Stage 3: Reducing Physical IO
Stage 4: Optimizing Disk IO
Summary
Chapter 2. Oracle Architecture and Concepts
The Oracle APIs
Creating the Cursor
Checking for Cached SQL Statements
Parsing the SQL
Associating Bind Variables
Executing the SQL
Fetching Rows
Using Array Fetch
Processing Result Sets
Closing the Cursor
Optimizing Oracle API Calls
The Oracle Query Optimizer
Cost Based Optimization
Optimizer Goal
Optimizer Statistics
Bind Variable Peeking and Adaptive Cursor Sharing
Hints
Outlines, Profiles, and Baselines
Transactions and Locking
Oracle Server Architecture
Instances and Databases
The System Global Area
Data Caching
The Program Global Area
Memory Management
Segments and Files
Tables
Indexes
Blocks, Extents, Segments, and Partitions
Tablespaces and Data Files
Undo Segments
Redo Logs and Archive Logs
Flashback Logs
Server Processes
Background Processes
Real Application Clusters
Summary
Chapter 3. Tools of the Trade
Explaining SQL Statements
The Plan Table
Exploiting Cached SQL
DBMS_XPLAN
Interpreting the Execution Plan
Virtual Indexing
Tracing Oracle Execution
Tracing from Within Your Session
Identifying Your Trace File
Getting Tracing Status
Invoking Trace in Another Session
Tracing by MODULE, ACTION, or SERVICE
Starting a Trace Using a Login Trigger
Finding the Trace File
Other Specialized Traces
Formatting Traces with tkprof
The tkprof Sort Options
Other tkprof Options
Merging Multiple SQL Trace Files
Interpreting Tkprof Output
Execution Plans in tkprof
Wait Statistics and tkprof
Alternatives to tkprof
Using AUTOTRACE in SQL*PLUS
Monitoring the Oracle Server
The V$ table interface
Wait Interface
The Time Model
Integrating the Time Model and Wait Interface
Oracle Enterprise Manager
Spotlight on Oracle
Summary
Part II: Application and Database Design
Chapter 4. Logical and Physical Database Design
Logical Data Modeling
Normalization and Third Normal Form
Data Type Choices
Artificial Keys
Data Warehouse Design
Logical to Physical
Mapping Entities or Classes to Tables
Choosing a Table Type
Data Types and Precisions
Optional Attributes and NULL Values
Column Order
Exploiting Oracle Object Types
Denormalization
Replicating Column Values to Avoid Joins
Summary Tables
Vertical Partitioning
Implementing Denormalization
Star Schema Design
Star Schema Basics
Snowflakes Schemas
Dimension Hierarchies
Aggregations and Materialized Views
Materialized View Best Practices
Physical Storage Options
Manual and Automatic Segment Storage Management
Concurrent Inserts and Freelists
PCTFREE and PCTUSED
Compression
LOB Storage
Oracle Partitioning
Types of Partitions
Composite Partitions
Choosing a Partitioning Strategy
Enterprise Manager Partitioning Advisor
Summary
Chapter 5. Indexing and Clustering
Overview of Oracle Indexing and Clustering
B*-Tree Indexes
Index Selectivity
Unique Indexes
Implicit Indexes
Concatenated Indexes
Index Skip Scans
Guidelines for Concatenated Indexes
Index Merges
Null Values in Indexes
Reverse Key Indexes
Index Compression
Functional Indexes
Foreign Keys and Locking
Indexes and Partitioning
Bitmap Indexes
Features of Bitmap Indexes
Drawbacks of Bitmap Indexes
Bitmap Indexes and Cardinality
Bitmap Index Merge
Bitmap Join Indexes
Index Overhead
Index Organized Tables
Configuring the Overflow Segment
Periodic Rebuild of Index Only Tables
Clustering
Index Clusters
Hash Clusters
Nested Tables
Choosing the Best Indexing Strategy
Summary
Chapter 6. Application Design and Implementation
SQL Statement Management
Optimizing Parsing
Avoiding Unnecessary SQL Executions
The Array Interface
Implementing Array Fetch
Array Insert
Transaction Design
Isolation Levels
Transactions and Locks
Row Level Locking in Oracle
Application Locking Strategies
Using Stored Procedures to Reduce Network Traffic
Summary
Part III: SQL and PL/SQL Tuning
Chapter 7. Optimizing the Optimizer
The Oracle Optimizer
What Is Cost?
Optimizer Goal
Selectivity and Cardinality
Query Transformation
Cost Calculations
Object Statistics
Histograms
Bind Variable Peeking
Adaptive Cursor Sharing
Database Parameters
System Statistics
Collecting Statistics
Using DBMS_STAT
DBMS_STATS Procedures and Parameters
Setting DBMS_STATS Defaults
Creating Histograms with METHOD_OPT
Sampling
Partition Statistics
Extended Statistics
Locking Statistics
System Statistics
Exporting and Importing Statistics
Manipulating Statistics
Summary
Chapter 8. Execution Plan Management
Hints
Using Hints to Change the Access Path
Using Hints to Change the Join Order
Errors in Hint Specifications
Stored Outlines
Creating an Outline to Stabilize a Plan
Hacking an Outline
SQL Tuning Sets
Manually Creating a Tuning Set
Creating Tuning Sets in
Enterprise Manager
SQL Profiles and the SQL Tuning Advisor
Using DBMS_SQLTUNE
Indexing Advice
SQL Tuning in Enterprise Manager
Cross-SQL Tuning with the SQL Access Advisor
SQL Baselines
Creating the Baseline
Evolving the Baseline
Automating and Configuring Baselines
Fixed Baselines
Baseline Management in Oracle
Enterprise Manager
Summary
Chapter 9. Tuning Table Access
Single Value Lookups
Choosing Between Table and Index Scan
Bitmap Indexes and Single Value Lookups
Hash Clusters and Single Value Lookups
Avoiding "Accidental" Table Scans
NOT EQUALS Conditions
Searching for Nulls
Searching for Values That Are NOT NULL
Creating Indexes on NULLable Columns
Unintentionally Disabling an Index with a Function
Functional Indexes
Functional Indexes and Statistics
Virtual Columns
Multicolumn Lookups
Using Concatenated Indexes
Index Merges
Uniqueness and Over-Indexing
Searching for Ranges
Unbounded Range Scan
Bounded Range Scans
Range Lookups
Using the LIKE Operator
Multvalue Single-Column Lookups
Optimizing Necessary Full Table Scans
Lowering the High Water Mark
Optimizing PCTFREE and PCTUSED
Reducing the Row Length
Compressing the Table
Making Database IO More Efficient
Using the SAMPLE Option
Parallel Query
The Fast Full Index Scan
Partitioning
Summary
Chapter 10. Joins and Subqueries
Types of Joins
Join Methods
Nested Loops Join
Sort-Merge Join
Hash Join
Choosing the Right Join Method
Sort-Merge/Hash Versus Nested Loops
Sort-Merge Versus Hash Joins
Optimizing Joins
Optimizing Nested Loops Join
Optimizing Sort-Merge and Hash Joins
Avoiding Joins
Denormalization
Index Clusters
Materialized Views
Bitmap Join Index
Join Order
Special Joins
Outer Joins
Star Joins
Hierarchical Joins
Subqueries
Simple Subqueries
Correlated Subqueries
Anti-Join Subqueries
Semi-Join Subqueries
Summary
Chapter 11. Sorting, Grouping, and Set Operations
Sort Operations
Optimal, One-Pass and Multi-Pass Sorts
Measuring Sort Activity
Tracing Sort Activity
Using an Index to Avoid a Sort
Grouping and Aggregates
Aggregate Operations
Maximums and Minimums
The "Top N" Query
Counting the Rows in a Table
GROUP BY Operations
HAVING Versus WHERE
SET Operations
UNION Versus UNION ALL
INTERSECT
MINUS
SET Operations and Their Alternatives
Summary
Chapter 12. Using and Tuning PL/SQL
Performance Advantages of PL/SQL
A Procedural Approach
Reduction in Network Overhead
Divide and Conquer Massive SQLs
Measuring PL/SQL Performance
Measuring PL/SQL Overhead
Using DBMS_PROFILER
The 11g Hierarchical Profiler
Data Access Optimization
Array Processing and BULK COLLECT
Array Processing for INSERT Statements
Bind Variables and Dynamic SQL
PL/SQL Code Optimization
Tune the SQL First
PLSQL_OPTIMIZE_LEVEL
LOOP Optimization
"Short Circuiting" Expressions
Order of Expressions in IF and CASE Statements
Recursion
The NOCOPY Clause
Associative Arrays
Other Optimizations
Native Compilation
PL/SQL In-Lining
Data Types
Using Java for Computation
Function Caching
DML Trigger Performance
UPDATE OF and WHEN Clauses
Before and After Row Triggers
Summary
Chapter 13. Parallel SQL
Understanding Parallel SQL
Parallel Processes and the Degree of Parallelism
Parallel Slave Pool
Parallel Query IO
Parallel Performance Gains
Deciding When to Use Parallel Processing
Your Server Computer Has Multiple CPUs
The Data to Be Accessed Is on Multiple Disk Drives
The SQL to Be Parallelized is Long Running or Resource-Intensive
The SQL Performs at Least One Full Table, Index, or Partition Scan
There Is Spare Capacity on Your Host
The SQL is Well Tuned
Configuring Parallel Processing
Determining the Degree of Parallelism
Parallel Hints
Parallel Configuration Parameters
Monitoring Parallel SQL
Parallel Explain Plans
Tracing Parallel Execution
The V$PQ_TQSTAT View
Other Statistics
Optimizing Parallel Performance
Start with a SQL That Is Optimized for Serial Execution
Ensure That the SQL Is a Suitable SQL for Parallel Execution
Ensure That the System Is Suitably Configured for Parallel Execution
Make Sure that All Parts of the Execution Plan Are Parallelized
Ensure That the Requested DOP Is Realistic
Monitor the Actual DOP
Check for Skew in Data and Skew in Workload Between Processes
Other Parallel Topics
Parallel Execution in RAC
Parallel Index Lookups
Parallel DML
Parallel DDL
Summary
Chapter 14. DML Tuning
DML Performance Fundamentals
WHERE Clause Optimization
Index Overhead
Trigger Overhead
Referential Integrity
INSERT Specific Optimizations
Array Processing
Direct Path Inserts
Multi-Table Insert
Manual Segment Storage Management (MSSM) and Freelists
Parallel DML
DELETE Operations
TRUNCATE
Partitions
Create Table as Select
UPDATE and MERGE Operations
Correlated UPDATEs
Optimizing MERGE
COMMIT Optimization
COMMIT Frequency
Batch and NOWAIT Commit
NOLOGGING
Summary
Part IV: Minimizing Contention
Chapter 15. Lock Contention
Lock Types and Modes
Waiting for Locks
Monitoring and Analyzing Locks
Lock Wait Statistics
Finding the Responsible SQL
Measuring Lock Contention for Specific Transactions
Tracing Lock Activity
Blockers and Waiters
Application Locking Strategies
When Row Level Locking Fails
Unindexed Foreign Keys
ITL Waits
Bitmap Indexes
Direct Path Inserts
System Locks
The High Water Mark (HW) Enqueue
The Space Transaction (ST) Enqueue
The Sequence Cache (SQ) Enqueue
The User Lock (UL) Enqueue
Other System Locks
Summary
Chapter 16. Latch and Mutex Contention
Overview of Latch and Mutex Architecture
Gets, Spins, and Sleeps
Mutexes
Measuring and Diagnosing Latch/Mutex Contention
Identifying Individual Latches
Finding SQLs and Segments Associated with Latch Waits
Specific Latch/Mutex Scenarios
Library Cache Mutex Waits
Library Cache Pin
Shared Pool Latch
Cache Buffers Chains Latch
Row Cache Objects Latch
Other Latch Scenarios
Is Latch Contention Inevitable?
What About Changing _SPIN_COUNT?
Spin Count, Latch Contention, and Throughput
Setting Spin Count for Individual Latches
Summary
Chapter 17. Shared Memory Contention
Buffer Cache Architecture
Free Buffer Waits
DBWR Direct and Asynchronous IO
Other Remedies for Free Buffer Waits
Recovery Writer (RVWR) Waits
Improving Flashback Log IO
Increasing the Size of the
Flashback Log Buffer
Buffer Busy Waits
Measuring Buffer Busy
Traditional Causes of Buffer Busy Waits
Buffer Busy and Hot Blocks
Redo Log Buffer Waits
Summary
Part V: Optimizing Memory
Chapter 18. Buffer Cache Tuning
Buffer Cache Principles
The LRU List
Table Scan Handling
The CACHE Property
Direct Path IO
Buffer Cache Configuration and Tuning
Monitoring the Buffer Cache
The Buffer Cache Hit Rate
Multiple Buffer Caches
Sizing the Buffer Cache
Automatic Shared Memory Management (ASMM)
Implementing ASMM
Monitoring Resize Operations
Tuning ASMM
Nondefault Pools
Memory Thrashing
Summary
Chapter 19. Optimizing PGA Memory
IO and PGA Memory
PGA Memory Management
PGA_AGGREGATE_TARGET
Session PGA Limits
Measuring PGA Usage and Efficiency
Session PGA Utilization
Measuring Temporary IO Wait Time
Measuring Work Area Activity
Sizing the PGA with V$PGA_TARGET_ADVICE
Over-Riding PGA Aggregate Target
Summary
Chapter 20. Other Memory Management Topics
Optimizing Overall Oracle Memory
IO Wait Times and Memory Optimization
Using Advisories to Distribute PGA/Buffer Cache Memory
Oracle 11G Automatic Memory Management (AMM)
Result Set Cache
Enabling and Configuring the Result Set Cache
Result Cache Statistics
Result Cache Dependencies
Result Cache Latches
PL/SQL Function Cache
Other Memory Optimizations
Sizing the Shared Pool
Large Pool Sizing
Redo Log Buffer
Locking the SGA
Summary
Part VI: IO Tuning and Clustering
Chapter 21. Disk IO Tuning Fundamentals
Disk IO Concepts
Service Time and Throughput
Queuing
Disk Drives: Slow and Getting Slower
Disk Capacity and Data Placement
Oracle IO Architecture
Datafile Single Block Read
Multi Block Read
Direct Path Reads
Temporary Direct Path IO
Data File Write IO
Direct Path Writes
Redo Log IO
Archive Log IO
Flashback IO
Control File IO
Measuring and Monitoring Oracle IO
IO Wait Times
Monitoring Datafile IO
Calibrating IO
Optimizing Datafile IO
Minimizing IO Latency
Maximizing IO Throughput
Striping Strategies
RAID Arrays
Isolating Datafile IO
Redo and Archive Optimization
Alternating and Distributing Logs
Redo and Archive Fine-Grained Striping
Just Say NO to RAID5 for Redo!
Redo Log Sizing
Flashback Logs
Summary
Chapter 22. Advanced IO Techniques
Automatic Storage Management (ASM)
ASM Architecture
ASM Monitoring
ASM Tuning
Solid State Disk (SSD)
Flash-Based SSD
DDR RAM-Based SSD
Hybrid SSD
Using SSD for Oracle Databases
The Exadata Storage Server
Database Block Size
Summary
Chapter 23. Optimizing RAC
RAC Overview
Global Cache Requests
RAC Tuning Principles
Single Instance Tuning and RAC
Measuring Cluster Overhead
Reducing Global Cache Latency
Measuring Global Cache Latency
Examining the Interconnect
Signs of Interconnect Problems
Optimizing the Interconnect
Network Hardware and Protocols
Ethernet Jumbo Frames
UDP Buffer Size
LMS Waits
Cluster Balance
Assessing Cluster Balance
Cluster Balance and Services
RAC Load Balancing Facilities
Minimizing Global Cache Requests
Causes of High Global Cache Request Rates
Measuring Global Cache Request Rates
Techniques for Reducing Global Cache Requests
Summary
Bibliography
9780137011957 TOC 9/21/2009
Part I: Methods, Concepts, and Tools
Chapter 1. Oracle Performance Tuning: A Methodical Approach
A Brief History of Oracle Performance Tuning
Moving Beyond a Symptomatic Approach
Stage 1: Minimizing the Application Workload
Stage 2: Reducing Contention and Bottlenecks
Stage 3: Reducing Physical IO
Stage 4: Optimizing Disk IO
Summary
Chapter 2. Oracle Architecture and Concepts
The Oracle APIs
Creating the Cursor
Checking for Cached SQL Statements
Parsing the SQL
Associating Bind Variables
Executing the SQL
Fetching Rows
Using Array Fetch
Processing Result Sets
Closing the Cursor
Optimizing Oracle API Calls
The Oracle Query Optimizer
Cost Based Optimization
Optimizer Goal
Optimizer Statistics
Bind Variable Peeking and Adaptive Cursor Sharing
Hints
Outlines, Profiles, and Baselines
Transactions and Locking
Oracle Server Architecture
Instances and Databases
The System Global Area
Data Caching
The Program Global Area
Memory Management
Segments and Files
Tables
Indexes
Blocks, Extents, Segments, and Partitions
Tablespaces and Data Files
Undo Segments
Redo Logs and Archive Logs
Flashback Logs
Server Processes
Background Processes
Real Application Clusters
Summary
Chapter 3. Tools of the Trade
Explaining SQL Statements
The Plan Table
Exploiting Cached SQL
DBMS_XPLAN
Interpreting the Execution Plan
Virtual Indexing
Tracing Oracle Execution
Tracing from Within Your Session
Identifying Your Trace File
Getting Tracing Status
Invoking Trace in Another Session
Tracing by MODULE, ACTION, or SERVICE
Starting a Trace Using a Login Trigger
Finding the Trace File
Other Specialized Traces
Formatting Traces with tkprof
The tkprof Sort Options
Other tkprof Options
Merging Multiple SQL Trace Files
Interpreting Tkprof Output
Execution Plans in tkprof
Wait Statistics and tkprof
Alternatives to tkprof
Using AUTOTRACE in SQL*PLUS
Monitoring the Oracle Server
The V$ table interface
Wait Interface
The Time Model
Integrating the Time Model and Wait Interface
Oracle Enterprise Manager
Spotlight on Oracle
Summary
Part II: Application and Database Design
Chapter 4. Logical and Physical Database Design
Logical Data Modeling
Normalization and Third Normal Form
Data Type Choices
Artificial Keys
Data Warehouse Design
Logical to Physical
Mapping Entities or Classes to Tables
Choosing a Table Type
Data Types and Precisions
Optional Attributes and NULL Values
Column Order
Exploiting Oracle Object Types
Denormalization
Replicating Column Values to Avoid Joins
Summary Tables
Vertical Partitioning
Implementing Denormalization
Star Schema Design
Star Schema Basics
Snowflakes Schemas
Dimension Hierarchies
Aggregations and Materialized Views
Materialized View Best Practices
Physical Storage Options
Manual and Automatic Segment Storage Management
Concurrent Inserts and Freelists
PCTFREE and PCTUSED
Compression
LOB Storage
Oracle Partitioning
Types of Partitions
Composite Partitions
Choosing a Partitioning Strategy
Enterprise Manager Partitioning Advisor
Summary
Chapter 5. Indexing and Clustering
Overview of Oracle Indexing and Clustering
B*-Tree Indexes
Index Selectivity
Unique Indexes
Implicit Indexes
Concatenated Indexes
Index Skip Scans
Guidelines for Concatenated Indexes
Index Merges
Null Values in Indexes
Reverse Key Indexes
Index Compression
Functional Indexes
Foreign Keys and Locking
Indexes and Partitioning
Bitmap Indexes
Features of Bitmap Indexes
Drawbacks of Bitmap Indexes
Bitmap Indexes and Cardinality
Bitmap Index Merge
Bitmap Join Indexes
Index Overhead
Index Organized Tables
Configuring the Overflow Segment
Periodic Rebuild of Index Only Tables
Clustering
Index Clusters
Hash Clusters
Nested Tables
Choosing the Best Indexing Strategy
Summary
Chapter 6. Application Design and Implementation
SQL Statement Management
Optimizing Parsing
Avoiding Unnecessary SQL Executions
The Array Interface
Implementing Array Fetch
Array Insert
Transaction Design
Isolation Levels
Transactions and Locks
Row Level Locking in Oracle
Application Locking Strategies
Using Stored Procedures to Reduce Network Traffic
Summary
Part III: SQL and PL/SQL Tuning
Chapter 7. Optimizing the Optimizer
The Oracle Optimizer
What Is Cost?
Optimizer Goal
Selectivity and Cardinality
Query Transformation
Cost Calculations
Object Statistics
Histograms
Bind Variable Peeking
Adaptive Cursor Sharing
Database Parameters
System Statistics
Collecting Statistics
Using DBMS_STAT
DBMS_STATS Procedures and Parameters
Setting DBMS_STATS Defaults
Creating Histograms with METHOD_OPT
Sampling
Partition Statistics
Extended Statistics
Locking Statistics
System Statistics
Exporting and Importing Statistics
Manipulating Statistics
Summary
Chapter 8. Execution Plan Management
Hints
Using Hints to Change the Access Path
Using Hints to Change the Join Order
Errors in Hint Specifications
Stored Outlines
Creating an Outline to Stabilize a Plan
Hacking an Outline
SQL Tuning Sets
Manually Creating a Tuning Set
Creating Tuning Sets in
Enterprise Manager
SQL Profiles and the SQL Tuning Advisor
Using DBMS_SQLTUNE
Indexing Advice
SQL Tuning in Enterprise Manager
Cross-SQL Tuning with the SQL Access Advisor
SQL Baselines
Creating the Baseline
Evolving the Baseline
Automating and Configuring Baselines
Fixed Baselines
Baseline Management in Oracle
Enterprise Manager
Summary
Chapter 9. Tuning Table Access
Single Value Lookups
Choosing Between Table and Index Scan
Bitmap Indexes and Single Value Lookups
Hash Clusters and Single Value Lookups
Avoiding "Accidental" Table Scans
NOT EQUALS Conditions
Searching for Nulls
Searching for Values That Are NOT NULL
Creating Indexes on NULLable Columns
Unintentionally Disabling an Index with a Function
Functional Indexes
Functional Indexes and Statistics
Virtual Columns
Multicolumn Lookups
Using Concatenated Indexes
Index Merges
Uniqueness and Over-Indexing
Searching for Ranges
Unbounded Range Scan
Bounded Range Scans
Range Lookups
Using the LIKE Operator
Multvalue Single-Column Lookups
Optimizing Necessary Full Table Scans
Lowering the High Water Mark
Optimizing PCTFREE and PCTUSED
Reducing the Row Length
Compressing the Table
Making Database IO More Efficient
Using the SAMPLE Option
Parallel Query
The Fast Full Index Scan
Partitioning
Summary
Chapter 10. Joins and Subqueries
Types of Joins
Join Methods
Nested Loops Join
Sort-Merge Join
Hash Join
Choosing the Right Join Method
Sort-Merge/Hash Versus Nested Loops
Sort-Merge Versus Hash Joins
Optimizing Joins
Optimizing Nested Loops Join
Optimizing Sort-Merge and Hash Joins
Avoiding Joins
Denormalization
Index Clusters
Materialized Views
Bitmap Join Index
Join Order
Special Joins
Outer Joins
Star Joins
Hierarchical Joins
Subqueries
Simple Subqueries
Correlated Subqueries
Anti-Join Subqueries
Semi-Join Subqueries
Summary
Chapter 11. Sorting, Grouping, and Set Operations
Sort Operations
Optimal, One-Pass and Multi-Pass Sorts
Measuring Sort Activity
Tracing Sort Activity
Using an Index to Avoid a Sort
Grouping and Aggregates
Aggregate Operations
Maximums and Minimums
The "Top N" Query
Counting the Rows in a Table
GROUP BY Operations
HAVING Versus WHERE
SET Operations
UNION Versus UNION ALL
INTERSECT
MINUS
SET Operations and Their Alternatives
Summary
Chapter 12. Using and Tuning PL/SQL
Performance Advantages of PL/SQL
A Procedural Approach
Reduction in Network Overhead
Divide and Conquer Massive SQLs
Measuring PL/SQL Performance
Measuring PL/SQL Overhead
Using DBMS_PROFILER
The 11g Hierarchical Profiler
Data Access Optimization
Array Processing and BULK COLLECT
Array Processing for INSERT Statements
Bind Variables and Dynamic SQL
PL/SQL Code Optimization
Tune the SQL First
PLSQL_OPTIMIZE_LEVEL
LOOP Optimization
"Short Circuiting" Expressions
Order of Expressions in IF and CASE Statements
Recursion
The NOCOPY Clause
Associative Arrays
Other Optimizations
Native Compilation
PL/SQL In-Lining
Data Types
Using Java for Computation
Function Caching
DML Trigger Performance
UPDATE OF and WHEN Clauses
Before and After Row Triggers
Summary
Chapter 13. Parallel SQL
Understanding Parallel SQL
Parallel Processes and the Degree of Parallelism
Parallel Slave Pool
Parallel Query IO
Parallel Performance Gains
Deciding When to Use Parallel Processing
Your Server Computer Has Multiple CPUs
The Data to Be Accessed Is on Multiple Disk Drives
The SQL to Be Parallelized is Long Running or Resource-Intensive
The SQL Performs at Least One Full Table, Index, or Partition Scan
There Is Spare Capacity on Your Host
The SQL is Well Tuned
Configuring Parallel Processing
Determining the Degree of Parallelism
Parallel Hints
Parallel Configuration Parameters
Monitoring Parallel SQL
Parallel Explain Plans
Tracing Parallel Execution
The V$PQ_TQSTAT View
Other Statistics
Optimizing Parallel Performance
Start with a SQL That Is Optimized for Serial Execution
Ensure That the SQL Is a Suitable SQL for Parallel Execution
Ensure That the System Is Suitably Configured for Parallel Execution
Make Sure that All Parts of the Execution Plan Are Parallelized
Ensure That the Requested DOP Is Realistic
Monitor the Actual DOP
Check for Skew in Data and Skew in Workload Between Processes
Other Parallel Topics
Parallel Execution in RAC
Parallel Index Lookups
Parallel DML
Parallel DDL
Summary
Chapter 14. DML Tuning
DML Performance Fundamentals
WHERE Clause Optimization
Index Overhead
Trigger Overhead
Referential Integrity
INSERT Specific Optimizations
Array Processing
Direct Path Inserts
Multi-Table Insert
Manual Segment Storage Management (MSSM) and Freelists
Parallel DML
DELETE Operations
TRUNCATE
Partitions
Create Table as Select
UPDATE and MERGE Operations
Correlated UPDATEs
Optimizing MERGE
COMMIT Optimization
COMMIT Frequency
Batch and NOWAIT Commit
NOLOGGING
Summary
Part IV: Minimizing Contention
Chapter 15. Lock Contention
Lock Types and Modes
Waiting for Locks
Monitoring and Analyzing Locks
Lock Wait Statistics
Finding the Responsible SQL
Measuring Lock Contention for Specific Transactions
Tracing Lock Activity
Blockers and Waiters
Application Locking Strategies
When Row Level Locking Fails
Unindexed Foreign Keys
ITL Waits
Bitmap Indexes
Direct Path Inserts
System Locks
The High Water Mark (HW) Enqueue
The Space Transaction (ST) Enqueue
The Sequence Cache (SQ) Enqueue
The User Lock (UL) Enqueue
Other System Locks
Summary
Chapter 16. Latch and Mutex Contention
Overview of Latch and Mutex Architecture
Gets, Spins, and Sleeps
Mutexes
Measuring and Diagnosing Latch/Mutex Contention
Identifying Individual Latches
Finding SQLs and Segments Associated with Latch Waits
Specific Latch/Mutex Scenarios
Library Cache Mutex Waits
Library Cache Pin
Shared Pool Latch
Cache Buffers Chains Latch
Row Cache Objects Latch
Other Latch Scenarios
Is Latch Contention Inevitable?
What About Changing _SPIN_COUNT?
Spin Count, Latch Contention, and Throughput
Setting Spin Count for Individual Latches
Summary
Chapter 17. Shared Memory Contention
Buffer Cache Architecture
Free Buffer Waits
DBWR Direct and Asynchronous IO
Other Remedies for Free Buffer Waits
Recovery Writer (RVWR) Waits
Improving Flashback Log IO
Increasing the Size of the
Flashback Log Buffer
Buffer Busy Waits
Measuring Buffer Busy
Traditional Causes of Buffer Busy Waits
Buffer Busy and Hot Blocks
Redo Log Buffer Waits
Summary
Part V: Optimizing Memory
Chapter 18. Buffer Cache Tuning
Buffer Cache Principles
The LRU List
Table Scan Handling
The CACHE Property
Direct Path IO
Buffer Cache Configuration and Tuning
Monitoring the Buffer Cache
The Buffer Cache Hit Rate
Multiple Buffer Caches
Sizing the Buffer Cache
Automatic Shared Memory Management (ASMM)
Implementing ASMM
Monitoring Resize Operations
Tuning ASMM
Nondefault Pools
Memory Thrashing
Summary
Chapter 19. Optimizing PGA Memory
IO and PGA Memory
PGA Memory Management
PGA_AGGREGATE_TARGET
Session PGA Limits
Measuring PGA Usage and Efficiency
Session PGA Utilization
Measuring Temporary IO Wait Time
Measuring Work Area Activity
Sizing the PGA with V$PGA_TARGET_ADVICE
Over-Riding PGA Aggregate Target
Summary
Chapter 20. Other Memory Management Topics
Optimizing Overall Oracle Memory
IO Wait Times and Memory Optimization
Using Advisories to Distribute PGA/Buffer Cache Memory
Oracle 11G Automatic Memory Management (AMM)
Result Set Cache
Enabling and Configuring the Result Set Cache
Result Cache Statistics
Result Cache Dependencies
Result Cache Latches
PL/SQL Function Cache
Other Memory Optimizations
Sizing the Shared Pool
Large Pool Sizing
Redo Log Buffer
Locking the SGA
Summary
Part VI: IO Tuning and Clustering
Chapter 21. Disk IO Tuning Fundamentals
Disk IO Concepts
Service Time and Throughput
Queuing
Disk Drives: Slow and Getting Slower
Disk Capacity and Data Placement
Oracle IO Architecture
Datafile Single Block Read
Multi Block Read
Direct Path Reads
Temporary Direct Path IO
Data File Write IO
Direct Path Writes
Redo Log IO
Archive Log IO
Flashback IO
Control File IO
Measuring and Monitoring Oracle IO
IO Wait Times
Monitoring Datafile IO
Calibrating IO
Optimizing Datafile IO
Minimizing IO Latency
Maximizing IO Throughput
Striping Strategies
RAID Arrays
Isolating Datafile IO
Redo and Archive Optimization
Alternating and Distributing Logs
Redo and Archive Fine-Grained Striping
Just Say NO to RAID5 for Redo!
Redo Log Sizing
Flashback Logs
Summary
Chapter 22. Advanced IO Techniques
Automatic Storage Management (ASM)
ASM Architecture
ASM Monitoring
ASM Tuning
Solid State Disk (SSD)
Flash-Based SSD
DDR RAM-Based SSD
Hybrid SSD
Using SSD for Oracle Databases
The Exadata Storage Server
Database Block Size
Summary
Chapter 23. Optimizing RAC
RAC Overview
Global Cache Requests
RAC Tuning Principles
Single Instance Tuning and RAC
Measuring Cluster Overhead
Reducing Global Cache Latency
Measuring Global Cache Latency
Examining the Interconnect
Signs of Interconnect Problems
Optimizing the Interconnect
Network Hardware and Protocols
Ethernet Jumbo Frames
UDP Buffer Size
LMS Waits
Cluster Balance
Assessing Cluster Balance
Cluster Balance and Services
RAC Load Balancing Facilities
Minimizing Global Cache Requests
Causes of High Global Cache Request Rates
Measuring Global Cache Request Rates
Techniques for Reducing Global Cache Requests
Summary
Bibliography
9780137011957 TOC 9/21/2009