Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Oracle Database Performance Tuning Guide?

Part I Performance Tuning

1 Performance Tuning Overview

• 1.1 Introduction to Performance Tuning
  • 1.1.1 Performance Planning
  • 1.1.2 Instance Tuning
  • 1.1.3 SQL Tuning
• 1.2 Introduction to Performance Tuning Features and Tools
  • 1.2.1 Automatic Performance Tuning Features
  • 1.2.2 Additional Oracle Tools

Part II Performance Planning

2 Designing and Developing for Performance

• 2.1 Oracle Methodology
• 2.2 Understanding Investment Options
• 2.3 Understanding Scalability
  • 2.3.1 What is Scalability?
  • 2.3.2 System Scalability
  • 2.3.3 Factors Preventing Scalability
• 2.4 System Architecture
  • 2.4.1 Hardware and Software Components
  • 2.4.2 Configuring the Right System Architecture for Your Requirements
• 2.5 Application Design Principles
  • 2.5.1 Simplicity In Application Design
  • 2.5.2 Data Modeling
  • 2.5.3 Table and Index Design
  • 2.5.4 Using Views
  • 2.5.5 SQL Execution Efficiency
  • 2.5.6 Implementing the Application
  • 2.5.7 Trends in Application Development
• 2.6 Workload Testing, Modeling, and Implementation
  • 2.6.1 Sizing Data
  • 2.6.2 Estimating Workloads
  • 2.6.3 Application Modeling
  • 2.6.4 Testing, Debugging, and Validating a Design
• 2.7 Deploying New Applications
  • 2.7.1 Rollout Strategies
  • 2.7.2 Performance Checklist

3 Performance Improvement Methods

• 3.1 The Oracle Performance Improvement Method
  • 3.1.1 Steps in The Oracle Performance Improvement Method
  • 3.1.2 A Sample Decision Process for Performance Conceptual Modeling
  • 3.1.3 Top Ten Mistakes Found in Oracle Systems
• 3.2 Emergency Performance Methods
  • 3.2.1 Steps in the Emergency Performance Method

Part III Optimizing Instance Performance

4 Configuring a Database for Performance

• 4.1 Performance Considerations for Initial Instance Configuration
  • 4.1.1 Initialization Parameters
  • 4.1.2 Configuring Undo Space
  • 4.1.3 Sizing Redo Log Files
  • 4.1.4 Creating Subsequent Tablespaces
• 4.2 Creating and Maintaining Tables for Optimal Performance
  • 4.2.1 Table Compression
  • 4.2.2 Reclaiming Unused Space
  • 4.2.3 Indexing Data
• 4.3 Performance Considerations for Shared Servers
  • 4.3.1 Identifying Contention Using the Dispatcher-Specific Views
  • 4.3.2 Identifying Contention for Shared Servers

5 Automatic Performance Statistics

• 5.1 Overview of Data Gathering
  • 5.1.1 Database Statistics
  • 5.1.2 Operating System Statistics
  • 5.1.3 Interpreting Statistics
• 5.2 Overview of the Automatic Workload Repository
  • 5.2.1 Snapshots
  • 5.2.2 Baselines
  • 5.2.3 Adaptive Thresholds
  • 5.2.4 Space Consumption
• 5.3 Managing the Automatic Workload Repository
  • 5.3.1 Managing Snapshots
  • 5.3.2 Managing Baselines
  • 5.3.3 Managing Baseline Templates
  • 5.3.4 Transporting Automatic Workload Repository Data
  • 5.3.5 Using Automatic Workload Repository Views
  • 5.3.6 Generating Automatic Workload Repository Reports
  • 5.3.7 Generating Automatic Workload Repository Compare Periods Reports
  • 5.3.8 Generating Active Session History Reports
  • 5.3.9 Using Active Session History Reports

6 Automatic Performance Diagnostics

• 6.1 Overview of the Automatic Database Diagnostic Monitor
  • 6.1.1 ADDM Analysis
  • 6.1.2 Using ADDM with Oracle Real Application Clusters
  • 6.1.3 ADDM Analysis Results
  • 6.1.4 Reviewing ADDM Analysis Results: Example
