Oracle8i Designing and Tuning for Performance
Release 2 (8.1.6)







Title and Copyright Information

Send Us Your Comments


Intended Audience
How This Book is Organized
What's New
Related Documents
Syntax Diagrams and Notation
Code Examples

Part I Introduction to Tuning

1 Understanding Oracle Performance Tuning

What Is Performance Tuning?
Trade-offs Between Response Time and Throughput
Critical Resources
Effects of Excessive Demand
Adjustments to Relieve Problems
Who Tunes?
Setting Performance Targets
Setting User Expectations
Evaluating Performance

2 Performance Tuning Methods

When Is Tuning Most Effective?
Proactive Tuning While Designing and Developing Systems
Reactive Tuning to Improve Production Systems
Prioritized Tuning Steps
Step 1: Tune the Business Rules
Step 2: Tune the Data Design
Step 3: Tune the Application Design
Step 4: Tune the Logical Structure of the Database
Step 5: Tune Database Operations
Step 6: Tune the Access Paths
Step 7: Tune Memory Allocation
Step 8: Tune I/O and Physical Structure
Step 9: Tune Resource Contention
Step 10: Tune the Underlying Platform(s)
Applying the Tuning Method
Set Clear Goals for Tuning
Create Minimum Repeatable Tests
Test Hypotheses
Keep Records and Automate Testing
Avoid Common Errors
Stop Tuning When Objectives Are Met
Demonstrate Meeting the Objectives

Part II Application Design Tuning for Designers and Developers

3 Application and System Performance Characteristics

Types of Applications
Online Transaction Processing (OLTP)
Decision Support Systems
Multipurpose Applications
Registering Applications
Oracle Configurations
Distributed Systems
Multi-Tier Systems
Oracle Parallel Server
Client/Server Configurations

4 The Optimizer

SQL Processing Architecture
Row Source Generator
SQL Execution
What Is The Optimizer?
Execution Plan
Choosing an Optimizer Approach and Goal
OPTIMIZER_MODE Initialization Parameter
Statistics in the Data Dictionary
OPTIMIZER_GOAL Parameter of the ALTER SESSION Statement
Changing the Goal with Hints
Cost-Based Optimizer (CBO)
Architecture of the CBO
Features that Require the CBO
Using the CBO
Access Paths for the CBO
How the CBO Chooses an Access Path
CBO Parameters
Parameters Affecting CBO Plans
Parameters Affecting How the Optimizer Uses Indexes
Setting Initialization Parameters
Extensible Optimizer
User-Defined Statistics
User-Defined Selectivity
User-Defined Costs
Rule-Based Optimizer (RBO)
Access Paths for the RBO
Overview of Optimizer Operations
Types of SQL Statements
Optimizer Operations
Optimizing Joins
Optimizing Join Statements
Join Operations
How the Optimizer Chooses the Join Method
Forcing the Join Order
Choosing Execution Plans for Join Statements
Optimizing Anti-Joins and Semi-Joins
Optimizing Star Queries
Optimizing Statements that Use Common Subexpressions
Evaluation of Expressions and Conditions
LIKE Operator
IN Operator
ANY or SOME Operator
ALL Operator
BETWEEN Operator
NOT Operator
Transforming and Optimizing Statements
Transforming ORs into Compound Queries
Transforming Complex Statements into Join Statements
Optimizing Statements That Access Views
Optimizing Compound Queries
Optimizing Distributed Statements


Understanding EXPLAIN PLAN
Creating the Output Table
Displaying PLAN_TABLE Output
Output Table Columns
Bitmap Indexes and EXPLAIN PLAN
EXPLAIN PLAN and Partitioned Objects
Displaying Range and Hash Partitioning with EXPLAIN PLAN
Pruning Information with Composite Partitioned Objects
Partial Partition-wise Joins
Full Partition-wise Joins
Domain Indexes and EXPLAIN PLAN
EXPLAIN PLAN Restrictions

6 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
The Argument Trap
The Read Consistency Trap
The Schema Trap
The Time Trap
The Trigger Trap
TKPROF Output Example

7 Using Optimizer Hints

