Skip Headers

Oracle® Database Performance Tuning Guide
10g Release 1 (10.1)

Part Number B10752-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to next page
Next
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?

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