Contents

Title and Copyright Information

Send Us Your Comments

Preface

Audience

Organization

Related Documentation

Conventions

Documentation Accessibility

What's New in Oracle Performance?

Oracle Database 10g Release 1 (10.1) New and Updated Features for Performance Tuning

Part I Performance Tuning

1 Performance Tuning Overview

Introduction to Performance Tuning

Performance Planning

Instance Tuning

SQL Tuning

Introduction to Performance Tuning Features and Tools

Automatic Performance Tuning Features

Additional Oracle Tools

Part II Performance Planning

2 Designing and Developing for Performance

Oracle Methodology

Understanding Investment Options

Understanding Scalability

What is Scalability?

System Scalability

Factors Preventing Scalability

System Architecture

Hardware and Software Components

Configuring the Right System Architecture for Your Requirements

Application Design Principles

Simplicity In Application Design

Data Modeling

Table and Index Design

Using Views

SQL Execution Efficiency

Implementing the Application

Trends in Application Development

Workload Testing, Modeling, and Implementation

Sizing Data

Estimating Workloads

Application Modeling

Testing, Debugging, and Validating a Design

Deploying New Applications

Rollout Strategies

Performance Checklist

3 Performance Improvement Methods

The Oracle Performance Improvement Method

Steps in The Oracle Performance Improvement Method

A Sample Decision Process for Performance Conceptual Modeling

Top Ten Mistakes Found in Oracle Systems

Emergency Performance Methods

Steps in the Emergency Performance Method

Part III Optimizing Instance Performance

4 Configuring a Database for Performance

Performance Considerations for Initial Instance Configuration

Initialization Parameters

Configuring Undo Space

Sizing Redo Log Files

Creating Subsequent Tablespaces

Creating and Maintaining Tables for Good Performance

Table Compression

Reclaiming Unused Space

Indexing Data

Performance Considerations for Shared Servers

Identifying Contention Using the Dispatcher-Specific Views

Identifying Contention for Shared Servers

5 Automatic Performance Statistics

Overview of Data Gathering

Database Statistics

Operating System Statistics

Interpreting Statistics

Automatic Workload Repository

Accessing the Automatic Workload Repository with Oracle Enterprise Manager

Managing Snapshot and Baseline Data with APIs

Workload Repository Views

Workload Repository Reports

6 Automatic Performance Diagnostics

Introduction to Database Diagnostic Monitoring

Automatic Database Diagnostic Monitor

ADDM Analysis Results

An ADDM Example

Setting Up ADDM

Accessing ADDM with Oracle Enterprise Manager

Diagnosing Database Performance Issues with ADDM

Views with ADDM Information

7 Memory Configuration and Use

Understanding Memory Allocation Issues

Oracle Memory Caches

Automatic Shared Memory Management

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

Maintaining Connections

Configuring and Using the Redo Log Buffer

Sizing the Log Buffer

Log Buffer Statistics

PGA Memory Management

Configuring Automatic PGA Memory

Configuring OLAP_PAGE_POOL_SIZE

8 I/O Configuration and Design

Understanding I/O

Basic I/O Configuration

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

9 Understanding Operating System Resources

Understanding Operating System Performance Issues

Using Operating System Caches

Memory Usage

Using Operating System Resource Managers

Solving Operating System Problems

Performance Hints on UNIX-Based Systems

Performance Hints on Windows 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

10 Instance Tuning Using Performance Views

Instance 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 Statistics

SQL*Net Events

buffer busy waits

db file scattered read

db file sequential read

direct path read and direct path read temp

direct path write and direct path write temp

enqueue (enq:) waits

free buffer waits

latch events

log file parallel write

library cache pin

library cache lock

log buffer space

log file switch

log file sync

rdbms ipc reply

Idle Wait Events

11 Tuning Networks

Understanding Connection Models

Shared Server Configuration

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 IV Optimizing SQL Statements

12 SQL Tuning Overview

Introduction to SQL Tuning

Goals for Tuning

Reduce the Workload

Balance the Workload

Parallelize the Workload

Identifying High-Load SQL

