Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Oracle Performance?

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 Good 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 Space Consumption
• 5.3 Managing the Automatic Workload Repository
  • 5.3.1 Managing Snapshots
  • 5.3.2 Managing Baselines
  • 5.3.3 Transporting Automatic Workload Repository Data
  • 5.3.4 Using Automatic Workload Repository Views
  • 5.3.5 Generating Automatic Workload Repository Reports
  • 5.3.6 Generating Active Session History Reports

6 Automatic Performance Diagnostics

• 6.1 Introduction to Database Diagnostic Monitoring
• 6.2 Automatic Database Diagnostic Monitor
  • 6.2.1 ADDM Analysis Results
  • 6.2.2 An ADDM Example
  • 6.2.3 Setting Up ADDM
  • 6.2.4 Diagnosing Database Performance Issues with ADDM
  • 6.2.5 Views with ADDM Information

7 Memory Configuration and Use

• 7.1 Understanding Memory Allocation Issues
  • 7.1.1 Oracle Memory Caches
  • 7.1.2 Automatic Shared Memory Management
  • 7.1.3 Dynamically Changing Cache Sizes
  • 7.1.4 Application Considerations
  • 7.1.5 Operating System Memory Use
  • 7.1.6 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

8 I/O Configuration and Design

• 8.1 Understanding I/O
• 8.2 Basic I/O Configuration
  • 8.2.1 Lay Out the Files Using Operating System or Hardware Striping
  • 8.2.2 Manually Distributing I/O
  • 8.2.3 When to Separate Files
  • 8.2.4 Three Sample Configurations
  • 8.2.5 Oracle-Managed Files
  • 8.2.6 Choosing Data Block Size

9 Understanding 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 Solving Operating System Problems
  • 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 Finding System CPU Utilization
  • 9.4.1 Checking Memory Management
  • 9.4.2 Checking I/O Management
  • 9.4.3 Checking Network Management
  • 9.4.4 Checking Process Management

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 SQL*Net Events
  • 10.3.2 buffer busy waits
  • 10.3.3 db file scattered read
  • 10.3.4 db file sequential read
  • 10.3.5 direct path read and direct path read temp
  • 10.3.6 direct path write and direct path write temp
  • 10.3.7 enqueue (enq:) waits
  • 10.3.8 events in wait class other
  • 10.3.9 free buffer waits
  • 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.4 Idle Wait Events

Part IV Optimizing SQL Statements

11 SQL Tuning Overview

• 11.1 Introduction to SQL Tuning
• 11.2 Goals for Tuning
  • 11.2.1 Reduce the Workload
  • 11.2.2 Balance the Workload
  • 11.2.3 Parallelize the Workload
• 11.3 Identifying High-Load SQL
  • 11.3.1 Identifying Resource-Intensive SQL
  • 11.3.2 Gathering Data on the SQL Identified
• 11.4 Automatic SQL Tuning Features
• 11.5 Developing Efficient SQL Statements
  • 11.5.1 Verifying Optimizer Statistics
  • 11.5.2 Reviewing the Execution Plan
  • 11.5.3 Restructuring the SQL Statements
  • 11.5.4 Controlling the Access Path and Join Order with Hints
  • 11.5.5 Restructuring the Indexes
  • 11.5.6 Modifying or Disabling Triggers and Constraints
  • 11.5.7 Restructuring the Data
  • 11.5.8 Maintaining Execution Plans Over Time
  • 11.5.9 Visiting Data as Few Times as Possible

12 Automatic SQL Tuning

• 12.1 Automatic SQL Tuning Overview
  • 12.1.1 Query Optimizer Modes
  • 12.1.2 Types of Tuning Analysis
• 12.2 SQL Tuning Advisor
  • 12.2.1 Input Sources
  • 12.2.2 Tuning Options
  • 12.2.3 Advisor Output
  • 12.2.4 Using SQL Tuning Advisor APIs
• 12.3 SQL Tuning Sets
  • 12.3.1 Creating a SQL Tuning Set
  • 12.3.2 Loading a SQL Tuning Set
  • 12.3.3 Displaying the Contents of a SQL Tuning Set
  • 12.3.4 Modifying a SQL Tuning Set
  • 12.3.5 Transporting a SQL Tuning Set
  • 12.3.6 Dropping a SQL Tuning Set
  • 12.3.7 Additional Operations on SQL Tuning Sets
• 12.4 SQL Profiles
  • 12.4.1 Accepting a SQL Profile
  • 12.4.2 Altering a SQL Profile
  • 12.4.3 Dropping a SQL Profile
• 12.5 SQL Tuning Information Views

13 The Query Optimizer

• 13.1 Optimizer Operations
• 13.2 Choosing an Optimizer Goal
  • 13.2.1 OPTIMIZER_MODE Initialization Parameter
  • 13.2.2 Optimizer SQL Hints for Changing the Query Optimizer Goal
  • 13.2.3 Query Optimizer Statistics in the Data Dictionary
