Part I Performance Tuning
1 Performance Tuning Overview

    1.1 Introduction to Performance Tuning

        1.1.1 Performance Planning
        1.1.2 Instance Tuning
        1.1.3 SQL Tuning

    1.2 Introduction to Performance Tuning Features and Tools

        1.2.1 Automatic Performance Tuning Features
        1.2.2 Additional Oracle Tools

Part II Performance Planning
2 Designing and Developing for Performance

    2.1 Oracle Methodology
    2.2 Understanding Investment Options
    2.3 Understanding Scalability

        2.3.1 What is Scalability?
        2.3.2 System Scalability
        2.3.3 Factors Preventing Scalability

    2.4 System Architecture

        2.4.1 Hardware and Software Components
        2.4.2 Configuring the Right System Architecture for Your Requirements

    2.5 Application Design Principles

        2.5.1 Simplicity In Application Design
        2.5.2 Data Modeling
        2.5.3 Table and Index Design
        2.5.4 Using Views
        2.5.5 SQL Execution Efficiency
        2.5.6 Implementing the Application
        2.5.7 Trends in Application Development

    2.6 Workload Testing, Modeling, and Implementation

        2.6.1 Sizing Data
        2.6.2 Estimating Workloads
        2.6.3 Application Modeling
        2.6.4 Testing, Debugging, and Validating a Design

    2.7 Deploying New Applications

        2.7.1 Rollout Strategies
        2.7.2 Performance Checklist

3 Performance Improvement Methods

    3.1 The Oracle Performance Improvement Method

        3.1.1 Steps in The Oracle Performance Improvement Method
        3.1.2 A Sample Decision Process for Performance Conceptual Modeling
        3.1.3 Top Ten Mistakes Found in Oracle Systems

    3.2 Emergency Performance Methods

        3.2.1 Steps in the Emergency Performance Method

Part III Optimizing Instance Performance
4 Configuring a Database for Performance

    4.1 Performance Considerations for Initial Instance Configuration

        4.1.1 Initialization Parameters
        4.1.2 Configuring Undo Space
        4.1.3 Sizing Redo Log Files
        4.1.4 Creating Subsequent Tablespaces

    4.2 Creating and Maintaining Tables for Good Performance

        4.2.1 Table Compression
        4.2.2 Reclaiming Unused Space
        4.2.3 Indexing Data

    4.3 Performance Considerations for Shared Servers

        4.3.1 Identifying Contention Using the Dispatcher-Specific Views
        4.3.2 Identifying Contention for Shared Servers

5 Automatic Performance Statistics

    5.1 Overview of Data Gathering

        5.1.1 Database Statistics
        5.1.2 Operating System Statistics
        5.1.3 Interpreting Statistics

    5.2 Automatic Workload Repository

        5.2.1 Accessing the Automatic Workload Repository with Oracle Enterprise Manager
        5.2.2 Managing Snapshot and Baseline Data with APIs
        5.2.3 Automatic Workload Repository Views
        5.2.4 Automatic Workload Repository Reports
        5.2.5 Active Session History Reports

6 Automatic Performance Diagnostics

    6.1 Introduction to Database Diagnostic Monitoring
    6.2 Automatic Database Diagnostic Monitor

        6.2.1 ADDM Analysis Results
        6.2.2 An ADDM Example
        6.2.3 Setting Up ADDM
        6.2.4 Accessing ADDM with Oracle Enterprise Manager
        6.2.5 Diagnosing Database Performance Issues with ADDM
        6.2.6 Views with ADDM Information

7 Memory Configuration and Use

    7.1 Understanding Memory Allocation Issues

        7.1.1 Oracle Memory Caches
        7.1.2 Automatic Shared Memory Management
        7.1.3 Dynamically Changing Cache Sizes
        7.1.4 Application Considerations
        7.1.5 Operating System Memory Use
        7.1.6 Iteration During Configuration

    7.2 Configuring and Using the Buffer Cache

        7.2.1 Using the Buffer Cache Effectively
        7.2.2 Sizing the Buffer Cache
        7.2.3 Interpreting and Using the Buffer Cache Advisory Statistics
        7.2.4 Considering Multiple Buffer Pools
        7.2.5 Buffer Pool Data in V$DB_CACHE_ADVICE
        7.2.6 Buffer Pool Hit Ratios
        7.2.7 Determining Which Segments Have Many Buffers in the Pool
        7.2.8 KEEP Pool
        7.2.9 RECYCLE Pool

    7.3 Configuring and Using the Shared Pool and Large Pool

        7.3.1 Shared Pool Concepts
        7.3.2 Using the Shared Pool Effectively
        7.3.3 Sizing the Shared Pool
        7.3.4 Interpreting Shared Pool Statistics
        7.3.5 Using the Large Pool
        7.3.6 Using CURSOR_SPACE_FOR_TIME
        7.3.7 Caching Session Cursors
        7.3.8 Configuring the Reserved Pool
        7.3.9 Keeping Large Objects to Prevent Aging
        7.3.10 CURSOR_SHARING for Existing Applications
        7.3.11 Maintaining Connections

    7.4 Configuring and Using the Redo Log Buffer

        7.4.1 Sizing the Log Buffer
        7.4.2 Log Buffer Statistics

    7.5 PGA Memory Management

        7.5.1 Configuring Automatic PGA Memory
        7.5.2 Configuring OLAP_PAGE_POOL_SIZE

