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

Reactive Tuning to Improve Production Systems

Prioritized Tuning Steps

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)

Applying the Tuning Method

Set Clear Goals for Tuning

Create Minimum Repeatable Tests

Test Hypotheses

Keep Records and Automate Testing

Avoid Common Errors

Stop Tuning When Objectives Are Met

Demonstrate Meeting the Objectives

Types of Applications

Online Transaction Processing (OLTP)

Data Warehousing

Multipurpose Applications

Oracle Configurations

Distributed Systems

The Oracle Parallel Server

Client/Server Configurations

Tuning Goals

Tuning a Serial SQL Statement

Tuning Parallel Execution

Tuning OLTP Applications

Methodology for Tuning Database Operations

Step 1: Find the Statements that Consume the Most Resources

Step 2: Tune These Statements To Use Fewer Resources

Approaches to SQL Statement Tuning

Restructure the Indexes

Restructure the Statement

Modify or Disable Triggers

Restructure the Data

Keeping Statistics Current and Using Plan Stability to Preserve Execution Plans

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

Using Indexes

When to Create Indexes

Tuning the Logical Structure

Choosing Columns and Expressions to Index

Choosing Composite Indexes

Writing Statements that Use Indexes

Writing Statements that Avoid Using Indexes

Assessing the Value of Indexes

Using Fast Full Index Scans

Re-creating Indexes

Compacting Indexes

Using Nonunique Indexes to Enforce Uniqueness

Using Enabled Novalidated Constraints

Using Function-based Indexes

Function-based Indexes and Index Organized Tables

Using Bitmap Indexes

When to Use Bitmap Indexes

Creating Bitmap Indexes

Initialization Parameters for Bitmap Indexing

Using Bitmap Access Plans on Regular B*-tree Indexes

Estimating Bitmap Index Size

Bitmap Index Restrictions

Using Domain Indexes

Using Clusters

Using Hash Clusters

When to Use a Hash Cluster

Creating Hash Clusters

Using Cost-based Optimization

When to Use the Cost-based Approach

Using the Cost-based Approach

Choosing a Goal for the Cost-based Approach

Using Histograms for Nonuniformly Distributed Data

Generating Statistics

Gathering Statistics with the DBMS_STATS Package

Gathering New Optimizer Statistics

Automated Statistics Gathering

Parameters Affecting Cost-based Optimization Plans

Parameters Affecting How the Optimizer Uses Indexes

Tips for Using the Cost-based Approach

Using Rule-Based Optimization

Using Plan Stability to Preserve Execution Plans

Plan Stability Uses Hints and Exact Text Matching

Matching SQL Statements with Outlines

How Oracle Stores Outlines

Parameter Settings to Enable Plan Stability

Creating Outlines

Creating and Assigning Categories to Stored Outlines

Using Stored Outlines

Viewing Outline Data

Managing Stored Outlines with the OUTLN_PKG Package

Moving Outline Tables

Plan Stability Procedures for the Cost-based Optimizer

Using Outlines to Move to the Cost-based Optimizer

RDBMS Upgrades and the Cost-based Optimizer

Using Hints

Specifying Hints

Hints for Optimization Approaches and Goals

Hints for Access Methods

Hints for Join Orders

Hints for Join Operations

Hints for Parallel Execution

Additional Hints

Using Hints with Views

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

Using Discrete Transactions

Deciding When to Use Discrete Transactions

How Discrete Transactions Work

Errors During Discrete Transactions

Usage Notes

Example

Using Serializable Transactions

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

Characteristics of Data Warehouse Applications

Building a Data Warehouse

Materialized Views and Dimensions

Parallel CREATE TABLE . . . AS SELECT

Parallel Index Creation

Querying a Data Warehouse

Oracle Parallel Server Option

Parallel-Aware Optimizer

Tuning Data Warehouse Applications

Backup and Recovery of the Data Warehouse

Tuning Fast-start Parallel Recovery

Sources of Data for Tuning

Data Volumes

Online Data Dictionary

Operating System Tools

Dynamic Performance Tables

Oracle Trace and Oracle Trace Data Viewer

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

Oracle Trace and Oracle Trace Data Viewer

The SQL Trace Facility and TKPROF

Supported Scripts

Application Registration

Oracle Enterprise Manager, Packs, and Applications

Introduction to Oracle Enterprise Manager

Oracle Diagnostics Pack

Oracle Tuning Pack

Oracle Parallel Server Management

Tools You May Have Developed

Introduction to EXPLAIN PLAN

Creating the Output Table

Displaying PLAN_TABLE Output

Output Table Columns

Bitmap Indexes and EXPLAIN PLAN

EXPLAIN PLAN and Partitioned Objects

Examples of How EXPLAIN PLAN Displays Range and Hash Partitioning

Pruning Information with Composite Partitioned Objects

Partial Partition-wise Joins

Full Partition-wise Joins

INLIST ITERATOR and EXPLAIN PLAN

DOMAIN INDEX and EXPLAIN PLAN

Formatting EXPLAIN PLAN Output

Using the EXPLAIN PLAN Statement