• 6.2 Setting Up ADDM
• 6.3 Diagnosing Database Performance Problems with ADDM
  • 6.3.1 Running ADDM in Database Mode
  • 6.3.2 Running ADDM in Instance Mode
  • 6.3.3 Running ADDM in Partial Mode
  • 6.3.4 Displaying an ADDM Report
• 6.4 Views with ADDM Information

7 Configuring and Using Memory

• 7.1 Understanding Memory Allocation Issues
  • 7.1.1 Oracle Memory Caches
  • 7.1.2 Automatic Memory Management
  • 7.1.3 Automatic Shared Memory Management
  • 7.1.4 Dynamically Changing Cache Sizes
  • 7.1.5 Application Considerations
  • 7.1.6 Operating System Memory Use
  • 7.1.7 Iteration During Configuration
• 7.2 Configuring and Using the Buffer Cache
  • 7.2.1 Using the Buffer Cache Effectively
  • 7.2.2 Sizing the Buffer Cache
  • 7.2.3 Interpreting and Using the Buffer Cache Advisory Statistics
  • 7.2.4 Considering Multiple Buffer Pools
  • 7.2.5 Buffer Pool Data in V$DB_CACHE_ADVICE
  • 7.2.6 Buffer Pool Hit Ratios
  • 7.2.7 Determining Which Segments Have Many Buffers in the Pool
  • 7.2.8 KEEP Pool
  • 7.2.9 RECYCLE Pool
• 7.3 Configuring and Using the Shared Pool and Large Pool
  • 7.3.1 Shared Pool Concepts
  • 7.3.2 Using the Shared Pool Effectively
  • 7.3.3 Sizing the Shared Pool
  • 7.3.4 Interpreting Shared Pool Statistics
  • 7.3.5 Using the Large Pool
  • 7.3.6 Using CURSOR_SPACE_FOR_TIME
  • 7.3.7 Caching Session Cursors
  • 7.3.8 Configuring the Reserved Pool
  • 7.3.9 Keeping Large Objects to Prevent Aging
  • 7.3.10 CURSOR_SHARING for Existing Applications
  • 7.3.11 Maintaining Connections
• 7.4 Configuring and Using the Redo Log Buffer
  • 7.4.1 Sizing the Log Buffer
  • 7.4.2 Log Buffer Statistics
• 7.5 PGA Memory Management
  • 7.5.1 Configuring Automatic PGA Memory
  • 7.5.2 Configuring OLAP_PAGE_POOL_SIZE
• 7.6 Managing the Server and Client Result Caches
  • 7.6.1 Managing the Server Result Cache
  • 7.6.2 Managing the Client Result Cache
  • 7.6.3 Specifying Queries for Result Caching
  • 7.6.4 Requirements for the Result Cache
  • 7.6.5 Accessing Result Cache Information

8 I/O Configuration and Design

• 8.1 About I/O
• 8.2 I/O Calibration
  • 8.2.1 Prerequisites for I/O Calibration
  • 8.2.2 Running I/O Calibration
• 8.3 I/O Configuration
  • 8.3.1 Lay Out the Files Using Operating System or Hardware Striping
  • 8.3.2 Manually Distributing I/O
  • 8.3.3 When to Separate Files
  • 8.3.4 Three Sample Configurations
  • 8.3.5 Oracle-Managed Files
  • 8.3.6 Choosing Data Block Size

9 Managing Operating System Resources

• 9.1 Understanding Operating System Performance Issues
  • 9.1.1 Using Operating System Caches
  • 9.1.2 Memory Usage
  • 9.1.3 Using Operating System Resource Managers
• 9.2 Resolving Operating System Issues
  • 9.2.1 Performance Hints on UNIX-Based Systems
  • 9.2.2 Performance Hints on Windows Systems
  • 9.2.3 Performance Hints on HP OpenVMS Systems
