Oracle 11g Release 2 DBA Performance Tuning

Follow-on Courses

• Oracle Database 11g Release 2 DBA Backup & Recovery with RMAN
• Oracle Database 11g Release 2 Data Guard

Introduction to Oracle Performance Tuning

• Overview of Oracle Database Tuning
• Application Developer Tuning Responsibilities
• Oracle DBA Tuning Responsibilities
• How to Tune an Oracle Database
• The Oracle Tuning Methodology

Basic Tuning Diagnostics

• Performance Tuning Diagnostics, Features, and Tools
• DB Time
• CPU and Wait Time Tuning Dimensions
• Time Model
• Dynamic Performance Views
• Statistics
• Wait Events
• Log Files and Trace Files

Identifying Problem SQL Statements

• The Processing Phases of a SQL Statement
• Monitoring SQL Statements
• Generating SQL Execution Plans
• Using the DBMS_XPLAN Package
• The EXPLAIN PLAN and Autotrace Commands
• Reading an Execution Plan
• The SQL Trace and tkprof Programs
• Generate and Interpret an Optimizer Trace

How to Influence the Optimizer

• Overview of the SQL Query Optimizer
• Manage the Optimizer Statistics
• Use Parameters to Influence the Behaviour of the Optimizer
• Enable Query Optimizer Features
• Use Hints to Influence the Optimizer
• Understand Access Paths
• Understand Join Operations
• Sort Operations

Reduce the Cost of SQL Operations

• Maintain Indexes
• The SQL Access Advisor
• Maintain Tables
• Reorganize Tables
• Manage Extents
• Compress Tables
• Row Chaining and Row Migration
• Segment Shrink

The SQL Performance Analyser

• An Overview of the SQL Performance Analyzer
• Usage of the SQL Performance Analyzer
• Capture a SQL Workload
• Create a SQL Performance Analyzer Task
• Generate Comparison Reports
• Tune Regressed Statements
• Guided Workflow Analysis
• SQL Performance Analyzer Views

SQL Performance Management

• Maintenance of the Optimizer Statistics
• Automated Statistics Gathering
• Gather Statistics Options
• Defer Publishing Statistics
• Automatic SQL Tuning
• The SQL Tuning Advisor
• The SQL Access Advisor
• SQL Plan Management

Tune the Shared Pool

• Overview of the Shared Pool Architecture
• Latches and Mutexes
• Diagnostic Tools for Tuning the Shared Pool
• Avoiding Hard Parses
• Size the Shared Pool
• Avoiding Fragmentation of the Shared Pool
• The Data Dictionary Cache
• The SQL Query Result Cache

Tune the Buffer Cache

• Overview of the Database Buffer Cache Architecture
• Buffer Cache Tuning Goals and Techniques
• Buffer Cache Performance Symptoms and Solutions
• Database Smart Flash Cache
• When to Flush the Buffer Cache

Tune PGA and Temporary Space

• Overview of the PGA
• SQL Memory Usage
• Automatic PGA Memory Mode Configuration
• PGA Target Advice Statistics and Histograms
• Automatic PGA and AWR Reports
• Temporary Tablespace Management
• Temporary Tablespace Group
• Multiple Temporary Tablespaces
• Monitoring Temporary Tablespaces
• Temporary Tablespace Shrink
• Data Dictionary Views and Sort Segments

Automatic Memory Management

• Overview of Automatic Shared Memory Management
• Dynamic SGA
• Parameters for Sizing the SGA
• Enable and Disable Automatic Shared Memory Management
• Use the SGA Advisor
• Overview of Automatic Memory Management
• Enable and Disable Automatic Memory Management
• Monitor Automatic Memory Management

Create and Use Snapshots and Baselines with the Automatic Workload Repository

• An Overview of the Automatic Workload Repository
• AWR Data
• Enterprise Manager and the AWR
• Create and Compare Snapshots
• Examine AWR Reports
• Create and Compare Baselines
• Moving Window Baseline
• Baseline Templates
• Baselines Views
• Performance Monitoring and Baselines
• Define Alert Thresholds Using a Static Baseline
• Configure Adaptive Thresholds

Define the Scope of Performance Issues

• An Overview of Monitoring and Tuning Tools
• Common Tuning Problems
• Define the Problem and Limiting the Scope
• Set the Priority of a Performance Issue
• View the Top SQL Reports
• Tune During the Life Cycle of a problem
• Generate and Analyze ADDM Reports
• Performance Tuning Resources

Use Metrics and Alerts

• An Overview of Metrics and Alerts
• The Benefits and Limitations of Metrics and Alerts
• System Generated, Threshold Generated and Event Based Alerts
• Set Thresholds
• View Metric History Information
• View Histograms
• Metric and Alert Views

Use AWR-Based Tools

• Automatic Maintenance Tasks
• ADDM Performance Monitoring
• Active Session History
• Generate an ASH Report
• Real-time ADDM

Real Time Database Operation Monitoring

• Overview of Real Time Database Operation Monitoring
• Database Operation Concepts
• Define a Database Operation
• Enable the Monitoring of Database Operations
• Identify, Start and Complete a Database Operation
• Monitor the Progress of a Database Operation
• Database Operation Views
• Database Operation Tuning

Monitor Applications

• The Attributes and Types of Services
• Create Services
• Use Services with Client Applications
• Use Services with the Resource Manager
• Use Services the Oracle Scheduler
• Services and Metric Thresholds
• Use Aggregation of Services with Tracing
• The Top Services Performance Page

Database Replay

• Overview of Database Replay Architecture
• Use Enterprise Manager to Capture and Replay a Workload
• Use the Database Replay Packages to Capture and Replay a Workload
• Capture a Workload
• Pre-process the Captured Workload
• Configure Replay Clients
• Replay the Workload
• Replay Workload Options
• Analyze the Replayed Workload
• Database Replay Views

Tune Database I/O

• An Overview of Database I/O Management
• I/O Architecture and Modes
• Important I/O Metrics for Oracle Databases
• I/O Calibration
• I/O Statistics and Diagnostics
• Database I/O Tuning
• Automatic Storage Management (ASM)

A Summary of Oracle Performance Tuning

• The Potential Impact of Initialization Parameters on Performance
• Initially Size Memory for a Database
• Recommended Best Practices for Different Types of Tablespaces
• Determine and Use Block Sizes
• Size the Redo Log Buffer and the Redo Log Files
• Configure Automatic Statistics Gathering