Deploying New Applications

Rollout Strategies

Performance Checklist

< a href="technique.htm#10796">3 Performance Improvement Methods

The Oracle Performance Improvement Method < dl class="H2TOC">
Steps in The Oracle Performance Improvement Method

A Sample Decision Process for Performance Conceptual Modeling

Top Ten Mistakes Found in Oracle Systems

Emergency Performance Methods

Steps in the Emergency Performance Method

Part III Optimizing Instance Performance

4 Configuring a Database for Performance

Performance Considerations for Initial Instance Configuration

Initialization Parameters

Configuring Undo Space

Sizing Redo Log Files

Creating Subsequent Tablespaces

Creating and Maintaining Tables for Good Performance

Table Compression

Reclaiming Unused Space

Indexing Data

Performance Considerations for Shared Servers

Identifying Contention Using the Dispatcher-Specific Views

Identifying Conte ntion for Shared Servers

5 Automatic Performance Statistics

Overview of Data Gathering

Database Stat istics

Operating System Statistics

Interpreting Statistics

Automati c Workload Repository

Accessing the Automatic Workloa d Repository with Oracle Enterprise Manager

Managing Snapshot and Baseline Da ta with APIs

Workload Repository Views

Workload Repository Reports

6 Automatic Performance Diagnostics

Introduction to Database Diagnostic Monitoring

Automatic Database Diagnostic Monitor

ADDM Analysis Results

An ADDM Example

Setting Up ADDM

Accessing ADDM with Orac le Enterprise Manager

Diagnosing Database Performance Issues with ADDM
Views with ADDM Information

7 Memory Configuration and Use

Understanding Memory Allocation Issues

Oracle Memory Caches

Automatic Shared Memory Management

Dynamically Changing Cache Sizes

Application Considerations

Operating System Memory U se

Iteration During Configuration

Configuring and Using the Buffer Cache

Using the Buffer Cache Effectively

Sizing the Buffer Ca che

Interpreting and Using the Buffer Cache Advisory Statistics

Considering Multiple Buffer Pools

Buffer Pool Data in V$DB_CACHE_ADVICE

Buffer Pool Hit Ratios

Determining Which Segments Have Many Buffers in the Pool

KEEP Pool

RECYCLE Pool

Configuring and Using the Shared Pool and Large Pool

Shared Pool Concepts

Using the Shared Pool E ffectively

Sizing the Shared Pool

Interpreting Shared Pool Statistics

Using the Large Pool

Using CURSOR_SPACE_FOR_TIME

Caching Se ssion Cursors

Configuring the Reserved Pool

Keeping Large Objects to Prevent Aging

CURSOR_SHARING for Ex isting Applications

Maintaining Connections

Configuring and Using the Redo Log Buffer

Sizing the Log Buffer

Log Buffer Statistic s

PGA Memory Management

Configuring Automatic PGA Memory

Configuring OLAP_PAGE_POOL_SIZE

8 I/O Configuration and Design

Understanding I/O

Basic I/O Configuration

Lay Out the Files Using Operating S ystem or Hardware Striping

Manually Distributing I/O

When to Separate Files

Three Sample Configura tions

Oracle-Managed Files

Choosing Data Block Size

9 Understanding Operating System Resources

Understanding Operating System Performance Issues

Using Operating System Caches

Memory Usage

Using Operating System Resource Managers

Solving Operating System Problems

Performance Hints on UNIX-Based Systems

Performance Hints on Windows Syste ms

Performance Hints on Midrange and Mainframe Computers

Understanding CPU

< a href="ch23_os.htm#4230">Context Switching

Find ing System CPU Utilization

Checking Memory Management

Checking I/O Management

Ch ecking Network Management

Checking Process Management
< h3 class="TitleTOC">10 Instance Tuning Using Performance Views

Instance Tuning Ste ps

Define the Problem

Examine the Host System

Examine the O racle Statistics

Implement and Measure Change

Interpreting Oracle Statistics

Examine Load

Using Wait Event Statistics to Drill Down to Bottlenecks

Table of Wait Events and Potential Causes

Additional Statistics

Wait Events Statistics

SQL*Net Events

buffer busy waits
db file scattered read

db file sequential read

direct path read and direct path read tem p

