Oracle Performance Survival Guide: A Systematic Approach to Database Optimization

Oracle Performance Survival Guide: A Systematic Approach to Database Optimization

by Guy Harrison

View All Available Formats & Editions

ISBN-10: 0137011954

ISBN-13: 9780137011957

Pub. Date: 10/28/2009

Publisher: Prentice Hall

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


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

Product Details

Prentice Hall
Publication date:
Prentice Hall Professional Oracle Series
Sales rank:
Product dimensions:
6.90(w) x 9.00(h) x 1.60(d)

Table of Contents


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


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


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



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


Chapter 3. Tools of the Trade

Explaining SQL Statements

The Plan Table

Exploiting Cached SQL


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


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


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


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


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



LOB Storage

Oracle Partitioning

Types of Partitions

Composite Partitions

Choosing a Partitioning Strategy

Enterprise Manager Partitioning Advisor


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


Index Clusters

Hash Clusters

Nested Tables

Choosing the Best Indexing Strategy


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


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


Bind Variable Peeking

Adaptive Cursor Sharing

Database Parameters

System Statistics

Collecting Statistics


DBMS_STATS Procedures and Parameters

Setting DBMS_STATS Defaults

Creating Histograms with METHOD_OPT


Partition Statistics

Extended Statistics

Locking Statistics

System Statistics

Exporting and Importing Statistics

Manipulating Statistics


Chapter 8. Execution Plan Management


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


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


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



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


Index Clusters

Materialized Views

Bitmap Join Index

Join Order

Special Joins

Outer Joins

Star Joins

Hierarchical Joins


Simple Subqueries

Correlated Subqueries

Anti-Join Subqueries

Semi-Join Subqueries


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


SET Operations




SET Operations and Their Alternatives


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


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


LOOP Optimization

“Short Circuiting” Expressions

Order of Expressions in IF and CASE Statements


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


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


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


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



Create Table as Select

UPDATE and MERGE Operations

Correlated UPDATEs

Optimizing MERGE

COMMIT Optimization

COMMIT Frequency

Batch and NOWAIT Commit



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


Chapter 16. Latch and Mutex Contention

Overview of Latch and Mutex Architecture

Gets, Spins, and Sleeps


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


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


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


Chapter 19. Optimizing PGA Memory

IO and PGA Memory

PGA Memory Management


Session PGA Limits

Measuring PGA Usage and Efficiency

Session PGA Utilization

Measuring Temporary IO Wait Time

Measuring Work Area Activity


Over-Riding PGA Aggregate Target


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


Part VI: IO Tuning and Clustering

Chapter 21. Disk IO Tuning Fundamentals

Disk IO Concepts

Service Time and Throughput


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


Chapter 22. Advanced IO Techniques

Automatic Storage Management (ASM)

ASM Architecture

ASM Monitoring

ASM Tuning

Solid State Disk (SSD)

Flash-Based SSD


Hybrid SSD

Using SSD for Oracle Databases

The Exadata Storage Server

Database Block Size


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



9780137011957 TOC 9/21/2009

Customer Reviews

Average Review:

Write a Review

and post it to your social network


Most Helpful Customer Reviews

See all customer reviews >