Oracle Database 11g Release 2 DBA Performance & Tuning

Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: INTRODUCTION TO ORACLE PERFORMANCE TUNING

• Tuning Overview of Oracle Database Tuning
• Application Developer Tuning Responsibilities
• Oracle DBA Tuning Responsibilities
• Oracle Tuning Process
• Plan a Routine Monitoring Regime
• Setting Suitable Goals

Session 2: TOOLS FOR EVALUATING SQL STATEMENTS

• Overview of SQL Statement Tuning
• Tools to Assist in SQL Tuning
• Use Explain Plan, Autotrace and SQL Trace to Examine the Execution of a SQL Statement
• Interpreting a SQL Trace

Session 3: THE SQL OPTIMIZER

• The SQL Optimizer
• Statement Transformation
• The Optimizer_Mode Initialization Parameter
• Cost Based Optimizer
• Managing Statistics with DBMS_STATS
• Automatic Statistics Gathering
• Dynamic Statistics
• Online Statistics Gathering for Bulk Loads

Session 4: SORTS

• How Oracle Processes Sorts
• Temporary Disk Space Assignment
• SQL Operations that Use Sorts

Session 5: INDEXES

• Index Overview
• Selecting Suitable Columns for an Index
• B*Tree Indexes
• Rebuild an Index
• Composite Indexes
• Descending Indexes
• Access Paths with Indexes
• Index Scans
• Conditions That Stop Indexes Being Used
• Parameters that Affect Optimizer Index Choice

Session 6: ADVANCED INDEXES

• Bitmap Indexes
• Key Compressed Indexes
• Index Organized Tables
• Function Based Indexes
• Invisible Indexes

Session 7: JOIN OPERATIONS

• Understand Access Paths
• Joining Tables
• Nested Loops Join
• Merge Join
• Cluster Join
• Hash Join
• Anti Join and Semi Join
• Outer Joins
• Star Join
• Improve Optimization with Different Access Paths

Session 8: SQL TUNING ADVISOR USING SQL DEVELOPER

• Overview of the DBMS_SQL_TUNE Package
• Using the SQL Tuning Advisor with SQL Developer

Session 9: SEQUENCES AND VIEWS

• Sequence Caching
• Views
• View Merging
• Inline Views

Session 10: USING HINTS

• Using Hints to Influence Execution Plan
• Optimization Mode and Goals
• Access Methods
• Query Transformations
• Join Orders
• Join Operations
• Hint Examples

Session 11: MISCELLANEOUS

• Tips for Avoiding Problematic Queries
• Array Size
• The Shared Pool
• The WITH Clause
• Bind Variable Usage
• Result Caching
• Some Miscellaneous PL/SQL Issues

Session 12: 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

Session 13: REDUCE THE COST OF SQL OPERATIONS

• Maintain Tables
• Reorganize Tables
• Manage Extents
• Compress Tables
• Row Chaining and Row Migration
• Segment Shrink

Session 14: THE SQL PERFORMANCE ANALYZER

• 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

Session 15: SQL PERFORMANCE MANAGEMENT

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

Session 16: 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

Session 17: TUNE THE BUFFER CACHE

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

Session 18: 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

Session 19: 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

Session 20: 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

Session 21: 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

Session 22: 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

Session 23: USE AWR-BASED TOOLS

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

Session 24: 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

Session 25: 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

Session 26: 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

Session 27: 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)

Session 28: 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