Oracle 10g Database Performance Tuning

Audience:

• Database Administrators
• Support Engineer
• Technical Consultant

Required Prerequisites:

• Knowledge of Database Administration
• Oracle Database 10g: Administration Workshop I Release 2
• Oracle Database 10g: Administration Workshop II Release 2
• Oracle Database 10g: New Features for Administrators Release 2

Course Objectives:

• 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

Course Topics:

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

Statistics and Wait Events

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

Metrics, Alerts, and Baselines

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

Using Statspack

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

Using AWR

• Create and manage AWR snapshots
• Generate AWR reports
• Create snapshot sets and compare periods
• Generate ADDM reports
• Generate ASH reports

Reactive Tuning

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

Tuning the Shared Pool

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

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

Automatic Shared Memory

• Enable Automatic Shared Memory
• Set auto-tuned memory parameters
• Set the manually tuned SGA parameters
• Use the SGA advisor to set SGA target

Checkpoint and Redo Tuning

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

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

Tuning PGA and Temporary Space

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

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 chaining
• Diagnose table fragmentation
• Compare characteristics of bigfile and smallfile tablespaces

Initial Configuration

• List best practices identified throughout the course

<<Back