Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

How This Book is Organized

What's New

Related Documents

Conventions

Text

Syntax Diagrams and Notation

Code Examples

Part I Introduction to Tuning

1 Understanding Oracle Performance Tuning

What Is Performance Tuning?

Trade-offs Between Response Time and Throughput

Critical Resources

Effects of Excessive Demand

Adjustments to Relieve Problems

Who Tunes?

Setting Performance Targets

Setting User Expectations

Evaluating Performance

2 Performance Tuning Methods

When Is Tuning Most Effective?

Proactive Tuning While Designing and Developing Systems

Reactive Tuning to Improve Production Systems

Prioritized Tuning Steps

Step 1: Tune the Business Rules

Step 2: Tune the Data Design

Step 3: Tune the Application Design

Step 4: Tune the Logical Structure of the Database

Step 5: Tune Database Operations

Step 6: Tune the Access Paths

Step 7: Tune Memory Allocation

Step 8: Tune I/O and Physical Structure

Step 9: Tune Resource Contention

Step 10: Tune the Underlying Platform(s)

Applying the Tuning Method

Set Clear Goals for Tuning

Create Minimum Repeatable Tests

Test Hypotheses

Keep Records and Automate Testing

Avoid Common Errors

Stop Tuning When Objectives Are Met

Demonstrate Meeting the Objectives

Part II Application Design Tuning for Designers and Developers

3 Application and System Performance Characteristics

Types of Applications

Online Transaction Processing (OLTP)

Decision Support Systems

Multipurpose Applications

Registering Applications

Oracle Configurations

Distributed Systems

Multi-Tier Systems

Oracle Parallel Server

Client/Server Configurations

4 The Optimizer

SQL Processing Architecture

Parser

Optimizer

Row Source Generator

SQL Execution

EXPLAIN PLAN

What Is The Optimizer?

Execution Plan

Choosing an Optimizer Approach and Goal

OPTIMIZER_MODE Initialization Parameter

Statistics in the Data Dictionary

OPTIMIZER_GOAL Parameter of the ALTER SESSION Statement

Changing the Goal with Hints

Cost-Based Optimizer (CBO)

Architecture of the CBO

Features that Require the CBO

Using the CBO

Access Paths for the CBO

How the CBO Chooses an Access Path

CBO Parameters

Parameters Affecting CBO Plans

Parameters Affecting How the Optimizer Uses Indexes

Setting Initialization Parameters

Extensible Optimizer

User-Defined Statistics

User-Defined Selectivity

User-Defined Costs

Rule-Based Optimizer (RBO)

Access Paths for the RBO

Overview of Optimizer Operations

Types of SQL Statements

Optimizer Operations

Optimizing Joins

Optimizing Join Statements

Join Operations

How the Optimizer Chooses the Join Method

Forcing the Join Order

Choosing Execution Plans for Join Statements

Optimizing Anti-Joins and Semi-Joins

Optimizing Star Queries

Optimizing Statements that Use Common Subexpressions

Evaluation of Expressions and Conditions

Constants

LIKE Operator

IN Operator

ANY or SOME Operator

ALL Operator

BETWEEN Operator

NOT Operator

Transitivity

DETERMINISTIC Functions

Transforming and Optimizing Statements

Transforming ORs into Compound Queries

Transforming Complex Statements into Join Statements

Optimizing Statements That Access Views

Optimizing Compound Queries

Optimizing Distributed Statements

5 Using EXPLAIN PLAN

Understanding EXPLAIN PLAN

Creating the Output Table

Displaying PLAN_TABLE Output

Output Table Columns

Bitmap Indexes and EXPLAIN PLAN

EXPLAIN PLAN and Partitioned Objects

Displaying Range and Hash Partitioning with EXPLAIN PLAN

Pruning Information with Composite Partitioned Objects

Partial Partition-wise Joins

Full Partition-wise Joins

INLIST ITERATOR and EXPLAIN PLAN

Domain Indexes and EXPLAIN PLAN

EXPLAIN PLAN Restrictions

6 Using SQL Trace and TKPROF

Understanding SQL Trace and TKPROF

Understanding the SQL Trace Facility

Understanding TKPROF

Using the SQL Trace Facility and TKPROF

Step 1: Setting Initialization Parameters for Trace File Management

Step 2: Enabling the SQL Trace Facility

Step 3: Formatting Trace Files with TKPROF

Step 4: Interpreting TKPROF Output

Step 5: Storing SQL Trace Facility Statistics