8 I/O Configuration and Design

    8.1 Understanding I/O
    8.2 Basic I/O Configuration

        8.2.1 Lay Out the Files Using Operating System or Hardware Striping
        8.2.2 Manually Distributing I/O
        8.2.3 When to Separate Files
        8.2.4 Three Sample Configurations
        8.2.5 Oracle-Managed Files
        8.2.6 Choosing Data Block Size

9 Understanding Operating System Resources

    9.1 Understanding Operating System Performance Issues

        9.1.1 Using Operating System Caches
        9.1.2 Memory Usage
        9.1.3 Using Operating System Resource Managers

    9.2 Solving Operating System Problems

        9.2.1 Performance Hints on UNIX-Based Systems
        9.2.2 Performance Hints on Windows Systems
        9.2.3 Performance Hints on HP OpenVMS Systems

    9.3 Understanding CPU
    9.4 Finding System CPU Utilization

        9.4.1 Checking Memory Management
        9.4.2 Checking I/O Management
        9.4.3 Checking Network Management
        9.4.4 Checking Process Management

10 Instance Tuning Using Performance Views

    10.1 Instance Tuning Steps

        10.1.1 Define the Problem
        10.1.2 Examine the Host System
        10.1.3 Examine the Oracle Statistics
        10.1.4 Implement and Measure Change

    10.2 Interpreting Oracle Statistics

        10.2.1 Examine Load
        10.2.2 Using Wait Event Statistics to Drill Down to Bottlenecks
        10.2.3 Table of Wait Events and Potential Causes
        10.2.4 Additional Statistics

    10.3 Wait Events Statistics

        10.3.1 SQL*Net Events
        10.3.2 buffer busy waits
        10.3.3 db file scattered read
        10.3.4 db file sequential read
        10.3.5 direct path read and direct path read temp
        10.3.6 direct path write and direct path write temp
        10.3.7 enqueue (enq:) waits
        10.3.8 events in wait class other
        10.3.9 free buffer waits
        10.3.10 latch events
        10.3.11 log file parallel write
        10.3.12 library cache pin
        10.3.13 library cache lock
        10.3.14 log buffer space
        10.3.15 log file switch
        10.3.16 log file sync
        10.3.17 rdbms ipc reply

    10.4 Idle Wait Events

Part IV Optimizing SQL Statements
11 SQL Tuning Overview

    11.1 Introduction to SQL Tuning
    11.2 Goals for Tuning

        11.2.1 Reduce the Workload
        11.2.2 Balance the Workload
        11.2.3 Parallelize the Workload

    11.3 Identifying High-Load SQL

        11.3.1 Identifying Resource-Intensive SQL
        11.3.2 Gathering Data on the SQL Identified

    11.4 Automatic SQL Tuning Features
    11.5 Developing Efficient SQL Statements

        11.5.1 Verifying Optimizer Statistics
        11.5.2 Reviewing the Execution Plan
        11.5.3 Restructuring the SQL Statements
        11.5.4 Controlling the Access Path and Join Order with Hints
        11.5.5 Restructuring the Indexes
        11.5.6 Modifying or Disabling Triggers and Constraints
        11.5.7 Restructuring the Data
        11.5.8 Maintaining Execution Plans Over Time
        11.5.9 Visiting Data as Few Times as Possible










12 Automatic SQL Tuning

    12.1 Automatic SQL Tuning Overview

        12.1.1 Query Optimizer Modes
        12.1.2 Types of Tuning Analysis

    12.2 SQL Tuning Advisor

        12.2.1 Input Sources
        12.2.2 Tuning Options
        12.2.3 Advisor Output
        12.2.4 Using SQL Tuning Advisor with Oracle Enterprise Manager
        12.2.5 Using SQL Tuning Advisor APIs

    12.3 SQL Tuning Sets

        12.3.1 Using SQL Tuning Sets with Oracle Enterprise Manager
        12.3.2 Using SQL Tuning Sets APIs

    12.4 SQL Profiles

        12.4.1 Accepting a SQL Profile
        12.4.2 Altering a SQL Profile
        12.4.3 Dropping a SQL Profile

    12.5 SQL Tuning Information Views

