Oracle9i Database Performance Guide and Reference
Release 1 (9.0.1)

Part Number A87503-02
Go To Documentation Library
Go To Product List
Book List
Go To Index

Master Index


Go to next page


Title and Copyright Information

Send Us Your Comments


Related Documentation
Documentation Accessibility

What's New in Oracle Performance?

Part I Writing and Tuning SQL

1 Introduction to the Optimizer

Overview of SQL Processing Architecture
Overview of the Optimizer
Steps in Optimizer Operations
Understanding Execution Plans
Choosing an Optimizer Approach and Goal
How the CBO Optimizes SQL Statements for Fast Response
Features that Require the CBO
Understanding the Cost-Based Optimizer (CBO)
Architecture of the CBO
Understanding Access Paths for the CBO
Full Table Scans
Sample Table Scans
Index Scans
Cluster Scans
Hash Scans
How the CBO Chooses an Access Path
Understanding Joins
How the CBO Executes Join Statements
How the CBO Chooses the Join Method
Nested Loop Joins
Nested Loop Outer Joins
Hash Joins
Hash Join Outer Joins
Sort Merge Joins
Sort Merge Outer Joins
Cartesian Joins
Full Outer Joins
How the CBO Chooses Execution Plans for Joins
How the CBO Executes Anti-Joins
How the CBO Executes Semi-Joins
How the CBO Executes Star Queries
Cost-Based Optimizer Parameters
Other CBO Parameters
Overview of the Extensible Optimizer
Understanding User-Defined Statistics
Understanding User-Defined Selectivity
Understanding User-Defined Costs

2 Optimizer Operations

How the Optimizer Performs Operations
How the CBO Evaluates IN-List Iterators
How the CBO Evaluates Concatenation
How the CBO Evaluates Remote Operations
How the CBO Executes Distributed Statements
How the CBO Executes Sort Operations
How the CBO Executes Views
How the CBO Evaluates Constants
How the CBO Evaluates the UNION/UNION ALL Operators
How the CBO Evaluates the LIKE Operator
How the CBO Evaluates the IN Operator
How the CBO Evaluates the ANY or SOME Operator
How the CBO Evaluates the ALL Operator
How the CBO Evaluates the BETWEEN Operator
How the CBO Evaluates the NOT Operator
How the CBO Evaluates Transitivity
How the CBO Optimizes Common Subexpressions
How the CBO Evaluates DETERMINISTIC Functions
How the Optimizer Transforms SQL Statements
How the CBO Transforms ORs into Compound Queries
How the CBO Unnests Subqueries
How the CBO Merges Views
How the CBO Pushes Predicates
How the CBO Executes Compound Queries

3 Gathering Optimizer Statistics

Understanding Statistics
Generating Statistics
Using the DBMS_STATS Package
Using the ANALYZE Statement
Finding Data Distribution
Missing Statistics
Using Statistics
Managing Statistics
Verifying Table Statistics
Verifying Index Statistics
Verifying Column Statistics
Using Histograms
When to Use Histograms
Creating Histograms
Types of Histograms
Viewing Histograms
Verifying Histogram Statistics

4 Understanding Indexes and Clusters

Understanding Indexes
Tuning the Logical Structure
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
Using Index-Organized Tables
Using Bitmap Indexes
When to Use Bitmap Indexes
Using Bitmap Indexes with Good Performance
Initialization Parameters for Bitmap Indexing
Using Bitmap Access Plans on Regular B-tree Indexes
Bitmap Index Restrictions
Using Bitmap Join Indexes
Using Domain Indexes
Using Clusters
Using Hash Clusters

5 Optimizer Hints

Understanding Optimizer Hints
Specifying Hints
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
Using Hints with Views

6 Optimizing SQL Statements

Goals for Tuning
Reduce the Workload
Balance the Workload
Parallelize the Workload
Identifying and Gathering Data on Resource-Intensive SQL
Identifying Resource-Intensive SQL
Gathering Data on the SQL Identified
Overview of SQL Statement Tuning
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

7 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 the Cost-Based Optimizer
Using Outlines to Move to the Cost-Based Optimizer
Upgrading and the Cost-Based Optimizer

8 Using the Rule-Based Optimizer

Overview of the Rule-Based Optimizer (RBO)
Understanding Access Paths for the RBO
Choosing Execution Plans for Joins with the RBO
Transforming and Optimizing Statements with the RBO
Transforming ORs into Compound Queries with the RBO
Using Alternative SQL Syntax

Part II SQL-Related Performance Tools


Understanding EXPLAIN PLAN
How Execution Plans Can Change
Looking Beyond Execution Plans
Creating the PLAN_TABLE Output Table
Identifying Statements for EXPLAIN PLAN
Specifying Different Tables for EXPLAIN PLAN
Displaying PLAN_TABLE Output
Reading EXPLAIN PLAN Output
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
Example of Domain Indexes and EXPLAIN PLAN
EXPLAIN PLAN Restrictions

10 Using SQL Trace and TKPROF

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

11 Using Autotrace in SQL*Plus

Controlling the Autotrace Report
Execution Plan
Tracing Parallel and Distributed Queries
Monitoring Disk Reads and Buffer Gets

12 Using Oracle Trace

