Oracle8i Tuning
Release 8.1.5







Title and Copyright Information

Send Us Your Comments


Part I Introduction to Tuning

1 Introduction to 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 Programmers

3 Application and System Performance Characteristics

Types of Applications
Online Transaction Processing (OLTP)
Data Warehousing
Multipurpose Applications
Oracle Configurations
Distributed Systems
The Oracle Parallel Server
Client/Server Configurations

4 Tuning Database Operations

Tuning Goals
Tuning a Serial SQL Statement
Tuning Parallel Execution
Tuning OLTP Applications
Methodology for Tuning Database Operations
Step 1: Find the Statements that Consume the Most Resources
Step 2: Tune These Statements To Use Fewer Resources
Approaches to SQL Statement Tuning
Restructure the Indexes
Restructure the Statement
Modify or Disable Triggers
Restructure the Data
Keeping Statistics Current and Using Plan Stability to Preserve Execution Plans

5 Registering Applications

Setting the Module Name
Setting the Action Name
Setting the Client Information
Retrieving Application Information
Querying V$SQLAREA

6 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 a Hash Cluster
Creating Hash Clusters

7 Optimizer Modes, Plan Stability, and Hints

Using Cost-based Optimization
When to Use the Cost-based Approach
Using the Cost-based Approach
Choosing a Goal for the Cost-based Approach
Using Histograms for Nonuniformly Distributed Data
Generating Statistics
Gathering Statistics with the DBMS_STATS Package
Gathering New Optimizer Statistics
Automated Statistics Gathering
Parameters Affecting Cost-based Optimization Plans
Parameters Affecting How the Optimizer Uses Indexes
Tips for Using the Cost-based Approach
Using Rule-Based Optimization
Using Plan Stability to Preserve Execution Plans
Plan Stability Uses Hints and Exact Text Matching
Matching SQL Statements with Outlines
How Oracle Stores Outlines
Parameter Settings to Enable Plan Stability
Creating Outlines
Creating and Assigning Categories to Stored Outlines
Using Stored Outlines
Viewing Outline Data
Managing Stored Outlines with the OUTLN_PKG Package
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
Using Hints
Specifying 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 Tuning Distributed Queries

Remote and Distributed Queries
Remote Data Dictionary Information
Remote SQL Statements
Distributed SQL Statements
EXPLAIN PLAN and SQL Decomposition
Partition Views
Distributed Query Restrictions
Transparent Gateways
Summary: Optimizing Performance of Distributed Queries

9 Transaction Modes

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

10 Managing SQL and Shared 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

11 Optimizing Data Warehouse Applications

Characteristics of Data Warehouse Applications
Building a Data Warehouse
Materialized Views and Dimensions
Parallel Index Creation
Fast Full Index Scan
Partitioned Tables
ANALYZE Statement
Parallel Load
Querying a Data Warehouse
Oracle Parallel Server Option
Parallel-Aware Optimizer
Parallel Execution
Bitmap Indexes
Domain Indexes
Star Queries
Query Rewrites
Tuning Data Warehouse Applications
Backup and Recovery of the Data Warehouse
Tuning Fast-start Parallel Recovery

Part III Application Design Tools for Designers and DBAs

12 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
Oracle Trace and Oracle Trace Data Viewer
The SQL Trace Facility 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
Tools You May Have Developed


Introduction to EXPLAIN PLAN
Creating the Output Table
Displaying PLAN_TABLE Output
Output Table Columns
Bitmap Indexes and EXPLAIN PLAN
EXPLAIN PLAN and Partitioned Objects
Examples of How EXPLAIN PLAN Displays Range and Hash Partitioning
Pruning Information with Composite Partitioned Objects
Partial Partition-wise Joins
Full Partition-wise Joins
Formatting EXPLAIN PLAN Output
Using the EXPLAIN PLAN Statement
Selecting PLAN_TABLE Output in Table Format
Selecting PLAN_TABLE Output in Nested Format
EXPLAIN PLAN Restrictions

14 The SQL Trace Facility and TKPROF

Introduction to SQL Trace and TKPROF
About the SQL Trace Facility
Using the SQL Trace Facility and TKPROF
Step 1: Set Initialization Parameters for Trace File Management
Step 2: Enable the SQL Trace Facility
Enabling the SQL Trace Facility for Your Current Session
Enabling the SQL Trace Facility for an Instance
Step 3: Format Trace Files with TKPROF
Sample TKPROF Output
Syntax of TKPROF
TKPROF Statement Examples
Step 4: Interpret TKPROF Output
Tabular Statistics
Library Cache Misses
Statement Truncation
User Issuing the SQL Statement
Execution Plan
Deciding Which Statements to Tune
Step 5: Store SQL Trace Facility Statistics
Generating the TKPROF Output SQL Script
Editing the TKPROF Output SQL Script
Querying the Output Table
Avoiding Pitfalls in TKPROF Interpretation
Finding Which Statements Constitute the Bulk of the Load
The Argument Trap
The Read Consistency Trap
The Schema Trap
The Time Trap
The Trigger Trap
The "Correct" Version
TKPROF Output Example

15 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

Part IV Optimizing Instance Performance

