Performance Tuning Overview

Introduction to Performance Tuning

Performance Planning

Instance Tuning

Performance Principles.

Baselines.

The Symptoms and the Problems.

When to Tune .

Proactive Monitoring

Bottleneck Elimination

SQL Tuning

Query Optimizer and Execution Plans

Introduction to Performance Tuning Features and Tools

Automatic Performance Tuning Features.

Additional Oracle Database Tools

V$ Performance Views

2 Designing and Developing for Performance

Oracle Methodology

Understanding Investment Options

Understanding Scalability

What is Scalability?

System Scalability

Factors Preventing Scalability

System Architecture

Hardware and Software Components

Hardware Components.

CPU.

Memory

I/O Subsystem.

Network

Software Components.

Managing the User Interface

Implementing Business Logic

Managing User Requests and Resource Allocation

Managing Data and Transactions

Configuring the Right System Architecture for Your Requirements.

Application Design Principles

Simplicity In Application Design.

Data Modeling

Table and Index Design.

Appending Columns to an Index or Using Index-Organized Tab

Using a Different Index Type.

B-Tree Indexes .

Bitmap Indexes .

Function-based Indexes

Partitioned Indexes

Reverse Key Indexes.

Finding the Cost of an Index.

Serializing within Indexes

Ordering Columns in an Index

Using Views

SQL Execution Efficiency.

Implementing the Application

Trends in Application Development

Workload Testing, Modeling, and Implementation

Sizing Data.

Estimating Workloads.

Extrapolating From a Similar System

Benchmarking.

Application Modeling

Testing, Debugging, and Validating a Design

Deploying New Applications

Rollout Strategies.

Performance Checklist.

3 Performance Improvement Methods

The Oracle Performance Improvement Method

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.

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 Permanent Tablespaces – Automatic Segment-Space

Creating Temporary Tablespaces.

Creating and Maintaining Tables for Optimal Performance

Table Compression

Estimating the Compression Factor

Tuning to Achieve a Better Compression Ratio

Using Attribute-Clustered Tables

Reclaiming Unused Space.

Indexing Data.

Specifying Memory for Sorting Data.

Performance Considerations for Shared Servers

Identifying Contention Using the Dispatcher-Specific Views

Reducing Contention for Dispatcher Processes.

Identifying Contention for Shared Servers.

5 Measuring Database Performance

About Database Statistics

Time Model Statistics.

Active Session History Statistics

Wait Events Statistics.

Session and System Statistics.

Interpreting Database Statistics

Using Hit Ratios

Using Wait Events with Timed Statistics

Using Wait Events without Timed Statistics.

Using Idle Wait Events.

Comparing Database Statistics with Other Factors

Using Computed Statistics.

6 Gathering Database Statistics

About Gathering Database Statistics

Automatic Workload Repository.

Snapshots

Baselines

Fixed Baselines.

Moving Window Baselines

Baseline Templates.

Single Baseline Templates

Repeating Baseline Templates

Space Consumption.

Adaptive Thresholds.

Percentage of Maximum Thresholds

Significance Level Thresholds

Managing the Automatic Workload Repository

Enabling the Automatic Workload Repository

Managing Snapshots

User Interfaces for Managing Snapshots

Creating Snapshots.

Creating Snapshots Using the Command-Line Interface.

Dropping Snapshots

Dropping Snapshots Using the Command-Line Interface

Modifying Snapshot Settings.

Modifying Snapshot Settings Using the Command-Line Int

Managing Baselines.

User Interface for Managing Baselines.

Creating a Baseline.

Creating a Baseline Using the Command-Line Interface.

Dropping a Baseline

Dropping a Baseline Using the Command-Line Interface

Renaming a Baseline

Renaming a Baseline Using the Command-Line Interface

Displaying Baseline Metrics

Displaying Baseline Metrics Using the Command-Line Inte

Resizing the Default Moving Window Baseline.

Resizing the Default Moving Window Using the Command

Managing Baseline Templates.

User Interfaces for Managing Baseline Templates.

Creating a Single Baseline Template

Creating a Single Baseline Template Using the Command

Creating a Repeating Baseline Template.

Creating a Repeating Baseline Template Using the Comm

Dropping a Baseline Template

Dropping a Baseline Template Using the Command-Line I

Transporting Automatic Workload Repository Data.

Extracting AWR Data

Loading AWR Data.

Using Automatic Workload Repository Views

Generating Automatic Workload Repository Reports