• 9.3 Understanding CPU
• 9.4 Resolving CPU Issues
  • 9.4.1 Finding and Tuning CPU Utilization
  • 9.4.2 Managing CPU Resources Using Oracle Database Resource Manager
  • 9.4.3 Managing CPU Resources Using Instance Caging

10 Instance Tuning Using Performance Views

• 10.1 Instance Tuning Steps
  • 10.1.1 Define the Problem
  • 10.1.2 Examine the Host System
  • 10.1.3 Examine the Oracle Statistics
  • 10.1.4 Implement and Measure Change
• 10.2 Interpreting Oracle Statistics
  • 10.2.1 Examine Load
  • 10.2.2 Using Wait Event Statistics to Drill Down to Bottlenecks
  • 10.2.3 Table of Wait Events and Potential Causes
  • 10.2.4 Additional Statistics
• 10.3 Wait Events Statistics
  • 10.3.1 buffer busy waits
  • 10.3.2 db file scattered read
  • 10.3.3 db file sequential read
  • 10.3.4 direct path read and direct path read temp
  • 10.3.5 direct path write and direct path write temp
  • 10.3.6 enqueue (enq:) waits
  • 10.3.7 events in wait class other
  • 10.3.8 free buffer waits
  • 10.3.9 Idle Wait Events
  • 10.3.10 latch events
  • 10.3.11 log file parallel write
  • 10.3.12 library cache pin
  • 10.3.13 library cache lock
  • 10.3.14 log buffer space
  • 10.3.15 log file switch
  • 10.3.16 log file sync
  • 10.3.17 rdbms ipc reply
  • 10.3.18 SQL*Net Events
• 10.4 Real-Time SQL Monitoring
  • 10.4.1 SQL Plan Monitoring
  • 10.4.2 Parallel Execution Monitoring
  • 10.4.3 Generating the SQL Monitor Report
  • 10.4.4 Enabling and Disabling SQL Monitoring
• 10.5 Tuning Instance Recovery Performance: Fast-Start Fault Recovery
  • 10.5.1 About Instance Recovery
  • 10.5.2 Configuring the Duration of Cache Recovery: FAST_START_MTTR_TARGET
  • 10.5.3 Tuning FAST_START_MTTR_TARGET and Using MTTR Advisor

Part IV Optimizing SQL Statements

11 The Query Optimizer

• 11.1 Optimizer Operations
• 11.2 Choosing an Optimizer Goal
  • 11.2.1 OPTIMIZER_MODE Initialization Parameter
  • 11.2.2 Optimizer SQL Hints for Changing the Query Optimizer Goal
  • 11.2.3 Query Optimizer Statistics in the Data Dictionary
• 11.3 Enabling and Controlling Query Optimizer Features
  • 11.3.1 Enabling Query Optimizer Features
  • 11.3.2 Controlling the Behavior of the Query Optimizer
• 11.4 Understanding the Query Optimizer
  • 11.4.1 Components of the Query Optimizer
  • 11.4.2 Reading and Understanding Execution Plans
• 11.5 Understanding Access Paths for the Query Optimizer
  • 11.5.1 Full Table Scans
  • 11.5.2 Rowid Scans
  • 11.5.3 Index Scans
  • 11.5.4 Cluster Access
  • 11.5.5 Hash Access
  • 11.5.6 Sample Table Scans
  • 11.5.7 How the Query Optimizer Chooses an Access Path
• 11.6 Understanding Joins
  • 11.6.1 How the Query Optimizer Executes Join Statements
  • 11.6.2 How the Query Optimizer Chooses Execution Plans for Joins
  • 11.6.3 Nested Loop Joins
  • 11.6.4 Hash Joins
  • 11.6.5 Sort Merge Joins
  • 11.6.6 Cartesian Joins
  • 11.6.7 Outer Joins

12 Using EXPLAIN PLAN

• 12.1 Understanding EXPLAIN PLAN
  • 12.1.1 How Execution Plans Can Change
  • 12.1.2 Minimizing Throw-Away
  • 12.1.3 Looking Beyond Execution Plans
  • 12.1.4 EXPLAIN PLAN Restrictions
