Oracle Database 10g Performance Tuning

Day 1

• Lesson 1 : Introduction
• Lesson 2 : Performance Tuning: Overview
• Lesson 3 : Statistics and Wait Events
• Lesson 4 : Metrics, Alerts, and Baselines
• Lesson 5 : Using Statspack

Day 2

• Lesson 6 : Using Automatic Workload Repository
• Lesson 7 : Reactive Tuning
• Lesson 8 : Tuning the Shared Pool

Day 3

• Lesson 9 : Tuning the Buffer Cache
• Lesson 10 : Automatic Shared Memory Management
• Lesson 11 : Checkpoint and Redo Tuning

Day 4

• Lesson 12 : Tuning I/O
• Lesson 13 : Tuning PGA and Temporary Space
• Lesson 14 : Tuning Block Space Usage
• Lesson 15 : Performance Tuning: Summary

Lesson 1 : Introduction

• 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 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

Lesson 2 : Performance Tuning: Overview

• Write appropriate tuning goals
• Apply the tuning methodology
• Balance performance and safety trade-offs
• Identify common tuning problems
• Log a performance
• Service Request with Oracle Support

Lesson 3 : Statistics and Wait Events

• Identify dynamic performance views useful in tuning
• Identify the key tuning components of the alert log file
• Identify the key tuning components of user trace files
• Use dynamic performance views to view statistics and wait events

Lesson 4 : Metrics, Alerts, and Baselines

• View metrics by using the metrics history views
• Create metric thresholds
• View alerts
• Create metric baselines Enable adaptive thresholds

Lesson 5 : Using Statspack

• Install Statspack
• Create Statspack snapshots
• Generate Statspack reports I
• dentify the major sections of the Statspack report

Lesson 6 : Using Automatic Workload Repository

• Create and manage Automatic Workload Repository (AWR) snapshots
• Generate AWR reports
• Create snapshot sets and compare periods
• Generate ADDM reports
• Generate ASH reports

Lesson 7 : Reactive Tuning

• Use Enterprise Manager pages to identify performance issues
• Eliminate operating system issues

Lesson 8 : Tuning the Shared Pool

• Diagnose shared pool problems
• Size the shared pool
• Size the reserved area
• Keep objects in the shared pool

Lesson 9 : Tuning the Buffer Cache

• Describe the buffer cache architecture
• Size the buffer cache
• Resolve common performance issues related to the buffer cache
• Use common diagnostic indicators to suggest a possible solution

Lesson 10 : Automatic Shared Memory Management

• Enable Enterprise Manager (EM) memory parameters
• Set auto-tuned memory parameters
• Override minimum size with the manually tuned SGA parameters
• Use the SGA advisor to set SGA_TARGET

Lesson 11 : Checkpoint and Redo Tuning

• Diagnose checkpoint and redo issues
• Implement Fast Start MTTR Target
• Monitor the performance impact of Fast Start MTTR Target
• Tune the redo chain
• Size the redo log file
• Size the redo log buffer

Lesson 12 : Tuning I/O

• Diagnose database I/O issues
• Describe the Stripe And Mirror Everything (SAME) concept
• Explain the benefits of asynchronous I/O
• Choose appropriate I/O solutions
• Tune I/O using Automatic Storage Management (ASM)

Lesson 13 : Tuning PGA and Temporary Space

• Diagnose PGA memory issues
• Size the PGA memory
• Diagnose temporary space issues
• Specify temporary tablespace parameters for efficient operation

Lesson 14 : Tuning Block Space Usage

• Tune segment space management
• Convert from dictionary-managed tablespaces
• Convert to Automatic Segment Space Management
• Tune block space management
• Diagnose and correct row migration
• Diagnose table fragmentation
• Compare characteristics of bigfile and smallfile tablespaces

Lesson 15 : Performance Tuning: Summary

• List best practices identified throughout the course
• Summarize the performance tuning methodology