Oracle8 Server TuningOracle8 Server

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

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 the SQL

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

Keep Records

Avoid Common Errors

Stop Tuning When the Objectives Are Met

Demonstrate Meeting the Objectives

Tuning Factors for a Well-Designed Existing System

Sources of Data for Tuning

Data Volumes

Online Data Dictionary

Operating System Tools

Dynamic Performance Tables

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

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 Trace

Oracle Tablespace Manager

Oracle Expert

Tools You May Have Developed

Types of Application

Online Transaction Processing (OLTP)

Decision Support Systems (DSS)

Multi-Purpose Applications

Oracle Configurations

Distributed Systems

The Oracle Parallel Server

Client/Server Configurations

Introduction

Features for Building a Data Warehouse

PARALLEL CREATE TABLE AS SELECT

Parallel Index Creation

Partitioned Tables

ANALYZE

Parallel Load

Features for Querying a Data Warehouse

Oracle Parallel Server Option

Parallel Aware Optimizer

Parallel Data Manipulation Language and Parallel Query

Bitmap Indexes

Star Queries

Star Transformation

Backup and Recovery of the Data Warehouse

Tuning Goals

Tuning a Serial SQL Statement

Tuning Parallel Operations

Tuning OLTP Applications

Tuning Data Warehouse Applications

Methodology for Tuning Database Operations

Step 1: Find Statements Which 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

Using Cost-Based Optimization

When to Use the Cost-Based Approach

How to Use the Cost-Based Approach

Using Histograms for Non-uniformly Distributed Data

Generating Statistics

Choosing a Goal for the Cost-Based Approach

Parameters Which 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

ORDERED

STAR

STAR_TRANSFORMATION

Hints for Join Operations

Hints for Parallel Execution

Distributed Queries: Decomposition of SQL Statements

Remote Data Dictionary Information

EXPLAIN PLAN and SQL Decomposition

Accessing All Tables from the Same Remote Database

Accessing Tables from Different Databases

Transparent Gateways

Distributed Query Restrictions

Summary: Optimizing Performance of Distributed Queries

Using Clusters

Using Hash Clusters

When to Use a Hash Cluster

How to Use a Hash Cluster

Using Indexes

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

Recreating 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 Discrete Transactions

Deciding When to Use Discrete Transactions

How Discrete Transactions Work

Errors During Discrete Transactions

Usage Notes

Example

Using Serializable Transactions

Introduction

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

What Are 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 Transaction Processing Monitor

Three-Tier: Using Multiple TP Monitors

Oracle Parallel Server

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 Multi-Threaded Server

Tuning Reserved Space from the Shared Pool

Tuning the Buffer Cache

Examining Buffer Cache Activity via the Cache Hit Ratio

Sizing the Buffer Cache

Raising Cache Hit Ratio by Reducing Buffer Cache Misses

Removing Unnecessary Buffers When Cache Hit Ratio 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

What Are I/O Problems?

Tuning I/O: Top Down and Bottom Up

How to Analyze I/O Requirements

How to Plan File Storage

How to Choose Data Block Size

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

Striping Disks

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

Tuning Sorts

Sorting to Memory: Allocating Sort Area

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 DBWR I/O

Tuning LGWR I/O

Tuning DBWR I/O

Configuring the Large Pool

How to Detect Network Problems

How to Solve Network Problems

Using Array Interfaces

Using Prestarted Processes

Adjusting SDU Buffer Size

Increasing the Listener Queue Size

Using TCP.NODELAY

Using Shared Server Processes, Not Dedicated Server Processes

Using Connection Manager

Understanding Operating System Performance Issues

Overview

Operating System and Hardware Caches

Raw Devices

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

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 Multi-Threaded 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

Introduction to Parallel Execution Tuning

Step 1: Tuning System Parameters for Parallel Execution

Parameters Affecting Resource Consumption

Parameters Enabling New Features

Parameters Related to I/O

Step 2: Tuning Physical Database Layout for Parallel Execution

Determining the Degree of Parallelism

Using Parallel Load

Setting Up Temporary Tablespaces for Parallel Sort, Hash Join

Creating Indexes in Parallel

Additional Considerations for Parallel DML

Step 3: Analyzing Data

Understanding Parallel Execution Performance Issues

The Formula for Memory, Users, & 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 Performance Techniques

Overriding the Default Degree of Parallelism

Rewriting the SQL

Creating and Populating Tables in Parallel

Creating Indexes in Parallel

Refreshing Tables of a Data Warehouse System in Parallel

Using Hints with Cost Based Optimization

Detecting Parallel Execution Performance Problems

Diagnosing Problems

Dynamic Performance Tables

Operating System Statistics

Recovery Time

PDML Restrictions

Tuning Parallel Insert Performance

INSERT

Direct-load INSERT

Parallelizing INSERT, UPDATE, and DELETE

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

Introduction

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

Introduction

About the SQL Trace Facility

About TKPROF

How to Use the SQL Trace Facility and TKPROF

Step 1: Setting Initialization Parameters for Trace File Management

Step 2: Enabling 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: Formatting Trace Files with TKPROF

Sample TKPROF Output

Syntax of TKPROF

TKPROF Statement Examples

Step 4: Interpreting TKPROF Output

Tabular Statistics

Library Cache Misses

User Issuing the SQL Statement

Execution Plan

Deciding What Statements to Tune

Step 5: Storing 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 the Statements Which 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

Using Oracle Trace for Server Performance Data Collection

Using Initialization Parameters to Control Oracle Trace

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

Overview

Registering Applications

DBMS_APPLICATION_INFO Package

Privileges

Setting the Module Name

Example

Syntax

Setting the Action Name

Example

Syntax

Setting the Client Information

Syntax

Retrieving Application Information

Querying V$SQLAREA

READ_MODULE Syntax

READ_CLIENT_INFO Syntax

Contents

Title and Copyright Information

Preface

PART 1 INTRODUCTION

Chapter 1 Introduction to Oracle Performance Tuning

Chapter 2 Performance Tuning Method

Chapter 3 Diagnosing Performance Problems in an Existing System

Chapter 4 Overview of Diagnostic Tools

PART 2 DESIGNING & DEVELOPING FOR PERFORMANCE

Chapter 5 Evaluating Your System's Performance Characteristics

Chapter 6 Designing Data Warehouse Applications

PART 3 OPTIMIZING DATABASE OPERATIONS

Chapter 7 Tuning Database Operations

Chapter 8 Optimization Modes and Hints

Chapter 9 Tuning Distributed Queries

Chapter 10 Data Access Methods

Chapter 11 Oracle8 Transaction Modes

Chapter 12 Managing SQL and Shared PL/SQL Areas

PART 4 OPTIMIZING ORACLE INSTANCE PERFORMANCE

Chapter 13 Tuning CPU

Chapter 14 Tuning Memory Allocation

Chapter 15 Tuning I/O

Chapter 16 Tuning Networks

Chapter 17 Tuning the Operating System

Chapter 18 Tuning Resource Contention

Chapter 19 Tuning Parallel Execution

PART 5 PERFORMANCE DIAGNOSTIC TOOLS

Chapter 20 The Dynamic Performance Tables

Chapter 21 The EXPLAIN PLAN Command

Chapter 22 The SQL Trace Facility and TKPROF

Chapter 23 Using Oracle Trace

Chapter 24 Registering Applications

Index