• 12.2 The PLAN_TABLE Output Table
• 12.3 Running EXPLAIN PLAN
  • 12.3.1 Identifying Statements for EXPLAIN PLAN
  • 12.3.2 Specifying Different Tables for EXPLAIN PLAN
• 12.4 Displaying PLAN_TABLE Output
  • 12.4.1 Customizing PLAN_TABLE Output
• 12.5 Reading EXPLAIN PLAN Output
• 12.6 Viewing Parallel Execution with EXPLAIN PLAN
  • 12.6.1 Viewing Parallel Queries with EXPLAIN PLAN
• 12.7 Viewing Bitmap Indexes with EXPLAIN PLAN
• 12.8 Viewing Result Cache with EXPLAIN PLAN
• 12.9 Viewing Partitioned Objects with EXPLAIN PLAN
  • 12.9.1 Examples of Displaying Range and Hash Partitioning with EXPLAIN PLAN
  • 12.9.2 Examples of Pruning Information with Composite Partitioned Objects
  • 12.9.3 Examples of Partial Partition-Wise Joins
  • 12.9.4 Examples of Full Partition-wise Joins
  • 12.9.5 Examples of INLIST ITERATOR and EXPLAIN PLAN
  • 12.9.6 Example of Domain Indexes and EXPLAIN PLAN
• 12.10 PLAN_TABLE Columns

13 Managing Optimizer Statistics

• 13.1 Overview of Optimizer Statistics
• 13.2 Managing Automatic Optimizer Statistics Collection
  • 13.2.1 Enabling and Disabling Automatic Optimizer Statistics Collection
  • 13.2.2 Considerations When Gathering Statistics
• 13.3 Gathering Statistics Manually
  • 13.3.1 Gathering Statistics with DBMS_STATS Procedures
  • 13.3.2 Setting Preferences for Manual Statistics Gathering
  • 13.3.3 When to Gather Statistics
  • 13.3.4 Comparing Statistics with DBMS_STATS Functions
• 13.4 System Statistics
  • 13.4.1 Workload Statistics
  • 13.4.2 Noworkload Statistics
• 13.5 Managing Statistics
  • 13.5.1 Pending Statistics
  • 13.5.2 Restoring Previous Versions of Statistics
  • 13.5.3 Exporting and Importing Statistics
  • 13.5.4 Restoring Statistics Versus Importing or Exporting Statistics
  • 13.5.5 Locking Statistics for a Table or Schema
  • 13.5.6 Setting Statistics
  • 13.5.7 Estimating Statistics with Dynamic Sampling
  • 13.5.8 Handling Missing Statistics
• 13.6 Viewing Statistics
  • 13.6.1 Statistics on Tables, Indexes and Columns
  • 13.6.2 Viewing Histograms

14 Using Indexes and Clusters

• 14.1 Understanding Index Performance
  • 14.1.1 Tuning the Logical Structure
  • 14.1.2 Index Tuning using the SQLAccess Advisor
  • 14.1.3 Choosing Columns and Expressions to Index
  • 14.1.4 Choosing Composite Indexes
  • 14.1.5 Writing Statements That Use Indexes
  • 14.1.6 Writing Statements That Avoid Using Indexes
  • 14.1.7 Re-creating Indexes
  • 14.1.8 Compacting Indexes
  • 14.1.9 Using Nonunique Indexes to Enforce Uniqueness
  • 14.1.10 Using Enabled Novalidated Constraints
• 14.2 Using Function-based Indexes for Performance
• 14.3 Using Partitioned Indexes for Performance
• 14.4 Using Index-Organized Tables for Performance
• 14.5 Using Bitmap Indexes for Performance
• 14.6 Using Bitmap Join Indexes for Performance
• 14.7 Using Domain Indexes for Performance
• 14.8 Using Clusters for Performance
• 14.9 Using Hash Clusters for Performance

15 Using SQL Plan Management

• 15.1 Overview of SQL Plan Baselines
  • 15.1.1 Purpose of SQL Plan Baselines
  • 15.1.2 Architecture of SQL Plan Baselines
