Skip Headers
Oracle® Database Performance Tuning Guide
11g Release 1 (11.1)
Part Number B28274-01
Home
Book List
Index
Master Index
Contact Us
Next
View PDF
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 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
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
Memory Configuration and Use
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
Using the Client Query Result Cache
7.6.1
Using the Result Cache Mode
7.6.2
Managing the Query Result Cache
7.6.3
Accessing Information in the Query Result Cache
8
I/O Configuration and Design
8.1
Understanding 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
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
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
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
Understanding Statistics
13.2
Automatic Optimizer Statistics Collection
13.2.1
Enabling Automatic Optimizer Statistics Collection
13.2.2
Considerations When Gathering Statistics
13.3
Manual Statistics Gathering
13.3.1
Gathering Statistics with DBMS_STATS Procedures
13.3.2
When to Gather Statistics
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
Managing SQL Plan Baselines
15.1.1
Capturing SQL Plan Baselines
15.1.2
Selecting SQL Plan Baselines
15.1.3
Evolving SQL Plan Baselines
15.2
Using SQL Plan Baselines with the SQL Tuning Advisor
15.3
Using Fixed SQL Plan Baselines
15.4
Displaying SQL Plan Baselines
15.5
SQL Management Base
15.5.1
Disk Space Usage
15.5.2
Purging Policy
15.5.3
SQL Management Base Configuration Parameters
15.6
Importing and Exporting SQL Plan Baselines
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
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.2
SQL Tuning Advisor
17.3
Automatic SQL Tuning Advisor
17.3.1
Enabling and Disabling Automatic SQL Tuning
17.3.2
Configuring Automatic SQL Tuning
17.3.3
Viewing Automatic SQL Tuning Reports
17.4
Reactive Tuning Using the SQL Tuning Advisor
17.4.1
Input Sources
17.4.2
Tuning Options
17.4.3
Advisor Output
17.4.4
Running the SQL Tuning Advisor
17.5
SQL Tuning Sets
17.5.1
Creating a SQL Tuning Set
17.5.2
Loading a SQL Tuning Set
17.5.3
Displaying the Contents of a SQL Tuning Set
17.5.4
Modifying a SQL Tuning Set
17.5.5
Transporting a SQL Tuning Set
17.5.6
Dropping a SQL Tuning Set
17.5.7
Additional Operations on SQL Tuning Sets
17.6
SQL Profiles
17.6.1
Accepting a SQL Profile
17.6.2
Altering a SQL Profile
17.6.3
Dropping a SQL Profile
17.7
SQL Tuning Information 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
Understanding 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 and Editing 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
Accessing End to End Tracing with Oracle Enterprise Manager
21.1.2
Managing End to End Tracing with APIs and Views
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.5.5
Avoiding the Trigger Trap
21.6
Sample TKPROF Output
21.6.1
Sample TKPROF Header
21.6.2
Sample TKPROF Body
21.6.3
Sample TKPROF Summary
Part V Real Application Testing
22
Database Replay
22.1
Overview of Database Replay
22.1.1
Workload Capture
22.1.2
Workload Preprocessing
22.1.3
Workload Replay
22.1.4
Analysis and Reporting
22.2
Capturing a Database Workload
22.2.1
Prerequisites for Capturing a Database Workload
22.2.2
Workload Capture Options
22.2.3
Workload Capture Restrictions
22.2.4
Capturing a Database Workload Using Enterprise Manager
22.2.5
Monitoring Workload Capture Using Enterprise Manager
22.2.6
Capturing a Database Workload Using APIs
22.2.7
Monitoring Workload Capture Using Views
22.3
Preprocessing a Database Workload
22.3.1
Preprocessing a Captured Workload Using Enterprise Manager
22.3.2
Preprocessing a Captured Workload Using APIs
22.4
Replaying a Database Workload
22.4.1
Setting Up the Test System
22.4.2
Steps for Replaying a Database Workload
22.4.3
Replaying a Database Workload Using Enterprise Manager
22.4.4
Monitoring Workload Replay Using Enterprise Manager
22.4.5
Replaying a Database Workload Using APIs
22.4.6
Monitoring Workload Replay Using Views
22.5
Analyzing Replayed Workload
22.5.1
Generating a Workload Capture Report Using Enterprise Manager
22.5.2
Generating a Workload Capture Report Using APIs
22.5.3
Using a Workload Capture Report
22.5.4
Generating a Workload Replay Report Using Enterprise Manager
22.5.5
Generating a Workload Replay Report Using APIs
22.5.6
Using a Workload Replay Report
23
SQL Performance Analyzer
23.1
Overview of SQL Performance Analyzer
23.2
Using SQL Performance Analyzer
23.2.1
Capturing the SQL Workload
23.2.2
Setting Up the Test System
23.2.3
Transporting the Workload to the Test System
23.2.4
Creating a SQL Performance Analyzer Task
23.2.5
Executing the SQL Workload Before a Change
23.2.6
Making a Change
23.2.7
Executing the SQL Workload After a Change
23.2.8
Comparing SQL Performance
23.3
SQL Performance Analyzer Views
23.4
Example: Analyzing SQL Performance Impact of a Database Upgrade
Glossary
Index
