Skip Headers
Oracle® Database Performance Tuning Guide
10
g
Release 2 (10.2)
Part Number B14211-01
Home
Book List
Index
Master Index
Feedback
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 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
Automatic Workload Repository
5.2.1
Accessing the Automatic Workload Repository with Oracle Enterprise Manager
5.2.2
Managing Snapshot and Baseline Data with APIs
5.2.3
Automatic Workload Repository Views
5.2.4
Automatic Workload Repository Reports
5.2.5
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
Accessing ADDM with Oracle Enterprise Manager
6.2.5
Diagnosing Database Performance Issues with ADDM
6.2.6
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 with Oracle Enterprise Manager
12.2.5
Using SQL Tuning Advisor APIs
12.3
SQL Tuning Sets
12.3.1
Using SQL Tuning Sets with Oracle Enterprise Manager
12.3.2
Using SQL Tuning Sets APIs
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
17.4
Managing SQL Access Advisor Tasks Using Enterprise Manager
17.4.1
Step 1: Select the Initial Options
17.4.2
Step 2: Define the Workload Source
17.4.3
Step 3: Choose the Types of Recommendations
17.4.4
Step 4: Set a Schedule
17.4.5
Step 5: Review and Submit Your Selections
17.4.6
Step 6: Examine the Recommendations
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 and Editing 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
Accessing the End to End Tracing with Oracle Enterprise Manager
20.1.2
Managing End to End Tracing with APIs and Views
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
Glossary
Index