• 15.2 Managing SQL Plan Baselines
  • 15.2.1 Capturing SQL Plan Baselines
  • 15.2.2 Selecting SQL Plan Baselines
  • 15.2.3 Evolving SQL Plan Baselines
• 15.3 Using SQL Plan Baselines with the SQL Tuning Advisor
• 15.4 Using Fixed SQL Plan Baselines
• 15.5 Displaying SQL Plan Baselines
• 15.6 SQL Management Base
  • 15.6.1 Disk Space Usage
  • 15.6.2 Purging Policy
  • 15.6.3 SQL Management Base Configuration Parameters
• 15.7 Importing and Exporting SQL Plan Baselines
• 15.8 Migrating Stored Outlines to SQL Plan Baselines
  • 15.8.1 Overview of Stored Outline Migration
  • 15.8.2 Preparing for Stored Outline Migration
  • 15.8.3 Migrating Outlines to Utilize SQL Plan Management Features
  • 15.8.4 Migrating Outlines to Preserve Stored Outline Behavior
  • 15.8.5 Performing Follow-Up Tasks After Stored Outline Migration

16 SQL Tuning Overview

• 16.1 Introduction to SQL Tuning
• 16.2 Goals for Tuning
  • 16.2.1 Reduce the Workload
  • 16.2.2 Balance the Workload
  • 16.2.3 Parallelize the Workload
• 16.3 Identifying High-Load SQL
  • 16.3.1 Identifying Resource-Intensive SQL
  • 16.3.2 Gathering Data on the SQL Identified
• 16.4 Automatic SQL Tuning Features
  • 16.4.1 ADDM
  • 16.4.2 SQL Tuning Advisor
  • 16.4.3 SQL Tuning Sets
  • 16.4.4 SQL Access Advisor
• 16.5 Developing Efficient SQL Statements
  • 16.5.1 Verifying Optimizer Statistics
  • 16.5.2 Reviewing the Execution Plan
  • 16.5.3 Restructuring the SQL Statements
  • 16.5.4 Controlling the Access Path and Join Order with Hints
  • 16.5.5 Restructuring the Indexes
  • 16.5.6 Modifying or Disabling Triggers and Constraints
  • 16.5.7 Restructuring the Data
  • 16.5.8 Maintaining Execution Plans Over Time
  • 16.5.9 Visiting Data as Few Times as Possible
• 16.6 Building SQL Test Cases
  • 16.6.1 Creating a Test Case

17 Automatic SQL Tuning

• 17.1 Overview of the Automatic Tuning Optimizer
  • 17.1.1 Statistics Analysis
  • 17.1.2 SQL Profiling
  • 17.1.3 Access Path Analysis
  • 17.1.4 SQL Structure Analysis
  • 17.1.5 Alternative Plan Analysis
• 17.2 Managing the Automatic SQL Tuning Advisor
  • 17.2.1 How Automatic SQL Tuning Works
  • 17.2.2 Enabling and Disabling Automatic SQL Tuning
  • 17.2.3 Configuring Automatic SQL Tuning
  • 17.2.4 Viewing Automatic SQL Tuning Reports
• 17.3 Tuning Reactively with SQL Tuning Advisor
  • 17.3.1 Input Sources
  • 17.3.2 Tuning Options
  • 17.3.3 Advisor Output
  • 17.3.4 Running the SQL Tuning Advisor
• 17.4 Managing SQL Tuning Sets
  • 17.4.1 Creating a SQL Tuning Set
  • 17.4.2 Loading a SQL Tuning Set
  • 17.4.3 Displaying the Contents of a SQL Tuning Set
  • 17.4.4 Modifying a SQL Tuning Set
  • 17.4.5 Transporting a SQL Tuning Set
  • 17.4.6 Dropping a SQL Tuning Set
  • 17.4.7 Additional Operations on SQL Tuning Sets
• 17.5 Managing SQL Profiles
  • 17.5.1 Overview of SQL Profiles
  • 17.5.2 Accepting a SQL Profile
  • 17.5.3 Altering a SQL Profile
  • 17.5.4 Dropping a SQL Profile
  • 17.5.5 Transporting a SQL Profile