• 13.3 Enabling and Controlling Query Optimizer Features
  • 13.3.1 Enabling Query Optimizer Features
  • 13.3.2 Controlling the Behavior of the Query Optimizer
• 13.4 Understanding the Query Optimizer
  • 13.4.1 Components of the Query Optimizer
  • 13.4.2 Reading and Understanding Execution Plans
• 13.5 Understanding Access Paths for the Query Optimizer
  • 13.5.1 Full Table Scans
  • 13.5.2 Rowid Scans
  • 13.5.3 Index Scans
  • 13.5.4 Cluster Access
  • 13.5.5 Hash Access
  • 13.5.6 Sample Table Scans
  • 13.5.7 How the Query Optimizer Chooses an Access Path
• 13.6 Understanding Joins
  • 13.6.1 How the Query Optimizer Executes Join Statements
  • 13.6.2 How the Query Optimizer Chooses Execution Plans for Joins
  • 13.6.3 Nested Loop Joins
  • 13.6.4 Hash Joins
  • 13.6.5 Sort Merge Joins
  • 13.6.6 Cartesian Joins
  • 13.6.7 Outer Joins

14 Managing Optimizer Statistics

• 14.1 Understanding Statistics
• 14.2 Automatic Statistics Gathering
  • 14.2.1 GATHER_STATS_JOB
  • 14.2.2 Enabling Automatic Statistics Gathering
  • 14.2.3 Considerations When Gathering Statistics
• 14.3 Manual Statistics Gathering
  • 14.3.1 Gathering Statistics with DBMS_STATS Procedures
  • 14.3.2 When to Gather Statistics
• 14.4 System Statistics
  • 14.4.1 Workload Statistics
  • 14.4.2 Noworkload Statistics
• 14.5 Managing Statistics
  • 14.5.1 Restoring Previous Versions of Statistics
  • 14.5.2 Exporting and Importing Statistics
  • 14.5.3 Restoring Statistics Versus Importing or Exporting Statistics
  • 14.5.4 Locking Statistics for a Table or Schema
  • 14.5.5 Setting Statistics
  • 14.5.6 Estimating Statistics with Dynamic Sampling
  • 14.5.7 Handling Missing Statistics
• 14.6 Viewing Statistics
  • 14.6.1 Statistics on Tables, Indexes and Columns
  • 14.6.2 Viewing Histograms

15 Using Indexes and Clusters

• 15.1 Understanding Index Performance
  • 15.1.1 Tuning the Logical Structure
  • 15.1.2 Index Tuning using the SQLAccess Advisor
  • 15.1.3 Choosing Columns and Expressions to Index
  • 15.1.4 Choosing Composite Indexes
  • 15.1.5 Writing Statements That Use Indexes
  • 15.1.6 Writing Statements That Avoid Using Indexes
  • 15.1.7 Re-creating Indexes
  • 15.1.8 Compacting Indexes
  • 15.1.9 Using Nonunique Indexes to Enforce Uniqueness
  • 15.1.10 Using Enabled Novalidated Constraints
• 15.2 Using Function-based Indexes for Performance
• 15.3 Using Partitioned Indexes for Performance
• 15.4 Using Index-Organized Tables for Performance
• 15.5 Using Bitmap Indexes for Performance
• 15.6 Using Bitmap Join Indexes for Performance
• 15.7 Using Domain Indexes for Performance
• 15.8 Using Clusters for Performance
• 15.9 Using Hash Clusters for Performance

16 Using Optimizer Hints

• 16.1 Understanding Optimizer Hints
  • 16.1.1 Types of Hints
  • 16.1.2 Hints by Category
• 16.2 Specifying Hints
  • 16.2.1 Specifying a Full Set of Hints
  • 16.2.2 Specifying a Query Block in a Hint
  • 16.2.3 Specifying Global Table Hints
  • 16.2.4 Specifying Complex Index Hints
• 16.3 Using Hints with Views
  • 16.3.1 Hints and Complex Views
  • 16.3.2 Hints and Mergeable Views
  • 16.3.3 Hints and Nonmergeable Views

17 SQL Access Advisor

• 17.1 Overview of the SQL Access Advisor in the DBMS_ADVISOR Package
  • 17.1.1 Overview of Using the SQL Access Advisor
• 17.2 Using the SQL Access Advisor
  • 17.2.1 Steps for Using the SQL Access Advisor
  • 17.2.2 Privileges Needed to Use the SQL Access Advisor
  • 17.2.3 Setting Up Tasks and Templates
  • 17.2.4 Managing Workloads
  • 17.2.5 Working with Recommendations
  • 17.2.6 Performing a Quick Tune
  • 17.2.7 Managing Tasks
  • 17.2.8 Using SQL Access Advisor Constants
  • 17.2.9 Examples of Using the SQL Access Advisor
• 17.3 Tuning Materialized Views for Fast Refresh and Query Rewrite
  • 17.3.1 DBMS_ADVISOR.TUNE_MVIEW Procedure

18 Using Plan Stability

