Contents

Title and Copyright Information

Send Us Your Comments

Preface

Audience

Organization

Related Documentation

Conventions

Documentation Accessibility

What's New in Oracle Performance?

Part I Writing and Tuning SQL

1 Introduction to the Optimizer

Overview of SQL Processing

Overview of the Optimizer

Features that Require the CBO

Optimizer Operations

Choosing an Optimizer Approach and Goal

How the CBO Optimizes SQL Statements for Fast Response

Understanding the Cost-Based Optimizer

Components of the CBO

Understanding Execution Plans

Understanding Access Paths for the CBO

Full Table Scans

Rowid Scans

Index Scans

Cluster Scans

Hash Scans

Sample Table Scans

How the CBO Chooses an Access Path

Understanding Joins

How the CBO Executes Join Statements

How the CBO Chooses the Join Method

How the CBO Chooses Execution Plans for Join Types

Nested Loop Joins

Hash Joins

Sort Merge Joins

Cartesian Joins

Outer Joins

Setting Cost-Based Optimizer Parameters

Enabling CBO Features

Controlling the Behavior of the CBO

Overview of the Extensible Optimizer

Understanding User-Defined Statistics

Understanding User-Defined Selectivity

Understanding User-Defined Costs

2 Optimizer Operations

How the Optimizer Performs Operations

How the CBO Evaluates IN-List Iterators

How the CBO Evaluates Concatenation

How the CBO Evaluates Remote Operations

How the CBO Executes Distributed Statements

How the CBO Executes Sort Operations

How the CBO Executes Views

How the CBO Evaluates Constants

How the CBO Evaluates the UNION and UNION ALL Operators

How the CBO Evaluates the LIKE Operator

How the CBO Evaluates the IN Operator

How the CBO Evaluates the ANY or SOME Operator

How the CBO Evaluates the ALL Operator

How the CBO Evaluates the BETWEEN Operator

How the CBO Evaluates the NOT Operator

How the CBO Evaluates Transitivity

How the CBO Optimizes Common Subexpressions

How the CBO Evaluates DETERMINISTIC Functions

How the Optimizer Transforms SQL Statements

How the CBO Transforms ORs into Compound Queries

How the CBO Unnests Subqueries

How the CBO Merges Views

How the CBO Pushes Predicates

How the CBO Executes Compound Queries

3 Gathering Optimizer Statistics

Understanding Statistics

Generating Statistics

Getting Statistics for Partitioned Schema Objects

Using the DBMS_STATS Package

Using the ANALYZE Statement

Finding Data Distribution

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

4 Understanding Indexes and Clusters

Understanding 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

Re-creating Indexes

Compacting Indexes

Using Nonunique Indexes to Enforce Uniqueness

Using Enabled Novalidated Constraints

Using Function-based Indexes

Setting Parameters to Use Function-Based Indexes in Queries

Using Index-Organized Tables

Using Bitmap Indexes

When to Use Bitmap Indexes

Using Bitmap Indexes with Good Performance

Initialization Parameters for Bitmap Indexing

Using Bitmap Access Plans on Regular B-tree Indexes

Bitmap Index Restrictions

Using Bitmap Join Indexes

Using Domain Indexes

Using Clusters

Using Hash Clusters

5 Optimizer Hints

Understanding Optimizer Hints

Specifying Hints

Using Optimizer Hints

Hints for Optimization Approaches and Goals

Hints for Access Paths

Hints for Query Transformations

Hints for Join Orders

Hints for Join Operations

Hints for Parallel Execution

Additional Hints

Using Hints with Views

6 Optimizing SQL Statements

Goals for Tuning

Reduce the Workload

Balance the Workload

Parallelize the Workload

Identifying and Gathering Data on Resource-Intensive SQL

Identifying Resource-Intensive SQL

Gathering Data on the SQL Identified

Dynamic Sampling

How Dynamic Sampling Works

When to Use Dynamic Sampling

How to Use Dynamic Sampling to Improve Performance

Overview of SQL Statement Tuning

Verifying Optimizer Statistics