• 17.6 SQL Tuning Views

18 SQL Access Advisor

• 18.1 Overview of the SQL Access Advisor
  • 18.1.1 Overview of Using the SQL Access Advisor
• 18.2 Using the SQL Access Advisor
  • 18.2.1 Steps for Using the SQL Access Advisor
  • 18.2.2 Privileges Needed to Use the SQL Access Advisor
  • 18.2.3 Setting Up Tasks and Templates
  • 18.2.4 SQL Access Advisor Workloads
  • 18.2.5 Working with Recommendations
  • 18.2.6 Performing a Quick Tune
  • 18.2.7 Managing Tasks
  • 18.2.8 Using SQL Access Advisor Constants
  • 18.2.9 Examples of Using the SQL Access Advisor
• 18.3 Tuning Materialized Views for Fast Refresh and Query Rewrite
  • 18.3.1 DBMS_ADVISOR.TUNE_MVIEW Procedure

19 Using Optimizer Hints

• 19.1 Overview of Optimizer Hints
  • 19.1.1 Types of Hints
  • 19.1.2 Hints by Category
• 19.2 Specifying Hints
  • 19.2.1 Specifying a Full Set of Hints
  • 19.2.2 Specifying a Query Block in a Hint
  • 19.2.3 Specifying Global Table Hints
  • 19.2.4 Specifying Complex Index Hints
• 19.3 Using Hints with Views
  • 19.3.1 Hints and Complex Views
  • 19.3.2 Hints and Mergeable Views
  • 19.3.3 Hints and Nonmergeable Views

20 Using Plan Stability

• 20.1 Using Plan Stability to Preserve Execution Plans
  • 20.1.1 Using Hints with Plan Stability
  • 20.1.2 Storing Outlines
  • 20.1.3 Enabling Plan Stability
  • 20.1.4 Using Supplied Packages to Manage Stored Outlines
  • 20.1.5 Creating Outlines
  • 20.1.6 Using Stored Outlines
  • 20.1.7 Viewing Outline Data
  • 20.1.8 Moving Outline Tables
• 20.2 Using Plan Stability with Query Optimizer Upgrades
  • 20.2.1 Moving from RBO to the Query Optimizer
  • 20.2.2 Moving to a New Oracle Release under the Query Optimizer

21 Using Application Tracing Tools

• 21.1 End to End Application Tracing
  • 21.1.1 Enabling and Disabling Statistic Gathering for End to End Tracing
  • 21.1.2 Viewing Gathered Statistics for End to End Application Tracing
  • 21.1.3 Enabling and Disabling for End-to-End Tracing
  • 21.1.4 Viewing Enabled Traces for End to End Tracing
• 21.2 Using the trcsess Utility
  • 21.2.1 Syntax for trcsess
  • 21.2.2 Sample Output of trcsess
• 21.3 Understanding SQL Trace and TKPROF
  • 21.3.1 Understanding the SQL Trace Facility
  • 21.3.2 Understanding TKPROF
• 21.4 Using the SQL Trace Facility and TKPROF
  • 21.4.1 Step 1: Setting Initialization Parameters for Trace File Management
  • 21.4.2 Step 2: Enabling the SQL Trace Facility
  • 21.4.3 Step 3: Formatting Trace Files with TKPROF
  • 21.4.4 Step 4: Interpreting TKPROF Output
  • 21.4.5 Step 5: Storing SQL Trace Facility Statistics
• 21.5 Avoiding Pitfalls in TKPROF Interpretation
  • 21.5.1 Avoiding the Argument Trap
  • 21.5.2 Avoiding the Read Consistency Trap
  • 21.5.3 Avoiding the Schema Trap
  • 21.5.4 Avoiding the Time Trap
• 21.6 Sample TKPROF Output
  • 21.6.1 Sample TKPROF Header
  • 21.6.2 Sample TKPROF Body
  • 21.6.3 Sample TKPROF Summary

Glossary

Index