13 The Query Optimizer

    13.1 Optimizer Operations
    13.2 Choosing an Optimizer Goal

        13.2.1 OPTIMIZER_MODE Initialization Parameter
        13.2.2 Optimizer SQL Hints for Changing the Query Optimizer Goal
        13.2.3 Query Optimizer Statistics in the Data Dictionary

    13.3 Enabling and Controlling Query Optimizer Features

        13.3.1 Enabling Query Optimizer Features
        13.3.2 Controlling the Behavior of the Query Optimizer

    13.4 Understanding the Query Optimizer

        13.4.1 Components of the Query Optimizer
        13.4.2 Reading and Understanding Execution Plans

    13.5 Understanding Access Paths for the Query Optimizer

        13.5.1 Full Table Scans
        13.5.2 Rowid Scans
        13.5.3 Index Scans
        13.5.4 Cluster Access
        13.5.5 Hash Access
        13.5.6 Sample Table Scans
        13.5.7 How the Query Optimizer Chooses an Access Path

    13.6 Understanding Joins

        13.6.1 How the Query Optimizer Executes Join Statements
        13.6.2 How the Query Optimizer Chooses Execution Plans for Joins
        13.6.3 Nested Loop Joins
        13.6.4 Hash Joins
        13.6.5 Sort Merge Joins
        13.6.6 Cartesian Joins
        13.6.7 Outer Joins

14 Managing Optimizer Statistics

    14.1 Understanding Statistics
    14.2 Automatic Statistics Gathering

        14.2.1 GATHER_STATS_JOB
        14.2.2 Enabling Automatic Statistics Gathering
        14.2.3 Considerations When Gathering Statistics

    14.3 Manual Statistics Gathering

        14.3.1 Gathering Statistics with DBMS_STATS Procedures
        14.3.2 When to Gather Statistics

    14.4 System Statistics

        14.4.1 Workload Statistics
        14.4.2 Noworkload Statistics

    14.5 Managing Statistics

        14.5.1 Restoring Previous Versions of Statistics
        14.5.2 Exporting and Importing Statistics
        14.5.3 Restoring Statistics Versus Importing or Exporting Statistics
        14.5.4 Locking Statistics for a Table or Schema
        14.5.5 Setting Statistics
        14.5.6 Estimating Statistics with Dynamic Sampling
        14.5.7 Handling Missing Statistics

    14.6 Viewing Statistics

        14.6.1 Statistics on Tables, Indexes and Columns
        14.6.2 Viewing Histograms

15 Using Indexes and Clusters

    15.1 Understanding Index Performance

        15.1.1 Tuning the Logical Structure
        15.1.2 Index Tuning using the SQLAccess Advisor
        15.1.3 Choosing Columns and Expressions to Index
        15.1.4 Choosing Composite Indexes
        15.1.5 Writing Statements That Use Indexes
        15.1.6 Writing Statements That Avoid Using Indexes
        15.1.7 Re-creating Indexes
        15.1.8 Compacting Indexes
        15.1.9 Using Nonunique Indexes to Enforce Uniqueness
        15.1.10 Using Enabled Novalidated Constraints

    15.2 Using Function-based Indexes for Performance
    15.3 Using Partitioned Indexes for Performance
    15.4 Using Index-Organized Tables for Performance
    15.5 Using Bitmap Indexes for Performance
    15.6 Using Bitmap Join Indexes for Performance
    15.7 Using Domain Indexes for Performance
    15.8 Using Clusters for Performance
    15.9 Using Hash Clusters for Performance

16 Using Optimizer Hints

    16.1 Understanding Optimizer Hints

        16.1.1 Types of Hints
        16.1.2 Hints by Category

    16.2 Specifying Hints

        16.2.1 Specifying a Full Set of Hints
        16.2.2 Specifying a Query Block in a Hint
        16.2.3 Specifying Global Table Hints
        16.2.4 Specifying Complex Index Hints

    16.3 Using Hints with Views

        16.3.1 Hints and Complex Views
        16.3.2 Hints and Mergeable Views
        16.3.3 Hints and Nonmergeable Views

