Skip Headers

Oracle9i Database Performance Tuning Guide and Reference
Release 2 (9.2)

Part Number A96533-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to next page
View PDF

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