Oracle Database 11g Performance Tuining

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

Oracle Database 11g Performance Tuining