16 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

17 Diagnosing System Performance Problems

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

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: Using a Transaction Processing Monitor
Three-Tier: Using Multiple TP Monitors
Oracle Parallel Server

19 Tuning Memory Allocation

Understanding Memory Allocation Issues
Detecting Memory Allocation Problems
Solving Memory Allocation Problems
Tuning Operating System Memory Requirements
Reducing Paging and Swapping
Fitting the System Global Area into Main Memory
Allocating Adequate Memory to Individual Users
Tuning the Redo Log Buffer
Tuning Private SQL and PL/SQL Areas
Identifying Unnecessary Parse Calls
Reducing Unnecessary Parse Calls
Tuning the Shared Pool
Tuning the Library Cache
Tuning the Data Dictionary Cache
Tuning the Large Pool and Shared Pool for the Multi-threaded Server Architecture
Reducing Memory Use With Three-Tier Connections
Querying the V$SESSTAT View
Tuning Reserved Space from the Shared Pool
Reserved List Tuning Parameters
Controlling Space Reclamation of the Shared Pool
Initial Parameter Values
Tuning the Buffer Cache
Evaluating Buffer Cache Activity by Means of the Cache Hit Ratio
Increasing the Cache Hit Ratio by Reducing Buffer Cache Misses
Removing Unnecessary Buffers when Cache Hit Ratio Is High
Accommodating LOBs in the Buffer Cache
Temporary LOBs
Tuning Multiple Buffer Pools
Overview of the Multiple Buffer Pool Feature
When to Use Multiple Buffer Pools
Tuning the Buffer Cache Using Multiple Buffer Pools
Enabling Multiple Buffer Pools
Using Multiple Buffer Pools
Dictionary Views Showing Default Buffer Pools
Sizing Each Buffer Pool
Identifying and Eliminating LRU Latch Contention
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
What Is Disk Contention?
Separating Datafiles and Redo Log Files
Striping Table Data
Separating Tables and Indexes
Reducing Disk I/O Unrelated to Oracle
Striping Disks
The Purpose of Striping
I/O Balancing and Striping
Striping Disks Manually
Striping Disks with Operating System Software
Striping Hardware with RAID
Avoiding Dynamic Space Management
Detecting Dynamic Extension
Allocating Extents
Evaluating Unlimited Extents
Evaluating Multiple Extents
Avoiding Dynamic Space Management in Rollback Segments
Reducing Migrated and Chained Rows
Modifying the SQL.BSQ File
Tuning Sorts
Sorting to Memory
Sorting to Disk
Optimizing Sort Performance with Temporary Tablespaces
Improving Sort Performance by Striping Temporary Tablespaces
Improving Sort Performance Using SORT_MULTIBLOCK_READ_COUNT
Using NOSORT to Create Indexes Without Sorting
Tuning Checkpoints
How Checkpoints Affect Performance
Choosing Checkpoint Frequency
Fast-Start Checkpointing
Tuning LGWR and DBWn I/O
Tuning LGWR I/O
Tuning DBWn I/O
Tuning Backup and Restore Operations
Locating the Source of a Bottleneck
Using Fixed Views to Monitor Backup Operations
Improving Backup Throughput
Configuring the Large Pool

21 Tuning Resource Contention

Understanding Contention Issues
Detecting Contention Problems
Solving Contention Problems
Reducing Contention for Rollback Segments
Identifying Rollback Segment Contention
Creating Rollback Segments
Reducing Contention for Multi-threaded Server Processes
Identifying Contention Using the Dispatcher-specific Views
Reducing Contention for Dispatcher Processes
Reducing Contention for Shared Server Processes
Reducing Contention for Parallel Execution Servers
Identifying Contention for Parallel Execution Servers
Reducing Contention for Parallel Execution Servers
Reducing Contention for Redo Log Buffer Latches
Detecting Contention for Space in the Redo Log Buffer
Detecting Contention for Redo Log Buffer Latches
Examining Redo Log Activity
Reducing Latch Contention
Reducing Contention for the LRU Latch
Reducing Free List Contention
Identifying Free List Contention
Adding More Free Lists

22 Tuning Networks

Detecting Network Problems
Solving Network Problems
Using Array Interfaces
Using Prestarted Processes
Adjusting Session Data Unit Buffer Size
Increasing the Listener Queue Size
Using Shared Server Processes Rather than Dedicated Server Processes
Using Connection Manager

23 Tuning the Multi-Threaded Server Architecture

Setting Up MTS
Application Types that Benefit from MTS
Improving User Scalability with MTS
Configuring Dispatchers
Connection Pooling and Connection Multiplexing
Maximizing Throughput and Response Time with MTS
Configuring and Managing the Number of Shared Servers
Tuning the SDU Size
Balancing Load Connections
Tuning Memory Use with MTS
Configuring the Large Pool and Shared Pool for MTS
Limiting Memory Use Per User Session by Setting PRIVATE_SGA
MTS-related Views with Connection, Load and Statistics Data
MTS Feature Performance Issues

24 Tuning the Operating System

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