User Interface for Generating an AWR Report.

Generating an AWR Report Using the Command-Line Interface.

Generating an AWR Report for the Local Database.

Generating an AWR Report for a Specific Database

Generating an Oracle RAC AWR Report for the Local Databas

Generating an Oracle RAC AWR Report for a Specific Databas

Generating an AWR Report for a SQL Statement on the Local

Generating an AWR Report for a SQL Statement on a Specific

Generating Performance Hub Active Report

Overview of Performance Hub Active Report.

About Performance Hub Active Report Tabs

About Performance Hub Active Report Types

Command-Line User Interface for Generating a Performance Hub A

Generating a Performance Hub Active Report Using a SQL Script

7 Automatic Performance Diagnostics

Overview of the Automatic Database Diagnostic Monitor

ADDM Analysis

Using ADDM with Oracle Real Application Clusters

Real-Time ADDM Analysis.

Real-Time ADDM Connection Modes.

Real-Time ADDM Triggers

Real-Time ADDM Trigger Controls.

ADDM Analysis Results.

Reviewing ADDM Analysis Results: Example

Setting Up ADDM.

Diagnosing Database Performance Problems with ADDM

Running ADDM in Database Mode

Running ADDM in Instance Mode

Running ADDM in Partial Mode.

Displaying an ADDM Report

Views with ADDM Information

8 Comparing Database Performance Over Time

About Automatic Workload Repository Compare Periods Reports

Generating Automatic Workload Repository Compare Periods Reports

User Interfaces for Generating AWR Compare Periods Reports

Generating an AWR Compare Periods Report Using the Command

Generating an AWR Compare Periods Report for the Local Database

Generating an AWR Compare Periods Report for a Specific Database

Generating an Oracle RAC AWR Compare Periods Report

Generating an Oracle RAC AWR Compare Periods Report for a

Interpreting Automatic Workload Repository Compare Periods Reports

Summary of the AWR Compare Periods Report

Snapshot Sets.

Host Configuration Comparison

System Configuration Comparison.

Load Profile

Top 5 Timed Events

Details of the AWR Compare Periods Report

Time Model Statistics

Operating System Statistics

Wait Events.

Service Statistics.

SQL Statistics

Top 10 SQL Comparison by Execution Time.

Top 10 SQL Comparison by CPU Time.

Top 10 SQL Comparison by Buffer Gets.

Top 10 SQL Comparison by Physical Reads.

Top 10 SQL Comparison by Executions

Top 10 SQL Comparison by Parse Calls.

Complete List of SQL Text.

Instance Activity Statistics.

Key Instance Activity Statistics

Other Instance Activity Statistics

I/O Statistics

Tablespace I/O Statistics.

Top 10 File Comparison by I/O

Top 10 File Comparison by Read Time.

Top 10 File Comparison by Buffer Waits.

Advisory Statistics.

PGA Aggregate Summary.

PGA Aggregate Target Statistics.

Wait Statistics

Buffer Wait Statistics

Enqueue Activity

Undo Segment Summary

Latch Statistics

Segment Statistics

Top 5 Segments Comparison by Logical Reads

Top 5 Segments Comparison by Physical Reads

Top 5 Segments Comparison by Row Lock Waits.

Top 5 Segments Comparison by ITL Waits.

Top 5 Segments Comparison by Buffer Busy Waits

In-Memory Segment Statistics

Dictionary Cache Statistics.

Library Cache Statistics

Memory Statistics

Process Memory Summary

SGA Memory Summary.

SGA Breakdown Difference.

Streams Statistics.

Supplemental Information in the AWR Compare Periods Report.

init.ora Parameters

Complete List of SQL Text

9 Analyzing Sampled Data

About Active Session History9-1.

Generating Active Session History Reports

User Interfaces for Generating ASH Reports.

Generating an ASH Report Using the Command-Line Interface

Generating an ASH Report on the Local Database Instance

Generating an ASH Report on a Specific Database Instance.

Generating an ASH Report for Oracle RAC

Interpreting Results from Active Session History Reports

Top Events.

Top User Events.

Top Background Events

Top Event P1/P2/P3

Load Profile

Top Service/Module

Top Client IDs.

Top SQL Command Types.

Top Phases of Execution

Top SQL.

Top SQL with Top Events.

Top SQL with Top Row Sources.

Top SQL Using Literals.

Top Parsing Module/Action.

Complete List of SQL Text

Top PL/SQL

Top Java.

Top Sessions

Top Sessions