Understanding Hints
Specifying Hints
Using Hints
Hints for Optimization Approaches and Goals
Hints for Access Methods
Hints for Join Orders
Hints for Join Operations
Hints for Parallel Execution
Additional Hints
Using Hints with Views

8 Gathering Statistics

Understanding Statistics
Generating Statistics
Using the ANALYZE Statement
Using the DBMS_STATS Package
Statistics Data
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

9 Optimizing SQL Statements

Approaches to SQL Statement Tuning
Restructuring the Indexes
Restructuring the Statement
Modifying or Disabling Triggers
Restructuring the Data
Keeping Statistics Current and Using Plan Stability to Preserve Execution Plans
Tuning Goals
Tuning a Serial SQL Statement
Tuning Parallel Execution
Tuning OLTP Applications
Best Practices
Avoiding Rule-Based Optimizer Techniques
Index Cost
Analyzing Object Statistics
Avoiding Complex Expressions
Avoiding Balloon Tactic for Coding SQL
Handling Complex Logic in the Application
SQL Tuning Tips
Using EXPLAIN PLAN on All Queries
Predicate Collapsing
Tuning for the Typical Case
Disk Reads and Buffer Gets
Using EXISTS versus IN
Trouble Shooting
Tuning Distributed Queries
Remote and Distributed Queries
Distributed Query Restrictions
Transparent Gateways
Optimizing Performance of Distributed Queries

10 Using Plan Stability

Using Plan Stability to Preserve Execution Plans
Hints and Exact Text Matching
Storing Outlines
Enabling Plan Stability
Creating Outlines
Using Stored Outlines
Viewing Outline Data
Using the OUTLN_PKG Package to Manage Stored Outlines
Moving Outline Tables
Plan Stability Procedures for the Cost-Based Optimizer
Using Outlines to Move to the Cost-Based Optimizer
RDBMS Upgrades and the Cost-Based Optimizer

Part III Application Design Tools for Designers and DBAs

11 Overview of Diagnostic Tools

Sources of Data for Tuning
Data Volumes
Online Data Dictionary
Operating System Tools
Dynamic Performance Tables
Oracle Trace and Oracle Trace Data Viewer
SQL Trace Facility
Alert Log
Application Program Output
Initialization Parameter Files
Program Text
Design (Analysis) Dictionary
Comparative Data
Dynamic Performance Views
Oracle and SNMP Support
SQL Trace and TKPROF
Supported Scripts
Application Registration
Oracle Enterprise Manager, Packs, and Applications
Introduction to Oracle Enterprise Manager
Oracle Diagnostics Pack
Oracle Tuning Pack
Oracle Parallel Server Management
Independent Tools

12 Data Access Methods

Using Indexes
When to Create 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
Assessing the Value of Indexes
Using Fast Full Index Scans
Re-creating Indexes
Compacting Indexes
Using Nonunique Indexes to Enforce Uniqueness
Using Enabled Novalidated Constraints
Using Function-based Indexes
Function-based Indexes and Index Organized Tables
Using Bitmap Indexes
When to Use Bitmap Indexes
Creating Bitmap Indexes
Initialization Parameters for Bitmap Indexing
Using Bitmap Access Plans on Regular B*-tree Indexes
Estimating Bitmap Index Size
Bitmap Index Restrictions
Using Domain Indexes
Using Clusters
Using Hash Clusters
When to Use Hash Clusters
Creating Hash Clusters

13 Managing Shared SQL and PL/SQL Areas

Comparing SQL Statements and PL/SQL Blocks
Testing for Identical SQL Statements
Aspects of Standardized SQL Formatting
Keeping Shared SQL and PL/SQL in the Shared Pool
Reserving Space for Large Allocations
Preventing Objects from Aging Out

14 Using Oracle Trace