Reviewing the Execution Plan

Restructuring the SQL Statements

Controlling the Access Path and Join Order with Hints

Restructuring the Indexes

Modifying or Disabling Triggers and Constraints

Restructuring the Data

Maintaining Execution Plans Over Time

Visiting Data as Few Times as Possible

7 Using Plan Stability

Using Plan Stability to Preserve Execution Plans

Using Hints with Plan Stability

Storing Outlines

Enabling Plan Stability

Using Supplied Packages to Manage Stored Outlines

Creating Outlines

Using and Editing Stored Outlines

Viewing Outline Data

Moving Outline Tables

Using Plan Stability with the Cost-Based Optimizer

Using Outlines to Move to the Cost-Based Optimizer

Upgrading and the Cost-Based Optimizer

8 Using the Rule-Based Optimizer

Overview of the Rule-Based Optimizer (RBO)

Understanding Access Paths for the RBO

Details of the RBO Access Paths

Choosing Execution Plans for Joins with the RBO

Transforming and Optimizing Statements with the RBO

Transforming ORs into Compound Queries with the RBO

Using Alternative SQL Syntax

Part II SQL-Related Performance Tools

9 Using EXPLAIN PLAN

Understanding EXPLAIN PLAN

How Execution Plans Can Change

Minimizing Throw-Away

Looking Beyond Execution Plans

Creating the PLAN_TABLE Output Table

Running EXPLAIN PLAN

Identifying Statements for EXPLAIN PLAN

Specifying Different Tables for EXPLAIN PLAN

Displaying PLAN_TABLE Output

Reading EXPLAIN PLAN Output

EXPLAIN PLAN Examples

Viewing Bitmap Indexes with EXPLAIN PLAN

Viewing Partitioned Objects with EXPLAIN PLAN

Examples of Displaying Range and Hash Partitioning with EXPLAIN PLAN

Examples of Pruning Information with Composite Partitioned Objects

Examples of Partial Partition-wise Joins

Examples of Full Partition-wise Joins

Examples of INLIST ITERATOR and EXPLAIN PLAN

Example of Domain Indexes and EXPLAIN PLAN

Viewing Parallel Execution with EXPLAIN PLAN

CPU Costing Model

EXPLAIN PLAN Restrictions

PLAN_TABLE Columns

10 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

Avoiding the Argument Trap

Avoiding the Read Consistency Trap

Avoiding the Schema Trap

Avoiding the Time Trap

Avoiding the Trigger Trap

Sample TKPROF Output

Sample TKPROF Header

Sample TKPROF Body

Sample TKPROF Summary

11 Using Autotrace in SQL*Plus

Overview of the Autotrace Report

Configuring the Autotrace Report

Setups Required for the Autotrace Report

Execution Plans for SQL Statements

Database Statistics for SQL Statements

Tracing Statements Examples

Collecting Timing Statistics

Tracing Parallel and Distributed Queries

Monitoring Disk Reads and Buffer Gets

SYSTEM Variables Influencing SQL*Plus Performance

SET APPINFO OFF

SET ARRAYSIZE

SET DEFINE OFF

SET FLUSH OFF

SET SERVEROUTPUT

SET TRIMOUT ON

SET TRIMSPOOL ON

iSQL*Plus Server Statistics Report

Active Statistics

Interpreting Active Statistics

12 Using Oracle Trace

Overview of Oracle Trace

Event Data

Event Sets

Accessing Collected Data

Collecting Oracle Trace Data

Using the Oracle Trace Command-Line Interface

Using Initialization Parameters to Control Oracle Trace

Controlling Oracle Trace Collections from PL/SQL

Accessing Oracle Trace Collection Results

Formatting Oracle Trace Data to Oracle Tables

Running the Oracle Trace Reporting Utility

Oracle Server Events

Data Items Collected for Events

Items Associated with Each Event

Troubleshooting Oracle Trace

Oracle Trace Configuration

Formatter Tables

Part III Creating a Database for Good Performance

13 Building a Database for Performance

Initial Database Creation

Database Creation Using the Installer

Manual Database Creation

