Oracle7 Server Tuning

Library

Product

Oracle7 Server(tm) Tuning

PART I: Introduction

Chapter 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

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

Insufficient CPU

Insufficient Memory

Insufficient I/O

Network Constraints

Software Constraints

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

Server Manager Monitor Screens

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

PART II: Designing & Developing for Performance

Chapter 5 Evaluating Your System's Performance Characteristics

Types of Application

Online Transaction Processing (OLTP)

Decision Support Systems (DSS)

Multi-Purpose Applications

Parallel Query Processing

Oracle Configurations

Distributed Systems

The Oracle Parallel Server

Client/Server Configurations

Introduction

Oracle Data Warehousing Features

Parallel Load

Parallel Index Creation

Bitmap Indexes

Star Queries

Parallel Query

Parallel Aware Optimizer

ANALYZE

Fast Full Index Scan

PARALLEL CREATE TABLE AS SELECT

Partitions and Partition Views

Oracle Parallel Server Option

Backup and Recovery of the Data Warehouse

PART III: Writing Efficient SQL Statements

Chapter 7 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 Non-uniformly Distributed Data

Generating Statistics

Choosing a Goal for the Cost-Based Approach

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

ALL_ROWS

FIRST_ROWS

CHOOSE

RULE

Hints for Access Methods

FULL

ROWID

CLUSTER

HASH

HASH_AJ

INDEX

INDEX_ASC

INDEX_COMBINE

INDEX_DESC

INDEX_FFS

MERGE_AJ

AND_EQUAL

USE_CONCAT

Hints for Join Orders

ORDERED

STAR

Hints for Join Operations

USE_NL

USE_MERGE

NO_MERGE

USE_HASH

Hints for Parallel Query Execution

PARALLEL

NOPARALLEL

Additional Hints

CACHE

NOCACHE

PUSH_SUBQ

Considering Alternative SQL Syntax

Using Clusters

Using Hash Clusters

When to Use a Hash Cluster

How to Use a Hash Cluster

Using Anti-Joins

When to Use an Anti-Join

How to Use an Anti-Join

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

FAST FULL SCAN

Recreating an Index

Bitmap Indexing

Benefits for Data Warehousing

What Is a Bitmap Index?

Cardinality

Bitmap Index Example

When to Use Bitmap Indexing

How to Create a Bitmap Index

Initialization Parameters for Bitmap Indexing

Bitmap Indexes and EXPLAIN PLAN

Using Bitmap Access Plans on Regular B-tree Indexes

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

Partition View Guidelines

Partition View Highlights

Rules and Guidelines for Use

Partition Views and Parallelism

Defining Partition Views

Defining Partition Views Using Check Constraints

Defining Partition Views Using WHERE Clauses

Partition Views: Example

Create the Tables Underlying the Partition View

Load Each Partition View

Enable Check Constraints

Add Additional Overlapping Partition Criteria

Create Indexes for Each Partition

Analyze the Partitions

Create the View that Ties the Partitions Together

PART IV: Optimizing Oracle Instance Performance

Chapter 12 Tuning CPU

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 a 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 Is High

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

Unlimited 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

How to Detect Network Problems

How to Solve Network Problems

Using Array Interfaces

Using Out-of-band Breaks

Using Listener Load Balancing

Using Prestarted Processes

Adjusting SDU Buffer Size

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 Query Servers

Identifying Query Server Contention

Reducing Query Server Contention

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

Understanding Operating System Performance Issues

Overview

O/S 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

Performance on Mainframe Computers

Introduction to Parallel Query Tuning

Step 1: Tuning System Parameters for the Parallel Query

Parameters Affecting Resource Consumption

Parameters Enabling New Features

Parameters Related to I/O

Step 2: Tuning Physical Database Layout for the Parallel Query

A Case Study

Striping Data

Partitioning Data

Determining the Degree of Parallelism

Using Parallel Load

Setting Up Temporary Tablespaces for Parallel Sort and Hash Join

Creating Indexes in Parallel

Step 3: Analyzing Data

Understanding Parallel Query Performance Issues

The Formula for Memory, Users, and Query Servers

How to Balance the Formula

Examples of Balancing Memory, Users, and Query Servers

Parallel Query Space Management Issues

Optimizing Parallel Query on Oracle Parallel Server

Detecting Parallel Query Performance Problems

Diagnosing Problems

Dynamic Performance Tables

Operating System Statistics

Solving Parallel Query Performance Problems

Overriding the Default Degree of Parallelism

Rewriting the SQL

Creating and Populating Tables in Parallel

Creating Temporary Tables in Parallel

Creating Indexes in Parallel

Using Direct Disk I/O for Sorts with Parallel Query

Using Hints with the Cost Based Optimization Approach

PART V: Performance Diagnostic Tools

Chapter 19 The Dynamic Performance Tables

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

Formatting EXPLAIN PLAN Output

How to Run EXPLAIN PLAN

Selecting PLAN_TABLE Output in Table Format

Selecting PLAN_TABLE Output in Nested Format

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

TKPROF Sample Output

Header

Body

Summary

Introduction

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

Oracle Trace Initialization Parameters

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

PART VI: Appendices

Optimizer Concepts A-1

Evaluating Expressions and Conditions

Transforming ORs into Compound Queries

Transforming Complex Statements into Join Statements

Optimizing Statements that Access Views

Transforming Statements that Access Views

Optimizing Statements that Cannot Be Transformed

Choosing an Optimization Approach and Goal

OPTIMIZER_MODE Initialization Parameter

OPTIMIZER_GOAL Parameter of the ALTER SESSION Command

The FIRST_ROWS, ALL_ROWS, and RULE Hints

Choosing Access Paths

Choosing Access Paths with the Cost-Based Approach

Optimizing Join Statements

Optimizer Choices for Join Statements

Join Operations

Choosing Execution Plans for Join Statements

Optimizing Star Queries

The Star Query Environment

Tuning Star Queries

Optimizing Compound Queries

Using Histograms

Height-Balanced Histograms

Advantages of Height-Balanced Histograms

SQL Processing Concepts B-1

SQL Statement Execution

DML Statement Processing

Stage 1: Create a Cursor

Stage 2: Parse the Statement

Query Processing

Stage 3: Describe Results

Stage 4: Defining Output

Stage 5: Bind Any Variables

Stage 6: Execute the Statement

Stage 7: Parallelize the Statement

Stage 8: Fetch Rows of a Query Result

DDL Statement Processing

Parallel Query Concepts C-1

Parallel Query Processing

Parallel Query Process Architecture

CREATE TABLE ... AS SELECT in Parallel

Parallelizing SQL Statements

Parallelizing Operations

Partitioning Rows to Each Query Server

Setting the Degree of Parallelism

Determining the Degree of Parallelism for Operations

Hints

Table and Cluster Definition Syntax

Default Degree of Parallelism

Minimum Number of Query Servers

Limiting the Number of Available Instances

Managing the Query Servers

Varying Pool of Query Server Processes

Copyright © 1996 Oracle Corporation 500 Oracle Parkway, Redwood City, CA 94065

Library

Product