Course ORC033: Four days; Instructor-led

Introduction

Get the most out of your 10g Database by learning performance tuning methods that maximize the utility of the database. Students learn how to benefit from Oracle Database 10g automatic tuning features, as well as practicing the manual tuning methods using the statspack tool. After clearly defining the various methodologies one can use to tune an Oracle Database, the course covers the various tunable components of an Oracle Database. Throughout the course, students experience hands-on the tuning of an Oracle Database by praciticing through a series of workshops.

This course counts towards the Hands-on course requirement for the Oracle Database 10g Administrator Certification. Only instructor-led inclass or instructor-led online formats of this course will mee the Certification Hands-on requirement. Self Study CD-ROM and Knowledge Center courses are excellent study and reference tools but DO NOT meet the Hands-on requirement for certification.

Audience

Data Administrators
Support Engineer
Technical Consultant



Prerequisites

Required Prerequisites
Knowledge of Database Administration

Suggested Prerequisites
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 Outline

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