|
Oracle Database 11g: Performance Tuning |
|
Oracle University
Oracle Database 11g: Performance Tuning
Duration: 5 Days
What you will learn
Students learn how to use Oracle Database 11g automatic tuning features such as SQL Tuning Advisor, SQL Access
Advisor, Automatic Workload Repository and Automatic Database Diagnostic Monitor, and practice these tuning
methods. The course focuses on the tuning tasks expected of a DBA: reactive tuning of SQL statements, maintaining
SQL statement performance, and tuning the Oracle Database Instance components. Throughout the course, students
practice the art of tuning an Oracle Instance through a series of workshops. The methodology is practiced in the
workshops rather than taught.
This course does not address partition tuning, materialized views, or RAC specific issues as they are covered in courses
specifically for these products. This course makes use of many features that require the Enterprise Edition and optional
Packs.
This course counts towards the Hands-on course requirement for the Oracle Database 11g Administrator Certification.
Only instructor-led inclass or instructor-led online formats of this course will meet the Certification Hands-on
Requirement. Self Study CD-Rom and Knowledge Center courses DO NOT meet the Hands-on Requirement.
Learn To:
Use the Oracle Database tuning methodology appropriate to the available tools
Utilize database advisors to proactively tune an Oracle Database Instance
Use the tools based on the Automatic Workload Repository to tune the database.
Diagnose and tune common SQL related performance problems
Diagnose and tune common Instance related performance problems
Use Enterprise Manager performance-related pages to monitor an Oracle Database
Audience
Database Administrators
Support Engineer
Technical Consultant
Prerequisites
Required Prerequisites
Oracle Database 11g: Administration Workshop II
Oracle Database 11g: Administration Workshop I
Course Objectives
Use Database Statistics and Metrics to identify a performance problem
Interpret Tuning diagnostics
Identify and eliminate performance issues
Set tuning priorities and strategies
Identify problem SQL statements
Copyright © 2009, Oracle. All rights reserved. Page 1
Influence the optimizer
Course Topics
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
Using 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
Influencing the Optimizer
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
Copyright © 2009, Oracle. All rights reserved. Page 2
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 (Using Services)
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
Architecture
Tuning Goals and Techniques
Symptoms
Solutions
Tuning 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
Copyright © 2009, Oracle. All rights reserved. Page 3
Anatomy of a Database Block
Block Space Management
Tuning I/O
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
Copyright © 2009, Oracle. All rights reserved. Page 4
|