Top Blocking Sessions

Top Sessions Running PQs

Top Objects/Files/Latches

Top DB Objects.

Top DB Files.

Top Latches

Activity Over Time

10 Instance Tuning Using Performance Views

Instance Tuning Steps

Define the Problem.

Examine the Host System

CPU Usage.

Non-Oracle Processes.

Oracle Processes.

Oracle Database CPU Statistics

Interpreting CPU Statistics.

Identifying I/O Problems

Identifying I/O Problems Using V$ Views

Identifying I/O Problems Using Operating System Monitori

Identifying Network Issues

Examine the Oracle Database Statistics

Setting the Level of Statistics Collection.

V$STATISTICS_LEVEL.

Wait Events.

Dynamic Performance Views Containing Wait Event Statistics.

System Statistics.

V$ACTIVE_SESSION_HISTORY

V$SYSSTAT

V$FILESTAT

V$ROLLSTAT

V$ENQUEUE_STAT

V$LATCH.

Segment-Level Statistics.

Implement and Measure Change.

Interpreting Oracle Database Statistics

Examine Load

Changing Load

High Rates of Activity

Using Wait Event Statistics to Drill Down to Bottlenecks.

Table of Wait Events and Potential Causes.

Additional Statistics

Redo Log Space Requests Statistic

Read Consistency

Table Fetch by Continued Row.

Parse-Related Statistics

Wait Events Statistics

Changes to Wait Event Statistics from Past Releases.

buffer busy waits

Causes

Actions

segment header.

data block.

undo header.

undo block

db file scattered read

Actions

Managing Excessive I/O.

Inadequate I/O Distribution.

Finding the SQL Statement executed by Sessions Waiting for I/O

Finding the Object Requiring I/O.

db file sequential read

Actions

direct path read and direct path read temp

Causes

Actions

Sorts to Disk

Full Table Scans

Hash Area Size

direct path write and direct path write temp

Causes

Actions

enqueue (enq:) waits

Finding Locks and Lock Holders.

Actions

ST enqueue.

HW enqueue

TM enqueue.

TX enqueue.

events in wait class other.

free buffer waits

Causes

Actions

Writes.

Cache is Too Small

Cache Is Too Big for One DBWR

Consider Multiple Database Writer (DBWR) Processes or I/O S

DB_WRITER_PROCESSES.

DBWR_IO_SLAVES

Choosing Between Multiple DBWR Processes and I/O Slav

Idle Wait Events.

latch events

Actions

Example: Find Latches Currently Waited For.

Shared Pool and Library Cache Latch Contention

Unshared SQL.

Reparsed Sharable SQL

By Session.

cache buffers lru chain.

cache buffers chains

row cache objects

log file parallel write

library cache pin.

library cache lock

log buffer space

log file switch

Actions

log file sync.

rdbms ipc reply.

SQL*Net Events.

SQL*Net message from client.

Network Bottleneck

Resource Bottleneck on the Client Process.

SQL*Net message from dblink

SQL*Net more data to client.

Tuning Instance Recovery Performance: Fast-Start Fault Recovery

About Instance Recovery.

Cache Recovery (Rolling Forward).

Transaction Recovery (Rolling Back)

Checkpoints and Cache Recovery

How Checkpoints Affect Performance.

Fast Cache Recovery Tradeoffs

Configuring the Duration of Cache Recovery: FAST_START_MTTR_TARGET

Practical Values for FAST_START_MTTR_TARGET.

Reducing Checkpoint Frequency to Optimize Run-Time Performance

Monitoring Cache Recovery with V$INSTANCE_RECOVERY

Tuning FAST_START_MTTR_TARGET and Using MTTR Advisor.

Calibrate the FAST_START_MTTR_TARGET.

Determine the Practical Range for FAST_START_MTTR_TARGET

Determining Lower Bound for FAST_START_MTTR_TARGET

Determining Upper Bound for FAST_START_MTTR_TARGET

Selecting Preliminary Value for FAST_START_MTTR_TARGET

Evaluate Different Target Values with MTTR Advisor

Enabling MTTR Advisor.

Using MTTR Advisor

Viewing MTTR Advisor Results: V$MTTR_TARGET_ADVISORY

Determine the Optimal Size for Redo Logs

11 Database Memory Allocation

About Database Memory Caches and Other Memory Structures

Database Memory Management Methods

Automatic Memory Management.

Automatic Shared Memory Management.

Manual Shared Memory Management.

Automatic PGA Memory Management.

