Oracle Database 11g: Performance Tuning Training Course

Module 1. Basic Tuning Tools

• Monitoring tools overview
• Enterprise Manager
• V$ Views, Statistics and Metrics
• Wait Events

Module 2. Using Automatic Workload Repository

• Managing the Automatic Workload Repository
• Real Time SQL Monitoring

Module 3. Defining Problems

• Defining the Problem
• Limit the Scope and Setting the Priority
• Top SQL Reports
• Common Tuning Problems
• ADDM Tuning Session
• Performance Tuning Resources
• Monitoring and Tuning Tools: Overview

Module 4. Using Metrics and Alerts

• Metrics, Alerts, and Baselines
• Limitation of Base Statistics
• Oracle Database 11g Solution: Metrics
• Benefits of Metrics
• Viewing Metric History Information
• Statistic Histograms
• Database Control Usage Model and Setting Thresholds
• Server-Generated Alerts, Creating and Testing an Alert

Module 5. Using Baselines

• Comparative Performance Analysis with AWR Baselines
• Automatic Workload Repository Baselines
• Moving Window Baseline
• Baselines in Performance Page Settings & Baseline Templates
• AWR Baselines
• Managing Baselines with PL/SQL and Baseline Views
• Performance Monitoring and Baselines
• Using EM to Quickly Configure

Module 6. Using AWR Based Tools

• Automatic Maintenance Tasks
• ADDM Performance Monitoring
• Active Session History: Overview

Module 7. Monitoring an Application

• Creating Services
• Using Services with Client Applications
• Services and Resource Manager with EM
• Service Aggregation and Tracing
• Client Identifier Aggregation and Tracing
• Service Performance Views

Module 8. Identifying Problem SQL Statements

• SQL Statement Processing Phases and Role of the Oracle Optimizer
• Identifying Bad SQL, Real Time SQL Monitoring
• TOP SQL Reports
• What Is an Execution Plan?
• Methods for Viewing Execution Plans and Uses of Execution Plans
• DBMS_XPLAN Package: Overview and EXPLAIN PLAN Command
• Reading an Execution Plan, Using the V$SQL_PLAN View and Querying the AWR
• SQL*Plus AUTOTRACE and SQL Trace Facility
• How to Use the SQL Trace Facility
• Generate an Optimizer Trace

Module 9. Influencing the Optimizer

• Functions of the Query Optimizer, Selectivity, Cardinality and Cost
• Changing Optimizer Behavior
• Using Hints, Optimizer Statistics and Extended Statistics
• Controlling the Behavior of the Optimizer with Parameters
• Enabling Query Optimizer Features & Influencing the Optimizer Approach
• Optimizing SQL Statements, Access Paths and Choosing an Access Path
• Join and Sort Operations
• How the Query Optimizer Chooses Execution Plans for Joins
• Reducing the Cost

Module 10. Using SQL Performance Analyzer

• Real Application Testing
• SQL Performance Analyzer: Process and Capturing the SQL Workload
• Creating a SQL Performance Analyzer Task
• Comparison Report
• Tuning Regressing Statements and Preventing Regressions
• Parameter Change Analysis

Module 11. SQL Performance Management

• Maintaining SQL Performance and Optimizer Statistics
• Statistic Gathering Options
• Restore Statistics
• Deferred Statistics Publishing
• Automatic SQL Tuning: Overview
• SQL Tuning Advisor: Overview
• Using the SQL Access Advisor
• SQL Plan Management: Overview

Module 12. Using Database Replay

•  
• Database Replay Workflow in Enterprise Manager
• Packages and Procedures
• Data Dictionary Views: Database Replay
• Database Replay
• Calibrating Replay Clients

Module 13. Tuning the Shared Pool

• Shared Pool Architecture and Operation
• The Library Cache and Latch and Mutex
• Diagnostic Tools for Tuning the Shared Pool
• Avoiding Hard and Soft Parses
• Sizing the Shared Pool and Avoiding Fragmentation
• Data Dictionary Cache and SQL Query Result Cache
• UGA and Oracle Shared Server
• Large Pool and Tuning the Large Pool

Module 14. Tuning the Buffer Cache

• Oracle Database Architecture: Buffer Cache
• Database Buffers
• Buffer Hash Table for Lookups
• Working Sets
• Buffer Cache Tuning Goals and Techniques
• Buffer Cache Performance Symptoms and Solutions
• Automatically Tuned Multiblock Reads
• Flushing the Buffer Cache

Module 15. Tuning PGA and Temporary Space

• SQL Memory Usage and Performance Impact
• SQL Memory Manager
• Configuring Automatic PGA Memory
• Setting PGA_AGGREGATE_TARGET Initially
• Monitoring and Tuning SQL Memory Usage
• PGA Target Advice Statistics and Histograms
• Automatic PGA and Enterprise Manager
• Automatic PGA and AWR Reports
• Temporary Tablespace Management
• Temporary Tablespace Shrink
• Tablespace Option for Creating Temporary Table

Module 16. Automatic Memory Management

• Oracle Database Architecture, Dynamic SGA
• Memory Advisories
• Manually Adding Granules to Components
• Increasing the Size of an SGA Component
• SGA Sizing Parameters and Manually Resizing Dynamic SGA Parameters
• Behavior of Auto-Tuned and Manually Tuned SGA Parameters
• Using the V$PARAMETER View
• Resizing SGA_TARGET
• Disabling, Configuring and Monitoring ASMM

Module 17. Tuning Segment Space Usage

• Space and Extent Management
• Locally Managed Extents
• How Table Data Is Stored and Anatomy of a Database Block
• Minimize Block Visits
• The DB_BLOCK_SIZE Parameter
• Small and Large Block Size: Considerations
• Block Allocation, Free Lists and Block Space Management with Free Lists
• Automatic Segment Space Management
• Migration and Chaining, Shrinking Segments and Table Compression: Overview

Module 18. Tuning I/O

• I/O Architecture, File System Characteristics, I/O Modes and Direct I/O
• Interface and I/O Statistics and Enterprise Manager
• Using RAID
• I/O Diagnostics
• Database I/O Tuning