Course Introduction

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

Session 1: INTRODUCTION TO ORACLE PERFORMANCE TUNING

• Tuning Overview
• Oracle Tuning Process
• Planning a Routine Monitoring Regime
• Setting Suitable Goals
• Tips for Avoiding Problematic Queries

Session 2: TOOLS FOR EVALUATING SQL STATEMENTS

• Overview of SQL Statement Tuning
• Tools to Assist in SQL Tuning
• Explain Plan
• Autotrace
• SQL Trace
• The tkprof Program
• Interpreting SQL Trace

Session 3: THE OPTIMIZER

• SQL Optimizer
• OPTIMIZER_MODE Initialization Parameter
• Rule Based Optimizer
• Cost Based Optimizer
• The Analyze Command
• Managing Statistics with DBMS_STATS
• Automatic Statistics Gathering

Session 4: SORTS

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

Session 5: INDEXES

• Index Overview
• B*Tree Indexes
• Access Paths with Indexes
• Index Scans
• Conditions That 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
• Invisible Indexes

Session 7: JOINS

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

Session 8: SEQUENCES AND VIEWS

• Sequence Caching
• Views
• View Merging
• Inline Views

Session 9: USING HINTS

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

Session 10: MISCELLANEOUS

• Syntax Considerations
• Array Size
• hared Pool
• Intelligent Cursor Sharing
• Virtual Columns
• Result Caching
• Some PL/SQL Issues