Manual PGA Memory Management.

Using Automatic Memory Management

Monitoring Memory Management

12 Tuning the System Global Area .

Using Automatic Shared Memory Management

User Interfaces for Setting the SGA_TARGET Parameter

Setting the SGA_TARGET Parameter in Oracle Enterprise Manager

Setting the SGA_TARGET Parameter in the Command-Line Interface

Setting the SGA_TARGET Parameter.

Enabling Automatic Shared Memory Management.

Disabling Automatic Shared Memory Management

Sizing the SGA Components Manually

SGA Sizing Unit

Maximum Size of the SGA.

Application Considerations.

Operating System Memory Use.

Reduce Paging

Fit the SGA into Main Memory

Viewing SGA Memory Allocation.

Locking the SGA into Physical Memory.

Allow Adequate Memory to Individual Users.

Iteration During Configuration

Monitoring Shared Memory Management

Configuring the In-Memory Column Store

About the In-Memory Column Store.

Performance Benefits of Using the In-Memory Column Store.

Estimating the Required Size of the In-Memory Column Store.

Sizing the In-Memory Column Store

13 Tuning the Database Buffer Cache

About the Database Buffer Cache

Configuring the Database Buffer Cache

Using the V$DB_CACHE_ADVICE View.

Calculating the Buffer Cache Hit Ratio.

Interpreting the Buffer Cache Hit Ratio

Increasing Memory Allocated to the Database Buffer Cache

Reducing Memory Allocated to the Database Buffer Cache.

Configuring Multiple Buffer Pools

Considerations for Using Multiple Buffer Pools

Random Access to Large Segments.

Oracle Real Application Cluster Instances

Using Multiple Buffer Pools

Using the V$DB_CACHE_ADVICE View for Individual Buffer Pools.

Calculating the Buffer Pool Hit Ratio for Individual Buffer Pools.

Examining the Buffer Cache Usage Pattern.

Examining the Buffer Cache Usage Pattern for All Segments

Examining the Buffer Cache Usage Pattern for a Specific Segments

Configuring the KEEP Pool

Configuring the RECYCLE Pool.

Configuring the Redo Log Buffer

Sizing the Redo Log Buffer

Using Redo Log Buffer Statistics

Configuring the Database Caching Mode

Default Database Caching Mode.

Force Full Database Caching Mode.

Determining When to Use Force Full Database Caching Mode

Verifying the Database Caching Mode.

14 Tuning the Shared Pool and the Large Pool

About the Shared Pool

Benefits of Using the Shared Pool.

Shared Pool Concepts.

Library Cache Concepts

Data Dictionary Cache Concepts

SQL Sharing Criteria.

Using the Shared Pool

Use Shared Cursors.

Use Single-User Logon and Qualified Table Reference

Use PL/SQL.

Avoid Performing DDL Operations.

Cache Sequence Numbers

Control Cursor Access.

Controlling Cursor Access Using OCI.

Controlling Cursor Access Using Oracle Precompilers

Controlling Cursor Access Using SQLJ

Controlling Cursor Access Using JDBC

Controlling Cursor Access Using Oracle Forms

Maintain Persistent Connections

Configuring the Shared Pool

Sizing the Shared Pool.

Using Library Cache Statistics

Using the V$LIBRARYCACHE View

Calculating the Library Cache Hit Ratio.

Viewing the Amount of Free Memory in the Shared Pool.

Using Shared Pool Advisory Statistics

About the V$SHARED_POOL_ADVICE View.

About the V$LIBRARY_CACHE_MEMORY View.

About V$JAVA_POOL_ADVICE and V$JAVA_LIBRARY_C

Using Dictionary Cache Statistics.

Increasing Memory Allocated to the Shared Pool.

Reducing Memory Allocated to the Shared Pool

Deallocating Cursors

Caching Session Cursors.

About the Session Cursor Cache

Enabling the Session Cursor Cache.

Sizing the Session Cursor Cache.

Sharing Cursors.

About Cursor Sharing

Forcing Cursor Sharing.

Keeping Large Objects to Prevent Aging.

Configuring the Reserved Pool

Sizing the Reserved Pool

Increasing Memory Allocated to the Reserved Pool.

Reducing Memory Allocated to the Reserved Pool

Configuring the Large Pool

Configuring the Large Pool for Shared Server Architecture.

Configuring the Large Pool for Parallel Query

Sizing the Large Pool.

Limiting Memory Use for User Sessions