Introduction to Oracle Trace
Using Oracle Trace Data
Using Oracle Trace Manager
Managing Collections
Collecting Event Data
Accessing Collected Data
Using Oracle Trace Data Viewer
Oracle Trace Predefined Data Views
Viewing Oracle Trace Data
SQL Statement Property Page
Details Property Page
Example of Details Property Page
Getting More Information on a Selected Query
Manually Collecting Oracle Trace Data
Using the Oracle Trace Command-Line Interface
Using Initialization Parameters to Control Oracle Trace
Using Stored Procedures to Control Oracle Trace
Oracle Trace Collection Results
Formatting Oracle Trace Data to Oracle Tables
Oracle Trace Statistics Reporting Utility

15 Dynamic Performance Views

Instance-Level Views for Tuning
Session-Level or Transient Views for Tuning
Current Statistic Values and Rates of Change
Finding the Current Value of a Statistic
Finding the Rate of Change of a Statistic

16 Diagnosing System Performance Problems

Tuning Factors for Well Designed Existing Systems
Insufficient CPU
Insufficient Memory
I/O Constraints
Network Constraints
Software Constraints

17 Transaction Modes

Using Discrete Transactions
Deciding When to Use Discrete Transactions
How Discrete Transactions Work
Errors During Discrete Transactions
Using Discrete Transactions
Using Serializable Transactions

Part IV Optimizing Instance Performance

18 Tuning CPU Resources

Understanding CPU Problems
Detecting and Solving CPU Problems
System CPU Utilization
Oracle CPU Utilization
Solving CPU Problems by Changing System Architectures
Single Tier to Two-Tier
Multi-Tier: Using Smaller Client Machines
Two-Tier to Three-Tier
Oracle Parallel Server

19 Tuning Memory Allocation

Understanding Memory Allocation Issues
Detecting Memory Allocation Problems
Solving Memory Allocation Problems
Tuning Operating System Memory Requirements
Tuning the Redo Log Buffer
Tuning Private SQL and PL/SQL Areas
Tuning the Shared Pool
Tuning the Buffer Cache
Tuning Multiple Buffer Pools
Tuning Sort Areas
Reallocating Memory
Reducing Total Memory Usage

20 Tuning I/O

Understanding I/O Problems
Tuning I/O: Top Down and Bottom Up
Analyzing I/O Requirements
Planning File Storage
Choosing Data Block Size
Evaluating Device Bandwidth
Detecting I/O Problems
Checking System I/O Utilization
Checking Oracle I/O Utilization
Solving I/O Problems
Reducing Disk Contention by Distributing I/O
Striping Disks
Avoiding Dynamic Space Management
Tuning Sorts
Tuning Checkpoint Activity
Tuning LGWR and DBWR I/O
Tuning Backup and Restore Operations
Configuring the Large Pool

21 Tuning Resource Contention

Understanding Contention Issues
Detecting Contention Problems
Solving Contention Problems
Reducing Contention for Rollback Segments
Reducing Contention for Multi-Threaded Servers
Reducing Contention for Parallel Execution Servers
Reducing Contention for Redo Log Buffer Latches
Reducing Contention for the LRU Latch
Reducing Free List Contention

22 Tuning Networks

Understanding Connection Models
Detecting Network Problems
Using Dynamic Performance Views
Understanding Latency and Bandwidth
Solving Network Problems
Finding Bottlenecks
Dissecting Bottlenecks
Using Array Interfaces
Adjusting Session Data Unit Buffer Size
Using Connection Manager

23 Tuning the Operating System

Understanding Operating System Performance Issues
Operating System and Hardware Caches
Raw Devices
Process Schedulers
Operating System Resource Managers
Detecting Operating System Problems
Solving Operating System Problems
Performance on UNIX-Based Systems
Performance on NT Systems
Performance on Mainframe Computers

24 Tuning Instance Recovery Performance

Understanding Instance Recovery
How Oracle Applies Redo Log Information
Trade-offs of Minimizing Recovery Duration
Tuning the Duration of Instance and Crash Recovery
Using Initialization Parameters to Influence Recovery Time
Using Redo Log Size to Influence Checkpointing Frequency
Using SQL Statements to Initiate Checkpoints
Monitoring Instance Recovery
Tuning the Phases of Instance Recovery
Tuning the Rolling Forward Phase
Tuning the Rolling Back Phase


Copyright © 1999 Oracle Corporation.

All Rights Reserved.