5.Performance Tuning

 

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

 

Managing Database Performance

• Tuning Activities
• Using Statistic Preferences
• Optimizer Statistics Collection
• Monitor the Performance of Sessions and Services
• Automatic Workload Repository (AWR)
• Describing the Benefits of Database Replay

Managing Performance by SQL Tuning

• SQL Tuning and SQL Advisors
• Using SQL Tuning Advisor
• SQL Access Advisor
• SQL Performance Analyzer Overview