Oracle8 Tuning
Release 8.0

A58246-01

Library

Product

Index

Next

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
Who Tunes?
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
Keep Records
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
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
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

Introduction
Features for Building a Data Warehouse
Parallel CREATE TABLE . . . AS SELECT
Parallel Index Creation
Fast Full Index Scan
Partitioned Tables
ANALYZE Command
Parallel Load
Features for Querying a Data Warehouse
Oracle Parallel Server Option
Parallel-Aware Optimizer
Parallel Execution
Bitmap Indexes
Star Queries
Star Transformation
Backup and Recovery of the Data Warehouse

Part III Optimizing Database Operations

7 Tuning Database Operations

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 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
ALL_ROWS
FIRST_ROWS
CHOOSE
RULE
Hints for Access Methods
FULL
ROWID
CLUSTER
HASH
HASH_AJ
HASH_SJ
INDEX
INDEX_ASC
INDEX_COMBINE
INDEX_DESC
INDEX_FFS
MERGE_AJ
MERGE_SJ
AND_EQUAL
USE_CONCAT
Hints for Join Orders
ORDERED
STAR
Hints for Join Operations
USE_NL
USE_MERGE
USE_HASH
DRIVING_SITE
Hints for Parallel Execution
PARALLEL
NOPARALLEL
APPEND
NOAPPEND
PARALLEL_INDEX
NOPARALLEL_INDEX
Additional Hints
CACHE
NOCACHE
MERGE
NO_MERGE
PUSH_JOIN_PRED
NO_PUSH_JOIN_PRED
PUSH_SUBQ
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

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 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 Clusters
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
Usage Notes
Example
Using Serializable Transactions

12 Managing SQL and Shared PL/SQL Areas

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

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

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
Striping Data
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

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 There Skew?
Executing Parallel SQL Statements
Using EXPLAIN PLAN to See How an Operation Is Parallelized
Using the Dynamic Performance Views
V$FILESTAT
V$PARAMETER
V$PQ_SESSTAT
V$PQ_SLAVE
V$PQ_SYSSTAT
V$PQ_TQSTAT
V$SESSTAT and V$SYSSTAT
Querying the Dynamic Performance Views: Example
Checking Operating System Statistics
Minimum Recovery Time
Parallel DML Restrictions

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

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

24 The SQL Trace Facility and TKPROF

Introduction
About the SQL Trace Facility
About TKPROF
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
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
Header
Body
Summary

25 Using Oracle Trace

Introduction
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

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


Next
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Index