Avoiding Pitfalls in TKPROF Interpretation

The Argument Trap

The Read Consistency Trap

The Schema Trap

The Time Trap

The Trigger Trap

TKPROF Output Example

Header

Body

Summary

7 Using Optimizer Hints

Understanding Hints

Specifying Hints

Using Hints

Hints for Optimization Approaches and Goals

Hints for Access Methods

Hints for Join Orders

Hints for Join Operations

Hints for Parallel Execution

Additional Hints

Using Hints with Views

8 Gathering Statistics

Understanding Statistics

Generating Statistics

Using the ANALYZE Statement

Using the DBMS_STATS Package

Statistics Data

Missing Statistics

Using Statistics

Managing Statistics

Verifying Table Statistics

Verifying Index Statistics

Verifying Column Statistics

Using Histograms

When to Use Histograms

Creating Histograms

Types of Histograms

Viewing Histograms

Verifying Histogram Statistics

9 Optimizing SQL Statements

Approaches to SQL Statement Tuning

Restructuring the Indexes

Restructuring the Statement

Modifying or Disabling Triggers

Restructuring the Data

Keeping Statistics Current and Using Plan Stability to Preserve Execution Plans

Tuning Goals

Tuning a Serial SQL Statement

Tuning Parallel Execution

Tuning OLTP Applications

Best Practices

Avoiding Rule-Based Optimizer Techniques

Index Cost

Analyzing Object Statistics

Avoiding Complex Expressions

Avoiding Balloon Tactic for Coding SQL

Handling Complex Logic in the Application

SQL Tuning Tips

Using EXPLAIN PLAN on All Queries

Predicate Collapsing

Tuning for the Typical Case

Disk Reads and Buffer Gets

Using EXISTS versus IN

Trouble Shooting

Tuning Distributed Queries

Remote and Distributed Queries

Distributed Query Restrictions

Transparent Gateways

Optimizing Performance of Distributed Queries

10 Using Plan Stability

Using Plan Stability to Preserve Execution Plans

Hints and Exact Text Matching

Storing Outlines

Enabling Plan Stability

Creating Outlines

Using Stored Outlines

Viewing Outline Data

Using the OUTLN_PKG Package to Manage Stored Outlines

Moving Outline Tables

Plan Stability Procedures for the Cost-Based Optimizer

Using Outlines to Move to the Cost-Based Optimizer

RDBMS Upgrades and the Cost-Based Optimizer

Part III Application Design Tools for Designers and DBAs

11 Overview of Diagnostic Tools

Sources of Data for Tuning

Data Volumes

Online Data Dictionary

Operating System Tools

Dynamic Performance Tables

Oracle Trace and Oracle Trace Data Viewer

SQL Trace Facility

Alert Log

Application Program Output

Users

Initialization Parameter Files

Program Text

Design (Analysis) Dictionary

Comparative Data

Dynamic Performance Views

Oracle and SNMP Support

EXPLAIN PLAN

SQL Trace and TKPROF

Supported Scripts

Application Registration

Oracle Enterprise Manager, Packs, and Applications

Introduction to Oracle Enterprise Manager

Oracle Diagnostics Pack

Oracle Tuning Pack

Oracle Parallel Server Management

Independent Tools

12 Data Access Methods

Using Indexes

When to Create Indexes

Tuning the Logical Structure

Choosing Columns and Expressions to Index

Choosing Composite Indexes

Writing Statements that Use Indexes

Writing Statements that Avoid Using Indexes

Assessing the Value of Indexes

Using Fast Full Index Scans

Re-creating Indexes

Compacting Indexes

Using Nonunique Indexes to Enforce Uniqueness

Using Enabled Novalidated Constraints

Using Function-based Indexes

Function-based Indexes and Index Organized Tables

Using Bitmap Indexes

When to Use Bitmap Indexes

Creating Bitmap Indexes

Initialization Parameters for Bitmap Indexing

Using Bitmap Access Plans on Regular B*-tree Indexes

Estimating Bitmap Index Size

Bitmap Index Restrictions

Using Domain Indexes

Using Clusters

Using Hash Clusters

When to Use Hash Clusters

Creating Hash Clusters

13 Managing Shared SQL and PL/SQL Areas

Comparing SQL Statements and PL/SQL Blocks

Testing for Identical SQL Statements

Aspects of Standardized SQL Formatting

Keeping Shared SQL and PL/SQL in the Shared Pool

Reserving Space for Large Allocations

