Oracle8 Tuning
Release 8.0
A58246-01

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Part I Introduction

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

Setting Performance Targets

Setting User Expectations

Evaluating Performance

2 Performance Tuning Method

When Is Tuning Most Effective?

Proactive Tuning While Designing and Developing a System

Reactive Tuning to Improve a Production System

Prioritized Steps of the Tuning Method

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)

How to Apply the Tuning Method

Set Clear Goals for Tuning

Create Minimum Repeatable Tests

Test Hypotheses

Avoid Common Errors

Stop Tuning When the Objectives Are Met

Demonstrate Meeting the Objectives

3 Diagnosing Performance Problems in an Existing System

Tuning Factors for a Well-Designed Existing System

Insufficient CPU

Insufficient Memory

Insufficient I/O

Network Constraints

Software Constraints

4 Overview of Diagnostic Tools

Sources of Data for Tuning

Online Data Dictionary

Operating System Tools

Dynamic Performance Tables

SQL Trace Facility

Application Program Output

Initialization Parameter Files

Design (Analysis) Dictionary

Comparative Data

Dynamic Performance Views

Oracle and SNMP Support

The SQL Trace Facility and TKPROF

Supported Scripts

Application Registration

Oracle Enterprise Manager Applications

Introduction to Oracle Enterprise Manager

Oracle Performance Manager

Oracle TopSessions

Oracle Tablespace Manager

Oracle Parallel Server Management

Tools You May Have Developed

Part II Designing and Developing for Performance

5 Evaluating Your System's Performance Characteristics

Types of Application

Online Transaction Processing (OLTP)

Data Warehousing

Multipurpose Applications

Oracle Configurations

Distributed Systems

The Oracle Parallel Server

Client/Server Configurations

6 Designing Data Warehouse Applications

Features for Building a Data Warehouse

Parallel CREATE TABLE . . . AS SELECT

Parallel Index Creation

Fast Full Index Scan

Partitioned Tables

ANALYZE Command

Features for Querying a Data Warehouse

Oracle Parallel Server Option

Parallel-Aware Optimizer

Parallel Execution

Star Transformation

Backup and Recovery of the Data Warehouse

Part III Optimizing Database Operations

7 Tuning Database Operations

Tuning a Serial SQL Statement

Tuning Parallel Operations

Tuning OLTP Applications

Tuning Data Warehouse Applications

Methodology for Tuning Database Operations

Step 1: Find the Statements that Consume the Most Resources

Step 2: Tune These Statements so They Use Less Resources

Approaches to SQL Statement Tuning

Restructure the Indexes

Restructure the Statement

Restructure the Data

8 Optimization Modes and Hints

Using Cost-Based Optimization

When to Use the Cost-Based Approach

How to Use the Cost-Based Approach

Using Histograms for Nonuniformly Distributed Data

Generating Statistics

Choosing a Goal for the Cost-Based Approach

Parameters that Affect Cost-Based Optimization Plans

Tips for Using the Cost-Based Approach

Using Rule-Based Optimization

Introduction to Hints

How to Specify Hints

Hints for Optimization Approaches and Goals

Hints for Access Methods

Hints for Join Orders

Hints for Join Operations

Hints for Parallel Execution

NOPARALLEL_INDEX

Additional Hints

NO_PUSH_JOIN_PRED

STAR_TRANSFORMATION

Using Hints with Views

Hints and Mergeable Views

Hints and Nonmergeable Views

9 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

10 Data Access Methods

When to Create Indexes

Tuning the Logical Structure

How to Choose Columns to Index

How to Choose Composite Indexes

How to Write Statements that Use Indexes

How to Write Statements that Avoid Using Indexes

Assessing the Value of Indexes

Fast Full Index Scan

Re-creating an Index

Using Existing Indexes to Enforce Uniqueness

Using Enforced Constraints

Using Bitmap Indexes

When to Use Bitmap Indexing

How to Create a Bitmap Index

Initialization Parameters for Bitmap Indexing

Using Bitmap Access Plans on Regular B*-tree Indexes

Estimating Bitmap Index Size

Bitmap Index Restrictions

Using Hash Clusters

When to Use a Hash Cluster

How to Use a Hash Cluster

11 Oracle8 Transaction Modes

Using Discrete Transactions

Deciding When to Use Discrete Transactions

How Discrete Transactions Work

Errors During Discrete Transactions

Using Serializable Transactions

12 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 Being Aged Out

Part IV Optimizing Oracle Instance Performances

13 Tuning CPU Resources

Understanding CPU Problems

How to Detect and Solve CPU Problems

Checking System CPU Utilization

Checking Oracle CPU Utilization

Solving CPU Problems by Changing System Architecture

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

14 Tuning Memory Allocation

Understanding Memory Allocation Issues

How to Detect Memory Allocation Problems

How to Solve Memory Allocation Problems

Tuning Operating System Memory Requirements

Reducing Paging and Swapping

Fitting the System Global Area into Main Memory

Allocating Enough 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 Shared Pool with the Multithreaded Server

Tuning Reserved Space from the Shared Pool

Tuning the Buffer Cache

Evaluating Buffer Cache Activity by Means of the Cache Hit Ratio

Raising Cache Hit Ratio by Reducing Buffer Cache Misses

Removing Unnecessary Buffers when Cache Hit Ratio Is High

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

