Oracle7 Tuning, release 7.3.3 Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Index
Index


Preface

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

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

Chapter 6 Designing Data Warehouse Applications

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

Chapter 8 Data Access Methods

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

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

Chapter 11 Managing Partition Views

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

Chapter 13 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 Is High
Tuning Sort Areas
Reallocating Memory
Reducing Total Memory Usage

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

Chapter 15 Tuning Networks

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

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

Chapter 17 Tuning the Operating System

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

Chapter 18 Parallel Query Tuning

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

Chapter 20 The EXPLAIN PLAN Command

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

Chapter 21 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 22 Oracle Trace Methodology

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

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

PART VI: Appendices

Optimizer Concepts

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

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

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

Index


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


Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Index
Index