Parameters Necessary for Initial Database Creation

The CREATE DATABASE Statement

Running Data Dictionary Scripts

Sizing Redo Log Files

Creating Subsequent Tablespaces

Creating Tables for Good Performance

Data Segment Compression

Loading and Indexing Data

Using SQL*Loader for Good Performance

Efficient Index Creation

Initial Instance Configuration

Configuring Undo Space

Setting up Operating System, Database, and Network Monitoring

14 Memory Configuration and Use

Understanding Memory Allocation Issues

Oracle Memory Caches

Dynamically Changing Cache Sizes

Application Considerations

Operating System Memory Use

Iteration During Configuration

Configuring and Using the Buffer Cache

Using the Buffer Cache Effectively

Sizing the Buffer Cache

Interpreting and Using the Buffer Cache Advisory Statistics

Considering Multiple Buffer Pools

Buffer Pool Data in V$DB_CACHE_ADVICE

Buffer Pool Hit Ratios

Determining Which Segments Have Many Buffers in the Pool

KEEP Pool

RECYCLE Pool

Configuring and Using the Shared Pool and Large Pool

Shared Pool Concepts

Using the Shared Pool Effectively

Sizing the Shared Pool

Interpreting Shared Pool Statistics

Using the Large Pool

Using CURSOR_SPACE_FOR_TIME

Caching Session Cursors

Configuring the Reserved Pool

Keeping Large Objects to Prevent Aging

CURSOR_SHARING for Existing Applications

Configuring and Using the Java Pool

Configuring and Using the Redo Log Buffer

Sizing the Log Buffer

Log Buffer Statistics

Configuring the PGA Working Memory

Automatic PGA Memory Management

Configuring SORT_AREA_SIZE

15 I/O Configuration and Design

Understanding I/O

Designing I/O Layouts

Disk Performance and Reliability

Disk Technology

What Is Disk Contention?

Load Balancing and Striping

Striping and RAID

Balancing Budget, Performance, and Availability

Basic I/O Configuration

Determining Application I/O Characteristics

I/O Configuration Decisions

Know Your I/O System

Match I/O Requirements with the I/O System

Lay Out the Files Using Operating System or Hardware Striping

Manually Distributing I/O

When to Separate Files

Three Sample Configurations

Oracle-Managed Files

Choosing Data Block Size

16 Understanding Operating System Resources

Understanding Operating System Performance Issues

Using Operating System Caches

Memory Usage

Using Process Schedulers

Using Operating System Resource Managers

Solving Operating System Problems

Performance Hints on UNIX-Based Systems

Performance Hints on NT Systems

Performance Hints on Midrange and Mainframe Computers

Understanding CPU

Context Switching

Finding System CPU Utilization

Checking Memory Management

Checking I/O Management

Checking Network Management

Checking Process Management

17 Configuring Instance Recovery Performance

Understanding Instance Recovery

Checkpointing and Cache Recovery

How Checkpoints Affect Performance

Reducing Checkpoint Frequency to Optimize Runtime Performance

Configuring the Duration of Cache Recovery

Initialization Parameters that Influence Cache Recovery Time

Use Fast-Start Checkpointing to Limit Instance Recovery Time

Set LOG_CHECKPOINT_TIMEOUT to Influence the Amount of Redo

Set LOG_CHECKPOINT_INTERVAL to Influence the Amount of Redo

Use Parallel Recovery to Speed up Redo Application

Monitoring Cache Recovery

Monitoring Estimated MTTR: Example Scenario

Calculating Performance Overhead

Calculating Performance Overhead: Example Scenario

Calibrating the MTTR

MTTR Advisory

How MTTR Advisory Works

Enabling MTTR Advisory

Viewing MTTR Advisory

Tuning Transaction Recovery

Using Fast-Start On-Demand Rollback

Using Fast-Start Parallel Rollback

18 Configuring Undo and Temporary Segments

Configuring Undo Segments

Configuring Automatic Undo Management

Configuring Rollback Segments

Configuring Temporary Tablespaces

19 Configuring Shared Servers

