ORA10g203 - Oracle Database 10g: Performance Tuning

Course ORA10g203

Table of Contents

Introduction
Audience
At Course Completion
Prerequisites
Certified Professional Exams
Student Materials
Course Outline

Introduction

The purpose of this course is to equip database administrators to recognize, diagnose and remedy performance problems which might arise in the operation of the Oracle database server. Beyond reactive measures, proactive actions which can be taken to avoid performance issues before they arise or are recognized are also considered.

Return to Top

Audience

Target audience for this course is database administrators, data warehouse administrators and application developers who will be responsible for implementing and using data warehouse technology.

Return to Top

At Course Completion

The objective of this course is to consider present a comprehensive consideration of data warehouse features that exist within the Oracle database.

Major subject areas to be explored are:

• Utilization of advanced self-tuning and self-management capabilities, including the use of the database Advisors and other components of the Management Framework.
• Performance monitoring, problem troubleshooting, and resolution.
• Detailed scrutiny of database activity using statistics and wait events.
• Manual tuning using Statspack.
• Tuning of the shared pool, buffer cache and shared memory
• Tuning recovery structures such as checkpoints and redo data
• Tuning physical storage structures such as file I/O and database blocks
• Tuning specialty structures such as the PGA and temporary space

Return to Top

Prerequisites

Before attending this course, students must have:

• Completed ora10g008, Oracle Database 10g: Introduction to SQL I, or equivalent.
• Completed ora10g009, Oracle Database 10g: Introduction to SQL II, or equivalent.
• Completed ora10g101, Oracle Database 10g: Program With PL/SQL, or equivalent.

The following prerequisites are recommended:

• Completed ora10g201, Oracle Database 10g: Administration I, or equivalent.
• Completed ora10g003, Oracle Database 10g: SQL Tuning, or equivalent.
• Completed ora10g204, Oracle Database 10g: New Features for Administrators, or equivalent.

Suggested Next Course

• ora10g304, Oracle Application Server Discoverer 10g: Develop An EUL

Return to Top

Certified Professional Exams

No exam currently exists for this course.

Return to Top

Student Materials

The student kit includes a comprehensive workbook and other necessary materials for this class.

Return to Top

Course Outline

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

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

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

Return to Top