Preventing Objects from Aging Out

14 Using Oracle Trace

Introduction to Oracle Trace

Using Oracle Trace Data

Using Oracle Trace Manager

Managing Collections

Collecting Event Data

Accessing Collected Data

Using Oracle Trace Data Viewer

Oracle Trace Predefined Data Views

Viewing Oracle Trace Data

SQL Statement Property Page

Details Property Page

Example of Details Property Page

Getting More Information on a Selected Query

Manually Collecting Oracle Trace Data

Using the Oracle Trace Command-Line Interface

Using Initialization Parameters to Control Oracle Trace

Using Stored Procedures to Control Oracle Trace

Oracle Trace Collection Results

Formatting Oracle Trace Data to Oracle Tables

Oracle Trace Statistics Reporting Utility

15 Dynamic Performance Views

Instance-Level Views for Tuning

Session-Level or Transient Views for Tuning

Current Statistic Values and Rates of Change

Finding the Current Value of a Statistic

Finding the Rate of Change of a Statistic

16 Diagnosing System Performance Problems

Tuning Factors for Well Designed Existing Systems

Insufficient CPU

Insufficient Memory

I/O Constraints

Network Constraints

Software Constraints

17 Transaction Modes

Using Discrete Transactions

Deciding When to Use Discrete Transactions

How Discrete Transactions Work

Errors During Discrete Transactions

Using Discrete Transactions

Example

Using Serializable Transactions

Part IV Optimizing Instance Performance

18 Tuning CPU Resources

Understanding CPU Problems

Detecting and Solving CPU Problems

System CPU Utilization

Oracle CPU Utilization

Solving CPU Problems by Changing System Architectures

Single Tier to Two-Tier

Multi-Tier: Using Smaller Client Machines

Two-Tier to Three-Tier

Three-Tier

Oracle Parallel Server

19 Tuning Memory Allocation

Understanding Memory Allocation Issues

Detecting Memory Allocation Problems

Solving Memory Allocation Problems

Tuning Operating System Memory Requirements

Tuning the Redo Log Buffer

Tuning Private SQL and PL/SQL Areas

Tuning the Shared Pool

Tuning the Buffer Cache

Tuning Multiple Buffer Pools

Tuning Sort Areas

Reallocating Memory

Reducing Total Memory Usage

20 Tuning I/O

Understanding I/O Problems

Tuning I/O: Top Down and Bottom Up

Analyzing I/O Requirements

Planning File Storage

Choosing Data Block Size

Evaluating Device Bandwidth

Detecting I/O Problems

Checking System I/O Utilization

Checking Oracle I/O Utilization

Solving I/O Problems

Reducing Disk Contention by Distributing I/O

Striping Disks

Avoiding Dynamic Space Management

Tuning Sorts

Tuning Checkpoint Activity

Tuning LGWR and DBWR I/O

Tuning Backup and Restore Operations

Configuring the Large Pool

21 Tuning Resource Contention

Understanding Contention Issues

Detecting Contention Problems

Solving Contention Problems

Reducing Contention for Rollback Segments

Reducing Contention for Multi-Threaded Servers

Reducing Contention for Parallel Execution Servers

Reducing Contention for Redo Log Buffer Latches

Reducing Contention for the LRU Latch

Reducing Free List Contention

22 Tuning Networks

Understanding Connection Models

Detecting Network Problems

Using Dynamic Performance Views

Understanding Latency and Bandwidth

Solving Network Problems

Finding Bottlenecks

Dissecting Bottlenecks

Using Array Interfaces

Adjusting Session Data Unit Buffer Size

Using TCP.NODELAY

Using Connection Manager

23 Tuning the Operating System

Understanding Operating System Performance Issues

Operating System and Hardware Caches

Raw Devices

Process Schedulers

Operating System Resource Managers

Detecting Operating System Problems

Solving Operating System Problems

Performance on UNIX-Based Systems

Performance on NT Systems

Performance on Mainframe Computers

24 Tuning Instance Recovery Performance

Understanding Instance Recovery

How Oracle Applies Redo Log Information

Trade-offs of Minimizing Recovery Duration

Tuning the Duration of Instance and Crash Recovery

Using Initialization Parameters to Influence Recovery Time

Using Redo Log Size to Influence Checkpointing Frequency

Using SQL Statements to Initiate Checkpoints

Monitoring Instance Recovery

Tuning the Phases of Instance Recovery

Tuning the Rolling Forward Phase

Tuning the Rolling Back Phase

Index