How to Size Each Buffer Pool

How to Recognize and Eliminate LRU Latch Contention

Tuning Sort Areas

Reallocating Memory

Reducing Total Memory Usage

15 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

How to Detect I/O Problems

Checking System I/O Utilization

Checking Oracle I/O Utilization

How to Solve 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

What Is Striping?

I/O Balancing and Striping

How to Stripe Disks Manually

How to Stripe Disks with Operating System Software

How to Do Hardware Striping 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

Sorting to Memory

If You Do Sort to Disk

Optimizing Sort Performance with Temporary Tablespaces

Using NOSORT to Create Indexes Without Sorting

GROUP BY NOSORT

Optimizing Large Sorts with SORT_DIRECT_WRITES

Tuning Checkpoints

How Checkpoints Affect Performance

Choosing Checkpoint Frequency

Reducing the Performance Impact of a Checkpoint

Tuning LGWR and DBWn I/O

Tuning LGWR I/O

Tuning DBWn I/O

Configuring the Large Pool

16 Tuning Networks

How to Detect Network Problems

How to Solve Network Problems

Using Array Interfaces

Using Prestarted Processes

Adjusting Session Data Unit Buffer Size

Increasing the Listener Queue Size

Using TCP.NODELAY

Using Shared Server Processes Rather than Dedicated Server Processes

Using Connection Manager

17 Tuning the Operating System

Understanding Operating System Performance Issues

Operating System and Hardware Caches

Process Schedulers

How to Detect Operating System Problems

How to Solve Operating System Problems

Performance on UNIX-Based Systems

Performance on NT Systems

Performance on Mainframe Computers

18 Tuning Resource Contention

Understanding Contention Issues

How to Detect Contention Problems

How to Solve Contention Problems

Reducing Contention for Rollback Segments

Identifying Rollback Segment Contention

Creating Rollback Segments

Reducing Contention for Multithreaded Server Processes

Reducing Contention for Dispatcher Processes

Reducing Contention for Shared Server Processes

Reducing Contention for Parallel Server Processes

Identifying Contention for Parallel Server Processes

Reducing Contention for Parallel Server Processes

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

Part V Optimizing Parallel Execution

19 Tuning Parallel Execution

Introduction to Parallel Execution Tuning

Step 1: Tuning System Parameters for Parallel Execution

Parameters Affecting Resource Consumption for All Parallel Operations

Parameters Affecting Resource Consumption for Parallel DML & Parallel DDL

Parameters Enabling New Features

Parameters Related to I/O

Step 2: Tuning Physical Database Layout for Parallel Execution

Types of Parallelism

Partitioning Data

Determining the Degree of Parallelism

Populating the Database Using Parallel Load

Setting Up Temporary Tablespaces for Parallel Sort and Hash Join

Creating Indexes in Parallel

Additional Considerations for Parallel DML Only

Step 3: Analyzing Data

20 Understanding Parallel Execution Performance Issues

Understanding Parallel Execution Performance Issues

The Formula for Memory, Users, and Parallel Server Processes

Setting Buffer Pool Size for Parallel Operations

How to Balance the Formula

Examples: Balancing Memory, Users, and Processes

Parallel Execution Space Management Issues

Optimizing Parallel Execution on Oracle Parallel Server

Parallel Execution Tuning Techniques

Overriding the Default Degree of Parallelism

Rewriting SQL Statements

Creating and Populating Tables in Parallel

Creating Indexes in Parallel

Refreshing Tables in Parallel

Using Hints with Cost Based Optimization

Tuning Parallel Insert Performance

21 Diagnosing Parallel Execution Performance Problems

Part VI Performance Diagnostic Tools

22 The Dynamic Performance Views

Instance-Level Views for Tuning

Session-Level or Transient Views for Tuning

Current Statistic Value and Rate of Change

Finding the Current Value of a Statistic

Finding the Rate of Change of a Statistic

23 The EXPLAIN PLAN Command

Creating the Output Table

Output Table Columns

Bitmap Indexes and EXPLAIN PLAN

INLIST ITERATOR and EXPLAIN PLAN

Formatting EXPLAIN PLAN Output

How to Run EXPLAIN PLAN

Selecting PLAN_TABLE Output in Table Format

Selecting PLAN_TABLE Output in Nested Format

EXPLAIN PLAN Restrictions

24 The SQL Trace Facility and TKPROF

About the SQL Trace Facility

How to Use 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 a Different User 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

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 Trigger Trap

The "Correct" Version

TKPROF Output Example

25 Using Oracle Trace

Using Oracle Trace for Server Performance Data Collection

Using Initialization Parameters to Control Oracle Trace

Enabling Oracle Trace Collections

Determining the Event Set Which Oracle Trace Collects

Using Stored Procedure Packages to Control Oracle Trace

Using the Oracle Trace Command-Line Interface

Oracle Trace Collection Results

Oracle Trace Detail Reports

Formatting Oracle Trace Data to Oracle Tables

26 Registering Applications

Registering Applications

DBMS_APPLICATION_INFO Package

Setting the Module Name

Setting the Action Name

Setting the Client Information

Retrieving Application Information

Querying V$SQLAREA

READ_MODULE Syntax

READ_CLIENT_INFO Syntax

Copyright © 1997 Oracle Corporation.
All Rights Reserved.