Oracle 9i Database Tuning

Course code: O930

3 days

Everyone involved with the Oracle system; system architects, designers, developers and database administrators (DBAs), should consider performance tuning while carrying out their work.

Course content >

Who Should Attend?




Course Contents

Everyone involved with the Oracle system; system architects, designers, developers and database administrators (DBAs), should consider performance tuning while carrying out their work.

This course is aimed at the DBAs who often have to make the first attempt at solving performance problems prior to, and as they develop. We cover all aspects of internal database tuning on an Oracle 9i platform in this three day course.

The enhancements and changes that have been introduced in 9i will be discussed during this course for the benefit of DBA's responsible for earlier RDBMS versions for the purposes of planning and upgrade tuning.

Training Course Contents

Tuning Overview

  • Tuning Questions/Goals/Steps

Alert, Trace Files & Events

  • Diagnostic Information
  • The Alert Log File
  • User Trace Files
  • Oracle Wait Events
  • Statistics Event Views

Performance Views & Utilities

  • Dynamic Performance Views
  • Collecting System-Wide Statistics
  • Waiting Events Statistics
  • Collecting Session Related Statistics
  • Statspack

Tuning the Shared Pool

  • The Shared Global Area
  • Tuning the Library Cache
  • The Data Dictionary Cache
  • User Global Area and Multithreaded (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
  • Redo Log Buffer Latches

SQL Tuning

  • Optimizer Modes
  • Diagnostic Tools


  • 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
  • Using the Partitioning Option
  • Distributing Files Across Devices
  • Tuning Checkpoints

Enterprise Manager Tuning Pack

  • Oracle Expert
  • SQL Analyse

Using Blocks Efficiently

  • Database Storage Hierarchy
  • Database Block Size
  • Multiple Block Sizes
  • 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

  • CPU Tuning Guidelines
  • Tuning Memory
  • Tuning I/O

Optimising 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

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

Diagnostics Pack

  • Oracle Diagnostics Pack
  • Tools Overview
  • Performance Manager
  • Top Sessions
  • Trace Manager
  • Analysing Historical Data
  • Performing Trend Analysis

Event Management

  • Event Management
  • Event Creation/Progress
  • Event Notification/History

Database Resource Manager

  • Creating Consumer groups and adding users
  • Assigning privileges to groups
  • Controlling CPU and parallel operation
  • 9i Resource Manager
Contact us for more information