10g Database Tuning

Tuning Overview
Tuning Questions/Goals/Steps

Alerts, Trace Files & Events

Diagnostic Information
The Alert Log File
User Trace Files
Statistics Event Views

Dynamic Performance Views

Dynamic Performance Views
Collecting System-Wide Statistics
Collecting Session Related Statistics
Statspack

Oracle Wait Events

I/O related events
Lock related events
Latency related events
Gathering wait event data

Automatic Workload Repository

Automated snapshot collection
Active Session History

Automatic Database Diagnostics

Enterprise Manager Advisors
ADDM reports
Time model statistics
Wait model statistics

Tuning the Shared Pool

The Shared Global Area
Tuning the Library Cache
The Data Dictionary Cache
User Global Area and Shared Server

Tuning the Buffer Cache

Evaluating the Cache Hit Ratio
Adding Buffers
Using Multiple Buffer Pools
Defining Multiple Buffer Pools
Caching Tables

Tuning The Redo Log Buffer

Sizing The Redo Log Buffer
Reducing Redo Operations

SQL Tuning

Optimizer Modes
Diagnostic Tools
V$SQL_PLAN
Monitoring table and index useage

DBMS_STATS

Gathering statistics
Changing the data dictionary statistics
Automating statistics collection

Optimiser Plan Stability

Creating stored outlines
Using stored outlines
Viewing the execution paths
The OUTLN user

Materialised Views

Creating
Registering existing summary tables
Refresh options

Database Config. & I/O Issues

Tablespace Usage
Partitioning Tables and Indexes
Distributing Files Across Devices
Tuning Checkpoints
Automatic Storage Management

Oracle Blocks Efficiency

Database Storage Hierarchy
Database Block Size
Multiple Block Sizes
PCTFREE and PCTUSED
Automaic Space Segment Management
Detecting Chaining
Monitoring and Rebuilding Indexes

Tuning Rollback Segments

Rollback Segment Usage
Tools for Tuning Rollback Segments
Using Less Rollback
Using Automatic Undo Management

Tuning the Operating System

Tuning Guidelines
Tuning Memory
Tuning I/O

Optimizing Sorts

Sort Area and Parameters
Tuning Sorts
Avoiding Sorts
Configuring Temporary Tablespaces
Database Default Temporary Tablespace
Automatic PGA Management

Tuning Different Applications

Comparing B-Tree and Bitmap Indexes
Creating Reverse Key Indexes
Index-Organized Tables
Clusters / Cluster Types
Histograms

Database Resource Manager

Creating Consumer groups and adding users
Allocating resources to groups
Activating resource plans

Lock Contention

Locking Mechanisms
Tools for Monitoring Locking Activity
Resolving Contention
Deadlocks

Contention Issues

Diagnosing Latch Problems
Resolving Latch contention
Diagnosing Free List Contention
Resolving Free List Contention