Selecting PLAN_TABLE Output in Table Format

Selecting PLAN_TABLE Output in Nested Format

EXPLAIN PLAN Restrictions

Introduction to SQL Trace and TKPROF

About the SQL Trace Facility

About TKPROF

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

Introduction to Oracle Trace

Using Oracle Trace Data

Using Oracle Trace Manager

Managing Collections

Collecting Event Data

Accessing Collected Data

Using Oracle Trace Data Viewer

Oracle Trace Predefined Data Views

Viewing Oracle Trace Data

SQL Statement Property Page

Details Property Page

Example of Details Property Page

Getting More Information on a Selected Query

Manually Collecting Oracle Trace Data

Using the Oracle Trace Command-Line Interface

Using Initialization Parameters to Control Oracle Trace

Using Stored Procedures to Control Oracle Trace

Oracle Trace Collection Results

Formatting Oracle Trace Data to Oracle Tables

Oracle Trace Statistics Reporting Utility

Instance-Level Views for Tuning

Session-Level or Transient Views for Tuning

Current Statistic Values and Rates of Change

Finding the Current Value of a Statistic

Finding the Rate of Change of a Statistic

Tuning Factors for Well Designed Existing Systems

Insufficient CPU

Insufficient Memory

Insufficient I/O

Network Constraints

Software Constraints

Understanding CPU Problems

Detecting and Solving CPU Problems

System CPU Utilization

Oracle CPU Utilization

Solving CPU Problems by Changing System Architectures

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

Detecting Memory Allocation Problems

Solving Memory Allocation Problems

Tuning Operating System Memory Requirements

Reducing Paging and Swapping

Fitting the System Global Area into Main Memory

Allocating Adequate 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 Large Pool and Shared Pool for the Multi-threaded Server Architecture

Reducing Memory Use With Three-Tier Connections

The V$SESSTAT View

Querying the V$SESSTAT View

Tuning Reserved Space from the Shared Pool

Reserved List Tuning Parameters

Controlling Space Reclamation of the Shared Pool

Initial Parameter Values

SHARED_POOL_ RESERVED_SIZE Too Small

SHARED_POOL_ RESERVED_SIZE Too Large

SHARED_POOL_SIZE Too Small

Tuning the Buffer Cache

Evaluating Buffer Cache Activity by Means of the Cache Hit Ratio

Increasing the Cache Hit Ratio by Reducing Buffer Cache Misses

Removing Unnecessary Buffers when Cache Hit Ratio Is High

Accommodating LOBs in the Buffer Cache

Temporary LOBs

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

Sizing Each Buffer Pool

Identifying and Eliminating LRU Latch Contention

Tuning Sort Areas

Reallocating Memory

Reducing Total Memory Usage

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

Detecting I/O Problems

Checking System I/O Utilization

Checking Oracle I/O Utilization

Solving 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

The Purpose of Striping

I/O Balancing and Striping

Striping Disks Manually

Striping Disks with Operating System Software

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

Sorting to Disk

Optimizing Sort Performance with Temporary Tablespaces

Improving Sort Performance by Striping Temporary Tablespaces

Improving Sort Performance Using SORT_MULTIBLOCK_READ_COUNT

Using NOSORT to Create Indexes Without Sorting

GROUP BY NOSORT

Tuning Checkpoints

How Checkpoints Affect Performance

Choosing Checkpoint Frequency

Fast-Start Checkpointing

Tuning LGWR and DBWn I/O

Tuning LGWR I/O

Tuning DBWn I/O

Tuning Backup and Restore Operations

Locating the Source of a Bottleneck

Using Fixed Views to Monitor Backup Operations

Improving Backup Throughput

Configuring the Large Pool

Understanding Contention Issues

Detecting Contention Problems

Solving Contention Problems

Reducing Contention for Rollback Segments

Identifying Rollback Segment Contention

Creating Rollback Segments

Reducing Contention for Multi-threaded Server Processes

Identifying Contention Using the Dispatcher-specific Views

Reducing Contention for Dispatcher Processes

Reducing Contention for Shared Server Processes

Reducing Contention for Parallel Execution Servers

Identifying Contention for Parallel Execution Servers

Reducing Contention for Parallel Execution Servers

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

Detecting Network Problems

Solving 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

Setting Up MTS

Application Types that Benefit from MTS

Improving User Scalability with MTS

Configuring Dispatchers

Connection Pooling and Connection Multiplexing

Maximizing Throughput and Response Time with MTS

Configuring and Managing the Number of Shared Servers

Tuning the SDU Size

Balancing Load Connections

Tuning Memory Use with MTS

Configuring the Large Pool and Shared Pool for MTS

Limiting Memory Use Per User Session by Setting PRIVATE_SGA

MTS-related Views with Connection, Load and Statistics Data

MTS Feature Performance Issues

Understanding Operating System Performance Issues

Operating System and Hardware Caches

Raw Devices

Process Schedulers

Detecting Operating System Problems

Solving Operating System Problems

Performance on UNIX-Based Systems

Performance on NT Systems

Performance on Mainframe Computers