direct path write and direct path write temp

enqueue (enq:) waits

free buffer wai ts

latch events

log file parallel write

library cache pin

library cache lock

log buffer space< /a>

log file switch

log file sync

rdbms ipc reply

Idle Wait Events

11 Tuning Networks

Understanding Connection Models

Shared Server Configuration

De tecting Network Problems

Using Dynamic Performance View s for Network Performance

Understanding Latency and Bandwidth

Solving Network Problems

Finding Network Bottlenecks

Dissecting Ne twork Bottlenecks

Using Array Interfaces

Adjusting Session Data Unit Buffer Size

Using TCP.NODELAY
Using Connection Manager

Part IV Optimizing SQL Statements

12 SQL Tuning Overview

Introduction to SQL Tuning

Goals for Tuning

Reduce the Workload

Balance the Workload

Parallelize the Workload

Identifying High-Load SQL

Identifying Resource-Intens ive SQL

Gathering Data on the SQL Identified

Automatic SQL Tuning Features

Developing Efficient SQL Statements

Verifying Optimizer Statistics

Reviewing the Execution P lan

Restructuring the SQL Statements

Controlling the Access Path and Join Order with Hints

Restruc turing the Indexes

Modifying or Disabling Triggers and Constraints
< dd class="H2TOC">Restructuring the Data

Maintain ing Execution Plans Over Time

Visiting Data as Few Times as Possible

13 Automatic SQL Tuning

Automatic SQL Tuning Overview

Query Optimizer Modes

Types of Tuning Analysis

SQL Tuning Advisor

Input Sources

Tuning Options

Advisor Output

Accessing the SQL Tuning Advisor with Oracle Enterprise Manager

Using SQL Tuning Advisor APIs

Managing SQL Profiles with APIs

Ac cepting a SQL Profile

Altering a SQL Profile

Dropping a SQL Profile

SQL Tuning Sets

Accessing SQL Tuning Sets with Oracl e Enterprise Manager

Managing SQL Tuning Sets

SQL Tuning Information Views

14 The Query Optimizer

Optimizer Operations

Choosing an Optimizer Goal

OPTIMIZER_MODE Initialization Parameter

Optimizer SQL Hints for Changin g the Query Optimizer Goal

Query Optimizer Statistics in the Data Dictionary< /a>

Enabling and Controlling Query Optimizer Featu res

Enabling Query Optimizer Features

Controlling the Behavior of the Query Optimizer

Understanding the Query Optimizer

Components of the Query Optimizer

Reading and Underst anding Execution Plans

Understanding Access Pa ths for the Query Optimizer

Full Table Scans

Rowid Scans

Index Scans
Cluster Access

Hash Access

Sample Table Scans

H ow the Query Optimizer Chooses an Access Path

Understanding Joins

How the Query Optimizer Executes Join Statements

How the Query Optimizer Chooses Execution Plans for Joins

Nested Loop Joins

Hash J oins

Sort Merge Joins

Cartesian Joins

Outer Joins

15 Managing Optimizer Statistics

Understanding Statistics

Automatic Statistics Gathering

GATHER_STATS_JOB

Enabling Automatic Statistics Gathering

Considerations When Gathering Statistics

Manual Statistics Gathering

Gathering Statistics with DBMS_STATS Procedures

When to Gather Statisti cs

System Statistics

Managing Statistics

Restoring Previous Versions of Statistics

Exporting and Impor ting Statistics

Restoring Statistics Versus Importing or Exporting Statistics

Locking Statistics for a Table or Schema

Setting Statistics

Estimating Statistics with Dynamic Sampling
Handling Missing Statistics

Viewing Statistics

Statistics on Tables, Indexes and Columns

Viewing Histograms

16 Using Indexes and Clusters

Understanding Index Performance
Tuning the Logical Structure

Index Tuning using the SQLAccess Advisor

Choosing Columns and E xpressions to Index

Choosing Composite Indexes

Writing Statements That Use Indexes

Writing Statements T hat Avoid Using Indexes

Re-creating Indexes

Compacting Indexes

Using Nonunique Indexes to Enforce Uni queness

Using Enabled Novalidated Constraints

Using Function-based Indexes for Performance

Using Partitioned Indexes for Performance

Using Index-Organized Tables for Performance

