Course Introduction

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

Session 1: INTRODUCTION TO ORACLE PERFORMANCE TUNING

• Tuning Overview
• Resolving Performance Problems
• The Oracle Approach to Tuning
• Items to Evaluate
• Where to Look
• Plan a Routine Monitoring Regime
• Build a New Database for Performance
• Tune an Existing Database for Performance
• Set Suitable Goals

Session 2: DIAGNOSING PROBLEMS

• Diagnostic Sources
• Log a Performance Service Request (SR)
• Tips for Avoiding Problematic Queries

Session 3: TOOLS FOR EVALUATING SQL STATEMENTS

• Overview of SQL Statement Tuning
• Explain Plan and autotrace
• SQL Trace and the tkprof Program
• Interpreting SQL Trace

Session 4: THE OPTIMIZER

• The SQL Optimizer
• The Rule Based Optimizer
• The Cost Based Optimizer
• The Analyze Command
• Manage Statistics with DBMS_STATS

Session 5: INDEXES

• Index Overview
• B*Tree Indexes
• Access Paths with Indexes
• Index Scans
• Conditions which Stop Indexes Being Used
• Parameters Affecting Optimizer Index Choice

Session 6: ADVANCED INDEXES

• Bitmap Indexes
• Key Compressed Indexes
• Index Organized Tables
• Function-Based Indexes
• Descending Indexes

Session 7: TYPES OF TABLE JOINS

• Joining Tables
• Nested Loops Join
• Cluster Join and Hash Join
• Anti Join and Semi Join
• Outer Join
• Star Join

Session 8: METRICS, ALERTS AND BASELINES

• System Generated Alerts
• Threshold Based Alerts
• Event Based Alerts
• Retrieve Threshold Information
• View Alert Data
• Manage Metrics Using EM
• Blackouts
• Baselines and Adaptive Thresholds

Session 9: STATSPACK

• Overview Of Statspack
• Statspack Architecture
• Install Statspack
• Adjust the Statspack Collection Level
• Create, View and Delete Snapshots
• Generate a Report
• Analyze the Reports to Generate an Execution Plan for a Given SQL Statement
• Resolve Wait Events
• Remove Statspack from the Database

Session 10: AUTOMATIC WORKLOAD REPOSITORY

• Automatic Workload Repository
• Create Snapshots and Compare Snapshots
• Use Baselines
• Generate AWR Reports
• Compare AWR Periods Reports
• Generate Active Session History (ASH) Report
• Automatic Database Diagnostic Monitor (ADDM)
• Generate and Analyze ADDM Reports

Session 11: REACTIVE TUNING

• Use Enterprise Manager to Identify Common Problems
• Eliminate Operating System Contentions

Session 12: TUNING THE SHARED POOL

• Overview of the Shared Pool
• Alter and Tune the Shared Pool Size
• Tune the Data Dictionary Cache and the Library Cache
• Shared Pool Advisory Statistics
• Pin objects in the Shared Pool
• Cursor Sharing
• Using the Large Pool
• Latches and Mutexes

Session 13: TUNE THE BUFFER CACHE

• Memory Structures
• Data Block Buffer Pool
• Multiple Buffer Caches
• Manage Free Buffers
• Establish the Buffer Pool Efficiency
• Cache Tables in the Buffer Pools
• Common Performance Issues and the Buffer Cache

Session 14: AUTOMATIC MEMORY MANAGEMENT

• Automatic Memory Management
• Automatic Shared Memory Management
• Memory Allocation Advisors
• The Memory Advisor
• The Shared Pool and Buffer Cache Advisors

Session 15: TUNE PGA AND TEMPORARY SPACE

• PGA Overview
• Automatic PGA Mode
• Configure the PGA for a New Instance
• The Program Global Area Advisor
• PGA Management Data Dictionary Views
• Tune the PGA_AGGREGATE_TARGET
• SQL Operations that Use Sorts
• Sorting to Disk
• Multiple Temporary Tablespaces
• Allocate Temporary Disk Space for Sorting
• Temporary Tablespace Shrink
• Data Dictionary Views and Sort Segments

Session 16: CHECKPOINT AND REDO TUNING

• Checkpoint and Redo Issues
• Instance Recovery Performance Tuning
• Redo Log File Size Advisor
• Size the Redo Log Buffer
• Set Multiple Database Writers

Session 17: TUNE I/O

• Database I/O Management
• Layout the Files Using OS or Hardware Striping
• Manually Distribute Files to Reduce I/O Contention
• When to Separate Files
• Sample Configurations
• Select the Data Block Size
• Synchronous I/O Benefits
• FILESYSTEMIO_OPTIONS Parameter
• Automatic Storage (ASM) Monitoring

Session 18: USE ORACLE BLOCKS EFFICIENTLY

• Determine the Database Block Size
• Tablespace, Table and Index Storage Parameters
• Automatic Segment Space Management
• Detect and Resolve Freelist Contention
• Rebuild Tables and Indexes
• The DBMS_SPACE Package
• Row Migration
• Chained Rows
• Bigfile Versus Smallfile Tablespaces
• Extended ROWID Format
• Convert from Dictionary Managed Tablespaces
• Convert to Automatic Segment Space Management

Session 19: MATERIALIZED VIEWS

• Overview of Materialized Views
• Materialized View Creation
• Materialized View Refresh
• Query Rewrite
• Materialized View Data Dictionary Views

Session 20: PARALLEL EXECUTION

• Overview of Parallel Execution
• Enabling and Using Parallel Execution
• The Degree of Parallelism
• SQL Hints
• Restrictions on the Use of Parallel Execution
• Dictionary Views on Parallel Execution
• Specific Automatic Parallel Query Optimization
• Nologging Options

Session 21: TRACING SERVICES

• End to End Tracing
• Enable and Disable Tracing
• Check for Tracing Enabled
• The trcess Utility
• Session Based Tracing
• Enable and View SQL Tracing with Enterprise Manager
• Analyze Trace Results