17 SQL Access Advisor

    17.1 Overview of the SQL Access Advisor in the DBMS_ADVISOR Package

        17.1.1 Overview of Using the SQL Access Advisor

    17.2 Using the SQL Access Advisor

        17.2.1 Steps for Using the SQL Access Advisor
        17.2.2 Privileges Needed to Use the SQL Access Advisor
        17.2.3 Setting Up Tasks and Templates
        17.2.4 Managing Workloads
        17.2.5 Working with Recommendations
        17.2.6 Performing a Quick Tune
        17.2.7 Managing Tasks
        17.2.8 Using SQL Access Advisor Constants
        17.2.9 Examples of Using the SQL Access Advisor

    17.3 Tuning Materialized Views for Fast Refresh and Query Rewrite

        17.3.1 DBMS_ADVISOR.TUNE_MVIEW Procedure

    17.4 Managing SQL Access Advisor Tasks Using Enterprise Manager

        17.4.1 Step 1: Select the Initial Options
        17.4.2 Step 2: Define the Workload Source
        17.4.3 Step 3: Choose the Types of Recommendations
        17.4.4 Step 4: Set a Schedule
        17.4.5 Step 5: Review and Submit Your Selections
        17.4.6 Step 6: Examine the Recommendations

18 Using Plan Stability

    18.1 Using Plan Stability to Preserve Execution Plans

        18.1.1 Using Hints with Plan Stability
        18.1.2 Storing Outlines
        18.1.3 Enabling Plan Stability
        18.1.4 Using Supplied Packages to Manage Stored Outlines
        18.1.5 Creating Outlines
        18.1.6 Using and Editing Stored Outlines
        18.1.7 Viewing Outline Data
        18.1.8 Moving Outline Tables

    18.2 Using Plan Stability with Query Optimizer Upgrades

        18.2.1 Moving from RBO to the Query Optimizer
        18.2.2 Moving to a New Oracle Release under the Query Optimizer

19 Using EXPLAIN PLAN

    19.1 Understanding EXPLAIN PLAN

        19.1.1 How Execution Plans Can Change
        19.1.2 Minimizing Throw-Away
        19.1.3 Looking Beyond Execution Plans
        19.1.4 EXPLAIN PLAN Restrictions

    19.2 The PLAN_TABLE Output Table
    19.3 Running EXPLAIN PLAN

        19.3.1 Identifying Statements for EXPLAIN PLAN
        19.3.2 Specifying Different Tables for EXPLAIN PLAN

    19.4 Displaying PLAN_TABLE Output

        19.4.1 Customizing PLAN_TABLE Output

    19.5 Reading EXPLAIN PLAN Output
    19.6 Viewing Parallel Execution with EXPLAIN PLAN

        19.6.1 Viewing Parallel Queries with EXPLAIN PLAN

    19.7 Viewing Bitmap Indexes with EXPLAIN PLAN
    19.8 Viewing Partitioned Objects with EXPLAIN PLAN

        19.8.1 Examples of Displaying Range and Hash Partitioning with EXPLAIN PLAN
        19.8.2 Examples of Pruning Information with Composite Partitioned Objects
        19.8.3 Examples of Partial Partition-wise Joins
        19.8.4 Examples of Full Partition-wise Joins
        19.8.5 Examples of INLIST ITERATOR and EXPLAIN PLAN
        19.8.6 Example of Domain Indexes and EXPLAIN PLAN

    19.9 PLAN_TABLE Columns

20 Using Application Tracing Tools

    20.1 End to End Application Tracing

        20.1.1 Accessing the End to End Tracing with Oracle Enterprise Manager
        20.1.2 Managing End to End Tracing with APIs and Views

    20.2 Using the trcsess Utility

        20.2.1 Syntax for trcsess
        20.2.2 Sample Output of trcsess

    20.3 Understanding SQL Trace and TKPROF

        20.3.1 Understanding the SQL Trace Facility
        20.3.2 Understanding TKPROF

    20.4 Using the SQL Trace Facility and TKPROF

        20.4.1 Step 1: Setting Initialization Parameters for Trace File Management
        20.4.2 Step 2: Enabling the SQL Trace Facility
        20.4.3 Step 3: Formatting Trace Files with TKPROF
        20.4.4 Step 4: Interpreting TKPROF Output
        20.4.5 Step 5: Storing SQL Trace Facility Statistics

    20.5 Avoiding Pitfalls in TKPROF Interpretation

        20.5.1 Avoiding the Argument Trap
        20.5.2 Avoiding the Read Consistency Trap
        20.5.3 Avoiding the Schema Trap
        20.5.4 Avoiding the Time Trap
        20.5.5 Avoiding the Trigger Trap

    20.6 Sample TKPROF Output

        20.6.1 Sample TKPROF Header
        20.6.2 Sample TKPROF Body
        20.6.3 Sample TKPROF Summary