Reducing Memory Use Using Three-Tier Connections

15 Tuning the Result Cache

About the Result Cache

Server Result Cache Concepts

Benefits of Using the Server Result Cache.

Understanding How the Server Result Cache Works.

How Results are Retrieved in a Query.

How Results are Retrieved in a View.

Client Result Cache Concepts.

Benefits of Using the Client Result Cache

Understanding How the Client Result Cache Works.

Configuring the Result Cache

Configuring the Server Result Cache.

Sizing the Server Result Cache Using Initialization Parameters

Managing the Server Result Cache Using DBMS_RESULT_CA

Viewing Memory Usage Statistics for the Server Result Ca

Flushing the Server Result Cache.

Configuring the Client Result Cache

Setting the Result Cache Mode

Requirements for the Result Cache.

Read Consistency Requirements

Query Parameter Requirements.

Restrictions for the Result Cache

Specifying Queries for Result Caching

Using SQL Result Cache Hints

Using the RESULT_CACHE Hint

Using the NO_RESULT_CACHE Hint

Using the RESULT_CACHE Hint in Views.

Using Result Cache Table Annotations

Using the DEFAULT Table Annotation.

Using the FORCE Table Annotation.

Monitoring the Result Cache

16 Tuning the Program Global Area .

About the Program Global Area

Work Area Sizes.

Sizing the Program Global Area

Configuring Automatic PGA Memory Management.

Setting the Initial Value for PGA_AGGREGATE_TARGET.

Monitoring Automatic PGA Memory Management.

Using the V$PGASTAT View

Using the V$PROCESS View.

Using the V$PROCESS_MEMORY View.

Using the V$SQL_WORKAREA_HISTOGRAM View.

Using the V$WORKAREA_ACTIVE View.

Using the V$SQL_WORKAREA View.

Tuning PGA_AGGREGATE_TARGET

Enabling Automatic Generation of PGA Performance Advisory

Using the V$PGA_TARGET_ADVICE View.

Using the V$PGA_TARGET_ADVICE_HISTOGRAM View

Using the V$SYSSTAT and V$SESSTAT Views

Tutorial: How to Tune PGA_AGGREGATE_TARGET

Limiting the Size of the Program Global Area

About PGA_AGGREGATE_LIMIT.

Setting PGA_AGGREGATE_LIMIT

17 I/O Configuration and Design

About I/O

I/O Configuration

Lay Out the Files Using Operating System or Hardware Striping.

Requested I/O Size.

Concurrency of I/O Requests

Alignment of Physical Stripe Boundaries with Block Size Bound

Manageability of the Proposed System.

Manually Distributing I/O

When to Separate Files

Tables, Indexes, and TEMP Tablespaces

Redo Log Files

Archived Redo Logs

Three Sample Configurations.

Stripe Everything Across Every Disk.

Move Archive Logs to Different Disks.

Move Redo Logs to Separate Disks

Oracle Managed Files

Choosing Data Block Size

Reads

Writes

Block Size Advantages and Disadvantages

I/O Calibration Inside the Database

Prerequisites for I/O Calibration.

Running I/O Calibration

I/O Calibration with the Oracle Orion Calibration Tool

Introduction to the Oracle Orion Calibration Tool.

Orion Test Targets

Orion for Oracle Administrators.

Getting Started with Orion

Orion Input Files.

Orion Parameters.

Orion Required Parameter

Orion Optional Parameters.

Orion Command Line Samples.

Orion Output Files

Orion Sample Output Files

Orion Troubleshooting

18 Managing Operating System Resources

Understanding Operating System Performance Issues

Using Operating System Caches.

Asynchronous I/O

FILESYSTEMIO_OPTIONS Initialization Parameter

Limiting Asynchronous I/O in NFS Server Environments.

Memory Usage.

Buffer Cache Limits.

Parameters Affecting Memory Usage.

Using Operating System Resource Managers.

Resolving Operating System Issues

Performance Hints on UNIX-Based Systems.

Performance Hints on Windows Systems

Performance Hints on HP OpenVMS Systems

Understanding CPU

Resolving CPU Issues

Finding and Tuning CPU Utilization.

Checking Memory Management.

Paging and Swapping .

Oversize Page Tables .

Checking I/O Management.

Checking Network Management.

Checking Process Management.

Scheduling and Switching .

Context Switching .

Starting New Operating System Processes.

Managing CPU Resources Using Oracle Database Resource Manager

Managing CPU Resources Using Instance Caging