Identifying Resource-Intensive SQL

Gathering Data on the SQL Identified

Automatic SQL Tuning Features

Developing Efficient SQL Statements

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

13 Automatic SQL Tuning

Automatic SQL Tuning Overview

Query Optimizer Modes

Types of Tuning Analysis

SQL Tuning Advisor

Input Sources

Tuning Options

Advisor Output

Accessing the SQL Tuning Advisor with Oracle Enterprise Manager

Using SQL Tuning Advisor APIs

Managing SQL Profiles with APIs

Accepting a SQL Profile

Altering a SQL Profile

Dropping a SQL Profile

SQL Tuning Sets

Accessing SQL Tuning Sets with Oracle Enterprise Manager

Managing SQL Tuning Sets

SQL Tuning Information Views

14 The Query Optimizer

Optimizer Operations

Choosing an Optimizer Goal

OPTIMIZER_MODE Initialization Parameter

Optimizer SQL Hints for Changing the Query Optimizer Goal

Query Optimizer Statistics in the Data Dictionary

Enabling and Controlling Query Optimizer Features

Enabling Query Optimizer Features

Controlling the Behavior of the Query Optimizer

Understanding the Query Optimizer

Components of the Query Optimizer

Reading and Understanding Execution Plans

Understanding Access Paths for the Query Optimizer

Full Table Scans

Rowid Scans

Index Scans

Cluster Access

Hash Access

Sample Table Scans

How the Query Optimizer Chooses an Access Path

Understanding Joins

How the Query Optimizer Executes Join Statements

How the Query Optimizer Chooses Execution Plans for Joins

Nested Loop Joins

Hash Joins

Sort Merge Joins

Cartesian Joins

Outer Joins

15 Managing Optimizer Statistics

Understanding Statistics

Automatic Statistics Gathering

GATHER_STATS_JOB

Enabling Automatic Statistics Gathering

Considerations When Gathering Statistics

Manual Statistics Gathering

Gathering Statistics with DBMS_STATS Procedures

When to Gather Statistics

System Statistics

Managing Statistics

Restoring Previous Versions of Statistics

Exporting and Importing Statistics

Restoring Statistics Versus Importing or Exporting Statistics

Locking Statistics for a Table or Schema

Setting Statistics

Estimating Statistics with Dynamic Sampling

Handling Missing Statistics

Viewing Statistics

Statistics on Tables, Indexes and Columns

Viewing Histograms

16 Using Indexes and Clusters

Understanding Index Performance

Tuning the Logical Structure

Index Tuning using the SQLAccess Advisor

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 for Performance

Using Partitioned Indexes for Performance

Using Index-Organized Tables for Performance

Using Bitmap Indexes for Performance

Using Bitmap Join Indexes for Performance

Using Domain Indexes for Performance

Using Clusters for Performance

Using Hash Clusters for Performance

17 Optimizer Hints

Understanding Optimizer Hints

Type of Hints

Specifying Hints

Using Hints with Views

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

18 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 Query Optimizer Upgrades

Moving from RBO to the Query Optimizer

Moving to a New Oracle Release under the Query Optimizer

19 Using EXPLAIN PLAN

Understanding EXPLAIN PLAN

How Execution Plans Can Change

Minimizing Throw-Away

Looking Beyond Execution Plans

EXPLAIN PLAN Restrictions

The PLAN_TABLE Output Table

Running EXPLAIN PLAN

Identifying Statements for EXPLAIN PLAN

Specifying Different Tables for EXPLAIN PLAN

Displaying PLAN_TABLE Output

Customizing PLAN_TABLE Output

Reading EXPLAIN PLAN Output

Viewing Parallel Execution with EXPLAIN PLAN

Viewing Parallel Queries with EXPLAIN PLAN

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

PLAN_TABLE Columns

20 Using Application Tracing Tools

End to End Application Tracing

Accessing the End to End Tracing with Oracle Enterprise Manager

Managing End to End Tracing with APIs and Views

Using the trcsess Utility

Syntax for trcsess

Sample Output of trcsess

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

Glossary

Index