Understanding Instance Recovery

How Oracle Applies Redo Log Information

Trade-offs of Minimizing Recovery Duration

Tuning the Duration of Instance and Crash Recovery

Using Initialization Parameters to Influence Instance and Crash Recovery Time

Using Redo Log Size to Influence Checkpointing Frequency

Using SQL Statements to Initiate Checkpoints

Monitoring Instance Recovery

Tuning the Phases of Instance Recovery

Tuning the Rolling Forward Phase

Tuning the Rolling Back Phase

Transparent Application Failover

What Is Transparent Application Failover?

How does Transparent Application Failover Work?

Transparent Application Failover Implementation Scenarios

Transparent Application Failover Topics for the DBA

Transparent Application Failover Topics for Application Developers

Transparent Application Failover Restrictions

Introduction to Parallel Execution Tuning

When to Implement Parallel Execution

Phase One - Initializing and Tuning Parameters for Parallel Execution

Step One: Selecting Automated or Manual Tuning of Parallel Execution

Automatically Derived Parameter Settings under Fully Automated Parallel Execution

Step Two: Setting the Degree of Parallelism and Enabling Adaptive Multi-User

Degree of Parallelism and Adaptive Multi-User and How They Interact

Enabling Parallelism for Tables and Queries

Controlling Performance with PARALLEL_THREADS_PER_CPU

Step Three: Tuning General Parameters

Parameters Establishing Resource Limits for Parallel Operations

Parameters Affecting Resource Consumption

Parameters Related to I/O

Example Parameter Setting Scenarios for Parallel Execution

Example One: Small Datamart

Example Two: Medium-sized Data Warehouse

Example Three: Large Data Warehouse

EXAMPLE Four: Very Large Data Warehouse

Phase Two - Tuning Physical Database Layouts for Parallel Execution

Phase Three - Creating, Populating, and Refreshing the Database

Populating Databases Using Parallel Load

Creating Temporary Tablespaces for Parallel Sort and Hash Join

Creating Indexes in Parallel

Executing Parallel SQL Statements

Using EXPLAIN PLAN to Show Parallel Operations Plans

Additional Considerations for Parallel DML

Phase Four - Monitoring Parallel Execution Performance

Monitoring Parallel Execution Performance with Dynamic Performance Views

Monitoring Session Statistics

Monitoring Operating System Statistics

Understanding Parallel Execution Performance Issues

Formula for Memory, Users, and Parallel Execution Server Processes

Setting Buffer Pool Size for Parallel Operations

Balancing the Formula

Examples: Balancing Memory, Users, and Parallel Execution Servers

Parallel Execution Space Management Issues

Tuning Parallel Execution on Oracle Parallel Server

Parallel Execution Tuning Tips

Overriding the Default Degree of Parallelism

Rewriting SQL Statements

Creating and Populating Tables in Parallel

Creating Indexes in Parallel

Parallel DML Tips

Refreshing Tables in Parallel

Using Hints with Cost Based Optimization

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 The Workload Evenly Distributed?

Overview of Data Warehousing with Materialized Views

Materialized Views for Data Warehouses

Materialized Views for Distributed Computing

Materialized Views for Mobile Computing

Components of Summary Management

Terminology

Materialized Views

Schema Design Guidelines for Materialized Views

Oracle Tools for Data Warehousing

Getting Started

The Need for Materialized Views

Creating a Materialized View

Naming

Storage Characteristics

Build Methods

Used for Query Rewrite

Query Rewrite Restrictions

Refresh Options

Defining the Data for the Materialized View

Registration of an Existing Materialized View

Partitioning a Materialized View

Partitioning the Materialized View

Partitioning a Prebuilt Table

Indexing Selection for Materialized Views

Invalidating a Materialized View

Security Issues

Guidelines for using Materialized Views in a Data Warehouse

Altering a Materialized View

Dropping a Materialized View

Dimensions in a Data Warehouse

Creating a Dimension

Multiple Hierarchies

Using Normalized Dimension Tables

Viewing Dimensions

Dimensions and Constraints

Validating a Dimension

Altering a Dimension

Deleting a Dimension

Overview of Query Rewrite

Cost-Based Rewrite

Enabling Query Rewrite

Initialization Parameters for Query Rewrite

Privileges for Enabling Query Rewrite

When Does Oracle Rewrite a Query?

Query Rewrite Methods

SQL Text Match Rewrite Methods

General Query Rewrite Methods

When are Constraints and Dimensions Needed?

Accuracy of Query Rewrite

Did Query Rewrite Occur?

Explain Plan

Controlling Query Rewrite

Guidelines for Using Query Rewrite

Overview of Materialized View Management

Warehouse Refresh

Complete Refresh

Fast Refresh

Tips for Refreshing Using Warehouse Refresh

Recommended Initialization Parameters for Parallelism

Monitoring a Refresh

Tips after Refreshing Materialized Views

Summary Advisor

Collecting Structural Statistics

Collection of Dynamic Workload Statistics

Recommending Materialized Views

Estimating Materialized View Size

Is a Materialized View Being Used?

Contents