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

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

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

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.

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

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

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

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

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