• 18.1 Using Plan Stability to Preserve Execution Plans
  • 18.1.1 Using Hints with Plan Stability
  • 18.1.2 Storing Outlines
  • 18.1.3 Enabling Plan Stability
  • 18.1.4 Using Supplied Packages to Manage Stored Outlines
  • 18.1.5 Creating Outlines
  • 18.1.6 Using Stored Outlines
  • 18.1.7 Viewing Outline Data
  • 18.1.8 Moving Outline Tables
• 18.2 Using Plan Stability with Query Optimizer Upgrades
  • 18.2.1 Moving from RBO to the Query Optimizer
  • 18.2.2 Moving to a New Oracle Release under the Query Optimizer

19 Using EXPLAIN PLAN

• 19.1 Understanding EXPLAIN PLAN
  • 19.1.1 How Execution Plans Can Change
  • 19.1.2 Minimizing Throw-Away
  • 19.1.3 Looking Beyond Execution Plans
  • 19.1.4 EXPLAIN PLAN Restrictions
• 19.2 The PLAN_TABLE Output Table
• 19.3 Running EXPLAIN PLAN
  • 19.3.1 Identifying Statements for EXPLAIN PLAN
  • 19.3.2 Specifying Different Tables for EXPLAIN PLAN
• 19.4 Displaying PLAN_TABLE Output
  • 19.4.1 Customizing PLAN_TABLE Output
• 19.5 Reading EXPLAIN PLAN Output
• 19.6 Viewing Parallel Execution with EXPLAIN PLAN
  • 19.6.1 Viewing Parallel Queries with EXPLAIN PLAN
• 19.7 Viewing Bitmap Indexes with EXPLAIN PLAN
• 19.8 Viewing Partitioned Objects with EXPLAIN PLAN
  • 19.8.1 Examples of Displaying Range and Hash Partitioning with EXPLAIN PLAN
  • 19.8.2 Examples of Pruning Information with Composite Partitioned Objects
  • 19.8.3 Examples of Partial Partition-wise Joins
  • 19.8.4 Examples of Full Partition-wise Joins
  • 19.8.5 Examples of INLIST ITERATOR and EXPLAIN PLAN
  • 19.8.6 Example of Domain Indexes and EXPLAIN PLAN
• 19.9 PLAN_TABLE Columns

20 Using Application Tracing Tools

• 20.1 End to End Application Tracing
  • 20.1.1 Enabling and Disabling Statistic Gathering for End to End Tracing
  • 20.1.2 Viewing Gathered Statistics for End to End Application Tracing
  • 20.1.3 Enabling and Disabling for End to End Tracing
  • 20.1.4 Viewing Enabled Traces for End to End Tracing
• 20.2 Using the trcsess Utility
  • 20.2.1 Syntax for trcsess
  • 20.2.2 Sample Output of trcsess
• 20.3 Understanding SQL Trace and TKPROF
  • 20.3.1 Understanding the SQL Trace Facility
  • 20.3.2 Understanding TKPROF
• 20.4 Using the SQL Trace Facility and TKPROF
  • 20.4.1 Step 1: Setting Initialization Parameters for Trace File Management
  • 20.4.2 Step 2: Enabling the SQL Trace Facility
  • 20.4.3 Step 3: Formatting Trace Files with TKPROF
  • 20.4.4 Step 4: Interpreting TKPROF Output
  • 20.4.5 Step 5: Storing SQL Trace Facility Statistics
• 20.5 Avoiding Pitfalls in TKPROF Interpretation
  • 20.5.1 Avoiding the Argument Trap
  • 20.5.2 Avoiding the Read Consistency Trap
  • 20.5.3 Avoiding the Schema Trap
  • 20.5.4 Avoiding the Time Trap
  • 20.5.5 Avoiding the Trigger Trap
• 20.6 Sample TKPROF Output
  • 20.6.1 Sample TKPROF Header
  • 20.6.2 Sample TKPROF Body
  • 20.6.3 Sample TKPROF Summary

Part V Real Application Testing

21 Database Replay

• 21.1 Overview of Database Replay
  • 21.1.1 Workload Capture
  • 21.1.2 Workload Preprocessing
  • 21.1.3 Workload Replay
  • 21.1.4 Analysis and Reporting
• 21.2 Capturing a Database Workload
  • 21.2.1 Enabling and Disabling Workload Capture
  • 21.2.2 Prerequisites for Capturing a Database Workload
  • 21.2.3 Workload Capture Options
  • 21.2.4 Workload Capture Restrictions
  • 21.2.5 Capturing a Database Workload Using Enterprise Manager
  • 21.2.6 Monitoring Workload Capture Using Enterprise Manager
  • 21.2.7 Capturing a Database Workload Using APIs
  • 21.2.8 Monitoring Workload Capture Using Views
• 21.3 Analyzing Workload Capture
  • 21.3.1 Generating a Workload Capture Report Using Enterprise Manager
  • 21.3.2 Generating a Workload Capture Report Using APIs
  • 21.3.3 Using a Workload Capture Report

22 SQL Performance Analyzer

• 22.1 Overview of SQL Performance Analyzer
• 22.2 Capturing the SQL Workload

Glossary

Index