Oracle8
i
Designing and Tuning for Performance
Release 2 (8.1.6)
Part Number A76992-01
Library
Product
Index
Updated information for Release 8.1.7 can be found in the
Oracle8i Documentation Addendum.
Contents
Title and Copyright Information
Send Us Your Comments
Preface
Intended Audience
How This Book is Organized
What's New
Related Documents
Conventions
Text
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
Parser
Optimizer
Row Source Generator
SQL Execution
EXPLAIN PLAN
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
Constants
LIKE Operator
IN Operator
ANY or SOME Operator
ALL Operator
BETWEEN Operator
NOT Operator
Transitivity
DETERMINISTIC Functions
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
5 Using EXPLAIN PLAN
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
INLIST ITERATOR and EXPLAIN PLAN
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
Header
Body
Summary
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
Users
Initialization Parameter Files
Program Text
Design (Analysis) Dictionary
Comparative Data
Dynamic Performance Views
Oracle and SNMP Support
EXPLAIN PLAN
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
Example
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
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 TCP.NODELAY
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
Index
Copyright © 1996-2000, Oracle Corporation.
All Rights Reserved.
Library
Product
Index