Oracle Database 10g: Performance Tuning

 Click here

Course Overview

This course is designed to introduce students to tuning techniques that can be used to achieve acceptable performance from an Oracle database. Students learn how to maximize the use of the Oracle database by using various automatic and manual tuning features.

This course does not address partition tuning, materialized views, or RAC specific issues as they are covered in courses specifically for these features. This course makes use of many features that require the Enterprise Edition and optional Packs.

Throughout the course, discussions focus on addressing "real-world" scenarios and how to recognize, troubleshoot and resolve performance-related issues. These topics are reinforced through the student's participation in structured hands-on lab exercises.

Course Goals

• Use the Oracle Database tuning methodology appropriate to the available tool
• Utilize database advisors to proactively tune an Oracle database
• Use the tools based on the Automatic Workload Repository to tune the database
• Use Statspack reports to tune the database
• Diagnose and tune common database performance problems
• Use Enterprise Manager performance-related pages to monitor an Oracle database

Prerequisites

• Knowledge of Oracle database administration

Who Should Attend?

• Database Administrators
• Support Engineers
• Technical Consultants

Course Materials

• Student Handbook
• Performance Tuning Reference Book

Duration

• Three (3) days

Course Outline

• Introduction
  • Tuning Questions
  • Who tunes
  • What to tune
  • How to tune
• Monitoring with Basic Tools
  • Monitoring tools overview
  • Enterprise Manager
  • V$ views, Statistics and Metrics
  • Wait Events
  • Time Model: Overview
• Automatic Workload Repository
  • Automatic Workload Repository: Overview
  • Automatic Workload Repository Data
  • Database Control and AWR
  • Generating AWR Reports in SQL*Plus
• Identifying the Problem
  • Tuning Life Cycle Phases
  • Identify a Tuning Issue
  • Remedy one problem
• Identifying Problem SQL Statements
  • Characteristics of a bad SQL statement
  • Role of the Optimizer
  • Generate explain plan
  • Access Paths Choices
  • Trace the execution
• Influeing the Optimizer
  • Performance Management Approach
  • Manage Optimizer Statistics
  • Calibrate I/O
  • Optimizer Cost
  • Changing Optimizer Behavior
• SQL Plan Management
  • Automatic Maintenance Tasks
  • SQL Profiles
  • SQL Access Advisor
  • SQL Outlines
  • SQL Plan Baselines
• Change Management
  • Types of changes
  • SQL Performance Analyzer
  • DB Replay
  • Server-Generated Alerts
• Using Metrics and Alerts
  • Benefits of Metrics
  • Database Control Usage Model
  • User-Defined SQL Metrics
• Using AWR Based Tools
  • Automatic Maintenance Tasks
  • Using ADDM
  • Using Active Session History
  • Historical Data View
• Monitoring an Application
  • Automatic Storage Management
  • Service Overview
  • Managing Service
  • Service Aggregation and Tracing
  • Tracing Your Session
• Baselines
  • Working with Metric Baselines
  • Setting Adaptive Alert Thresholds
  • Configuring Normalization Metrics
• Tuning the Shared Pool
  • Shared Pool Operation
  • Mutex
  • Statspack/AWR Indicators
  • Library Cache Activity
  • Diagnostic Tools
  • UGA and Oracle Shared Server
  • Large Pool
• Tuning the Buffer Cache
  • ARCHIVELOG Mode
  • Architecture
  • Tuning Goals and Techniques
  • Symptoms
  • Solutions
• Tuning the PGA and Temporary Space
  • Monitoring SQL Memory Usage
  • Temporary Tablespace Management
• Automatic Memory Management
  • Automatic Memory Management Architecture
  • Dynamic SGA Feature
  • Managing Automatic Memory Management
• Tuning Block Space Usage
  • Space Management
  • Extent Management
  • Anatomy of a Database Block
  • Block Space Management
• Tuning I/O
  • Block Space Management
  • I/O Architecture
  • Striping and Mirroring
  • Using RAID
  • I/O Diagnostics
  • Using Automatic Storage Management
• Performance Tuning Summary
  • Important Initialization Parameters with Performance Impact
  • Database High Availability: Best Practices
  • Tablespace: Best Practices
  • Statistics Gathering
• Using Statspack
  • Introduction to Statspack
  • Capturing Statspack Snapshots
  • Reporting with Statspack
  • Statspack considerations
  • Statspack and AWR