25 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 Instance and Crash 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
Transparent Application Failover
What Is Transparent Application Failover?
How does Transparent Application Failover Work?
Transparent Application Failover Implementation Scenarios
Transparent Application Failover Topics for the DBA
Transparent Application Failover Topics for Application Developers
Transparent Application Failover Restrictions

Part V Parallel Execution

26 Tuning Parallel Execution

Introduction to Parallel Execution Tuning
When to Implement Parallel Execution
Phase One - Initializing and Tuning Parameters for Parallel Execution
Step One: Selecting Automated or Manual Tuning of Parallel Execution
Automatically Derived Parameter Settings under Fully Automated Parallel Execution
Step Two: Setting the Degree of Parallelism and Enabling Adaptive Multi-User
Degree of Parallelism and Adaptive Multi-User and How They Interact
Enabling Parallelism for Tables and Queries
Controlling Performance with PARALLEL_THREADS_PER_CPU
Step Three: Tuning General Parameters
Parameters Establishing Resource Limits for Parallel Operations
Parameters Affecting Resource Consumption
Parameters Related to I/O
Example Parameter Setting Scenarios for Parallel Execution
Example One: Small Datamart
Example Two: Medium-sized Data Warehouse
Example Three: Large Data Warehouse
EXAMPLE Four: Very Large Data Warehouse
Phase Two - Tuning Physical Database Layouts for Parallel Execution
Types of Parallelism
Partitioning Data
Partition Pruning
Partition-wise Joins
Phase Three - Creating, Populating, and Refreshing the Database
Populating Databases Using Parallel Load
Creating Temporary Tablespaces for Parallel Sort and Hash Join
Creating Indexes in Parallel
Executing Parallel SQL Statements
Using EXPLAIN PLAN to Show Parallel Operations Plans
Additional Considerations for Parallel DML
Phase Four - Monitoring Parallel Execution Performance
Monitoring Parallel Execution Performance with Dynamic Performance Views
Monitoring Session Statistics
Monitoring Operating System Statistics

27 Understanding Parallel Execution Performance Issues

Understanding Parallel Execution Performance Issues
Formula for Memory, Users, and Parallel Execution Server Processes
Setting Buffer Pool Size for Parallel Operations
Balancing the Formula
Examples: Balancing Memory, Users, and Parallel Execution Servers
Parallel Execution Space Management Issues
Tuning Parallel Execution on Oracle Parallel Server
Parallel Execution Tuning Tips
Overriding the Default Degree of Parallelism
Rewriting SQL Statements
Creating and Populating Tables in Parallel
Creating Indexes in Parallel
Parallel DML Tips
Refreshing Tables in Parallel
Using Hints with Cost Based Optimization
Diagnosing Problems
Is There Regression?
Is There a Plan Change?
Is There a Parallel Plan?
Is There a Serial Plan?
Is There Parallel Execution?
Is The Workload Evenly Distributed?

Part VI Materialized Views

28 Data Warehousing with Materialized Views

Overview of Data Warehousing with Materialized Views
Materialized Views for Data Warehouses
Materialized Views for Distributed Computing
Materialized Views for Mobile Computing
Components of Summary Management
Materialized Views
Schema Design Guidelines for Materialized Views
Oracle Tools for Data Warehousing
Getting Started

29 Materialized Views

The Need for Materialized Views
Creating a Materialized View
Storage Characteristics
Build Methods
Used for Query Rewrite
Query Rewrite Restrictions
Refresh Options
Defining the Data for the Materialized View
Registration of an Existing Materialized View
Partitioning a Materialized View
Partitioning the Materialized View
Partitioning a Prebuilt Table
Indexing Selection for Materialized Views
Invalidating a Materialized View
Security Issues
Guidelines for using Materialized Views in a Data Warehouse
Altering a Materialized View
Dropping a Materialized View

30 Dimensions

Dimensions in a Data Warehouse
Creating a Dimension
Multiple Hierarchies
Using Normalized Dimension Tables
Viewing Dimensions
Dimensions and Constraints
Validating a Dimension
Altering a Dimension
Deleting a Dimension

31 Query Rewrite

Overview of Query Rewrite
Cost-Based Rewrite
Enabling Query Rewrite
Initialization Parameters for Query Rewrite
Privileges for Enabling Query Rewrite
When Does Oracle Rewrite a Query?
Query Rewrite Methods
SQL Text Match Rewrite Methods
General Query Rewrite Methods
When are Constraints and Dimensions Needed?
Accuracy of Query Rewrite
Did Query Rewrite Occur?
Explain Plan
Controlling Query Rewrite
Guidelines for Using Query Rewrite
Outer Joins
SQL Text Match
Grouping Conditions

32 Managing Materialized Views

Overview of Materialized View Management
Warehouse Refresh
Complete Refresh
Fast Refresh
Tips for Refreshing Using Warehouse Refresh
Recommended Initialization Parameters for Parallelism
Monitoring a Refresh
Tips after Refreshing Materialized Views
Summary Advisor
Collecting Structural Statistics
Collection of Dynamic Workload Statistics
Recommending Materialized Views
Estimating Materialized View Size
Is a Materialized View Being Used?

Copyright © 1999 Oracle Corporation.

All Rights Reserved.