Overview of Oracle Trace
Event Data
Event Sets
Accessing Collected Data
Collecting Oracle Trace Data
Using the Oracle Trace Command-Line Interface
Using Initialization Parameters to Control Oracle Trace
Controlling Oracle Trace Collections from PL/SQL
Accessing Oracle Trace Collection Results
Formatting Oracle Trace Data to Oracle Tables
Running the Oracle Trace Reporting Utility
Oracle Server Events
Data Items Collected for Events
Items Associated with Each Event
Troubleshooting Oracle Trace
Oracle Trace Configuration
Formatter Tables

Part III Creating a Database for Good Performance

13 Building a Database for Performance

Initial Database Creation
Database Creation using the Installer
Manual Database Creation
Parameters Necessary for Initial Database Creation
Running Data Dictionary Scripts
Sizing Redo Log Files
Creating Subsequent Tablespaces
Creating Tables for Good Performance
Loading and Indexing Data
Using SQL*Loader for Good Performance
Efficient Index Creation
Initial Instance Configuration
Configuring Rollback Segments
Setting up OS, Database, and Network Monitoring

14 Memory Configuration and Use

Understanding Memory Allocation Issues
Oracle Memory Caches
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 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
Consider using the Large Pool
Consider Caching Session Cursors
Consider Configuring the Reserved Pool
Consider Keeping Large Objects to Prevent Aging
Consider CURSOR_SHARING for Existing Applications
Configuring and Using the Java Pool
Configuring and Using the Redo Log Buffer
Sizing the Log Buffer
Log Buffer Statistics
Configuring the PGA Working Memory
Automatic PGA Memory Management
Configuring SORT_AREA_SIZE
Reducing Total Memory Usage

15 I/O Configuration and Design

Understanding I/O
Basic I/O Configuration
Determining Application I/O Characteristics
I/O Configuration Decisions
Know your I/O System
Match I/O Requirements with the I/O System
Layout 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

16 Understanding Operating System Resources

Understanding Operating System Performance Issues
Using Hardware and Operating System Caches
Evaluating Raw Devices
Using Process Schedulers
Using Operating System Resource Managers
Solving Operating System Problems
Performance Hints on UNIX-Based Systems
Performance Hints on NT Systems
Performance Hints on Mainframe Computers
Understanding CPU
Finding System CPU Utilization

17 Configuring Instance Recovery Performance

Understanding Instance Recovery
Checkpointing and Cache Recovery
How Checkpoints Affect Performance
Reducing Checkpoint Frequency to Optimize Runtime Performance
Configuring the Duration of Cache Recovery
Use Fast-Start Checkpointing to Limit Instance Recovery Time
Set LOG_CHECKPOINT_TIMEOUT to Influence the Number of Redo Logs
Set LOG_CHECKPOINT_INTERVAL to Influence the Number of Redo Logs
Use Parallel Recovery to Speed up Redo Application
Initialization Parameters that Influence Cache Recovery Time
Monitoring Cache Recovery
Tuning Transaction Recovery

18 Configuring Undo and Temporary Segments

Configuring Undo Segments
Configuring Automatic Undo Management
Configuring Rollback Segments
Configuring Temporary Tablespaces

19 Configuring Shared Servers

Configuring the Number of Shared Servers
Identifying Contention Using the Dispatcher-Specific Views
Reducing Contention for Dispatcher Processes
Reducing Contention for Shared Servers
Determining the Optimal Number of Dispatchers and Shared Servers

Part IV System-Related Performance Tools

20 Oracle Tools to Gather Database Statistics

Overview of Tools
Principles of Data Gathering
Interpreting Statistics
Oracle Enterprise Manager Diagnostics Pack
V$ Performance Views

21 Using Statspack

Statspack vs. BSTAT/ESTAT
How Statspack Works
Configuring Statspack
Database Space Requirements for Statspack
Statspack in Dictionary Managed Tablespaces
Statspack in Locally Managed Tablespaces
Installing Statspack
Interactive Statspack Installation
Batch Mode Statspack Installation
Using Statspack
Taking a Statspack Snapshot
Running a Statspack Performance Report
Configuring the Amount of Data Captured in Statspack
Time Units Used for Wait Events
Event Timings
Managing and Sharing Statspack Performance Data
Oracle Real Application Clusters Considerations with Statspack
Removing Statspack
Statspack Supplied Scripts
Scripts for Statspack Installation
Scripts for Statspack Reporting and Automation
Scripts for Upgrading Statspack
Scripts for Statspack Performance Data Maintenance
Scripts for Statspack Documentation
Statspack Limitations

Part V Optimizing Instance Performance

22 Instance Tuning

Performance Tuning Principles
The Symptoms and the Problems
When to Tune
Performance 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
buffer busy waits
db file scattered read
db file sequential read
direct path read
direct path write
free buffer waits
latch free
log buffer space
log file switch
log file sync
rdbms ipc reply
Idle Wait Events

23 Tuning Networks

Understanding Connection Models
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 Connection Manager

Part VI Performance-Related Reference Information

24 Dynamic Performance Views for Tuning

Current State Views
Counter/Accumulator Views
Information Views

A Schemas Used in Performance Examples



Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Index

Master Index