Using Bitmap Join Indexes for Performance

< a href="data_acc.htm#7783">Using Domain Indexes for Performance

Using Clusters for Performance

Using Hash Clusters for Performance

17 Optimizer Hints

Understanding Optimizer Hints

Type of Hints

Specifying Hints

Using H ints with Views

Using Optimizer Hints

Hints for Optimization Approaches and Goals

Hints for Access Paths

Hints for Query T ransformations

Hints for Join Orders

Hints for Join Operations

Hints for Parallel Execution

Additional Hints

18 Using Plan Stability

Using Plan Stability to Preserve Execution Plans

Using Hints with Plan Stability

Storing O utlines

Enabling Plan Stability

Using Supplied Packages to Manage Stored Outlines

Creating Outline s

Using and Editing Stored Outlines

Viewing Outline Data

Moving Outline Tables

Using Plan Stability with Query Optimizer Upgrades

Moving from RBO to the Query Optimizer

< a href="outlines.htm#19357">Moving to a New Oracle Release under the Query Optimizer

19 Using EXPLAIN PLAN

Understanding EXPLAIN PLAN

How Execution Plans Can Change

Minimizing Throw-A way

Looking Beyond Execution Plans

EXPLAIN PLAN Restrictions

The PLAN_ TABLE Output Table

Running EXPLAIN PLAN

Identifying Statements for EXPLAIN PLAN

Specifying Different Tables for EXPLAIN PLAN

Displaying PLAN_TABLE Output

Customizing PLAN_TABLE Output

Reading EXP LAIN PLAN Output

Viewing Parallel Execution with EXPLAIN PLAN

Viewing Parallel Queries with EXPLAIN PL AN

Viewing Bitmap Indexes with EXPLAIN PLAN

Viewing Partitioned Objects with EXPLAIN PLAN

Examples of Displaying Range and Hash Partitioning with EXPL AIN PLAN

Examples of Pruning Information with Composite Partitioned Objects

Examples of Partial Partition-wise Joins

Examples of Full Partition-wise Joins

Examples of INLIST ITERATO R and EXPLAIN PLAN

Example of Domain Indexes and EXPLAIN PLAN

PLAN_TABLE Columns

20 Using Application Tracing Tools

End to End Application Tracing

Accessing the End to End Tracing with Oracle Enterprise Manager

Managing End to End Tracing with APIs and Views

Using the trcsess Utility

Syntax for trcsess

Sample Output of trcsess

Understanding SQL Trace and TKPROF

Understanding the SQL Trace Facility

Understanding TKPROF

Using the SQL Trac e Facility and TKPROF

Step 1: Setting Initialization P arameters for Trace File Management

Step 2: Enabling the SQL Trace Facility

Step 3: Formatting Trace Files with TKPROF

Step 4: Interpreting TKPROF Output

Step 5: Storing SQL Trace Facility Statistics

Avoiding Pitfalls in TKPRO F Interpretation

Avoiding the Argument Trap
< dd class="H2TOC">Avoiding the Read Consistency Trap

Avoiding the Schema Trap

Avoiding the Time Trap

< a href="sqltrace.htm#1625">Avoiding the Trigger Trap

Sample TKPROF Output

Sample TKPROF Header
Sample TKPROF Body

Sample T KPROF Summary

Gl ossary

Index

Contents

Title and Copyright Information

Send Us Your Comments

Preface

What's New in Oracle Performance?

Part I Performance Tuning

1 Performance Tuning Overview

Part II Performance Planning

2 Designing and Developing for Performance

< a href="technique.htm#10796">3 Performance Improvement Methods

Part III Optimizing Instance Performance

4 Configuring a Database for Performance

5 Automatic Performance Statistics

6 Automatic Performance Diagnostics

7 Memory Configuration and Use

8 I/O Configuration and Design

9 Understanding Operating System Resources

11 Tuning Networks

Part IV Optimizing SQL Statements

12 SQL Tuning Overview

13 Automatic SQL Tuning

14 The Query Optimizer

15 Managing Optimizer Statistics

16 Using Indexes and Clusters

17 Optimizer Hints

18 Using Plan Stability

19 Using EXPLAIN PLAN

20 Using Application Tracing Tools

Gl ossary

Index