Oracle8(TM) Server Tuning
Release 8.0
A54638-01

Library

Product

Index


Next

Contents

Title and Copyright Information

Preface

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

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

Chapter 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

Chapter 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
Tools You May Have Developed

PART 2 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
Oracle Configurations
Distributed Systems
The Oracle Parallel Server
Client/Server Configurations

Chapter 6 Designing Data Warehouse Applications

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

PART 3 OPTIMIZING DATABASE OPERATIONS

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

Chapter 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 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
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
STAR_TRANSFORMATION
Hints for Join Operations
USE_NL
USE_MERGE
NO_MERGE
USE_HASH
DRIVING_SITE
Hints for Parallel Execution
PARALLEL
NOPARALLEL
APPEND
NOAPPEND
PARALLEL_INDEX
Additional Hints
CACHE
NOCACHE
PUSH_SUBQ

Chapter 9 Tuning Distributed Queries

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

Chapter 10 Data Access Methods

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

Chapter 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

Chapter 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 4 OPTIMIZING ORACLE INSTANCE PERFORMANCE

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

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

Chapter 15 Tuning I/O

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

Chapter 16 Tuning Networks

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

Chapter 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

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

Chapter 19 Tuning Parallel Execution

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
Types of Parallelism
A Case Study
Striping Data
Partitioning Data
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

PART 5 PERFORMANCE DIAGNOSTIC TOOLS

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

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

Chapter 22 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: 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

Chapter 23 Using Oracle Trace

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

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

Index



Next
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.

Library

Product

Index