Introduction to Shared Server Performance

Configuring the Number of Shared Servers

Identifying Contention Using the Dispatcher-Specific Views

Reducing Contention for Dispatcher Processes

Reducing Contention for Shared Servers

Determining the Optimal Number of Dispatchers and Shared Servers

Part IV System-Related Performance Tools

20 Oracle Tools to Gather Database Statistics

Overview of Tools

Principles of Data Gathering

Interpreting Statistics

Oracle Enterprise Manager Diagnostics Pack

Statspack

V$ Performance Views

Example - Saving File I/O Data

21 Using Statspack

Introduction to Statspack

Statspack Compared with BSTAT/ESTAT

How Statspack Works

Configuring Database Space Requirements for Statspack

Installing Statspack

Interactive Statspack Installation

Batch Mode Statspack Installation

Using Statspack

Taking a Statspack Snapshot

Automating Statistics Gathering

Running a Statspack Performance Report

Configuring the Amount of Data Captured in Statspack

Time Units Used for Wait Events

Event Timings

Managing and Sharing Statspack Performance Data

Oracle Real Application Clusters Considerations with Statspack

Removing Statspack

Statspack Supplied Scripts and Documentation

Scripts for Statspack Installation and Removal

Scripts for Statspack Reporting and Automation

Scripts for Upgrading Statspack

Scripts for Statspack Performance Data Maintenance

Statspack Documentation

Part V Optimizing Instance Performance

22 Instance Tuning

Performance Tuning Principles

Baselines

The Symptoms and the Problems

When to Tune

Performance Tuning Steps

Define the Problem

Examine the Host System

Examine the Oracle Statistics

Implement and Measure Change

Interpreting Oracle Statistics

Examine Load

Using Wait Event Statistics to Drill Down to Bottlenecks

Table of Wait Events and Potential Causes

Additional Statistics

Wait Events

SQL*Net

buffer busy waits

db file scattered read

db file sequential read

direct path read and direct path read (lob)

direct path write

enqueue

free buffer waits

latch free

log buffer space

log file switch

log file sync

rdbms ipc reply

Idle Wait Events

23 Tuning Networks

Understanding Connection Models

Detecting Network Problems

Using Dynamic Performance Views for Network Performance

Understanding Latency and Bandwidth

Solving Network Problems

Finding Network Bottlenecks

Dissecting Network Bottlenecks

Using Array Interfaces

Adjusting Session Data Unit Buffer Size

Using TCP.NODELAY

Using Connection Manager

Part VI Performance-Related Reference Information

24 Dynamic Performance Views for Tuning

Dynamic Performance Tables

Current State Views

Counter/Accumulator Views

Information Views

Description of Dynamic Performance Views

V$DB_OBJECT_CACHE

V$FILESTAT

V$LATCH

V$LATCH_CHILDREN

V$LATCHHOLDER

V$LIBRARYCACHE

V$LIBRARY_CACHE_MEMORY

V$LOCK

V$MTTR_TARGET_ADVICE

V$MYSTAT

V$OPEN_CURSOR

V$PARAMETER and V$SYSTEM_PARAMETER

V$PROCESS

V$ROLLSTAT

V$ROWCACHE

V$SEGMENT_STATISTICS

V$SEGSTAT

V$SEGSTAT_NAME

V$SESSION

V$SESSION_EVENT

V$SESSION_WAIT

V$SESSTAT

V$SHARED_POOL_ADVICE

V$SQL

V$SQL_PLAN

V$SQL_PLAN_STATISTICS

V$SQL_PLAN_STATISTICS_ALL

V$SQLAREA

V$SQLTEXT

V$STATISTICS_LEVEL

V$SYSSTAT

V$SYSTEM_EVENT

V$UNDOSTAT

V$WAITSTAT

A Schemas Used in Performance Examples

PER_ALL_PEOPLE_F Table

RA_CUSTOMERS Table

SO_HEADERS_ALL and SO_HEADERS Tables

MTL_SYSTEM_ITEMS Table

SO_LINES_ALL and SO_LINES Tables

Glossary

Index