Oracle9I SQL Tuning Course

This course is tailored for developers to write efficient, well-tuned SQL statements. An in-depth discussion of the Oracle optimizer is considered including cost based optimization, optimization modes and altering behavior of the optimizer. Various utilities, which assist in SQL statement tuning, are also presented including EXPLAIN PLAN, TKPROF and AUTOTRACE. Special techniques such as histograms and specialty indexes are also considered.

This course is designed for Oracle developers.

Upon completion of this course, the participant will be able to:
> Examine the execution plan of the Oracle optimizer
> Alter the mode and goals of the optimizer
> Collect statistics for database objects
> Interpret and influence SQL statement execution plans
> Exploit Oracle9i plan stability techniques

Understanding The Tuning Issues
OLTP Vs. Data Warehouses
Database Tuning
Infrastructure & Network Issues

SQL Statement Execution
Parse Phase
Execution Phase
Fetch Phase
Dedicated Server Processes
Multi-Threaded Server Processes
Parallel Query Processes

Explain Plan Utility
Generating The Execution Plan
Viewing The Execution Plan
Interpreting The Execution Plan

Cost Based Optimizer (CBO)
Optimization Methods
Collecting Statistics
Analyze
DBMS_STATS()
Chained Rows

Collecting Statistics
Statistics Collection Methods
Monitoring Object Modifications
Using The DBMS_STATS() Package
Using The Analyze Command
Create Index...Compute Statistics

Optimizer Operations
Table Scan
Join Operations
Hash Operations

Indexes & The Execution Plan
B-Tree Indexes
Bitmap Indexes
Function-Based Indexes

Optimizer Hints

Using TKPROF & Autotrace

Histograms
Creating Histograms
Data Dictionary Storage

Using Plan Stability
Preparing For Stored Outlines
Creating & Using Stored Outlines
Data Dictionary Storage
Managing Stored Outlines

Editing Private Outlines
Preparing For Private Outlines
Creating Private Outlines
Editing Private Outlines
Utilizing Private Outlines
Collecting System Statistics
Managing System Statistics
Developing A Tuning Strategy

Exploiting & Managing Cursor Sharing
Bind Variables & Cursor Peeking
Using The Cursor_Sharing Parameter

Understanding The Rule Based Optimizer
About The RBO
Activating The RBO Explicitly
How The RBO Works
The Access Paths
RBO Join Execution Plan Selection

Tuning With The Rule Based Optimizer
Correctly Building Indexes
Understanding When Indexes Will Be Ignored
Rewriting Queries To Avoid Index Suppression
Rewriting Queries To Force Index Suppression

Oracle9i SQL (RT 262)
Oracle9i Advanced SQL (RT 258)
Oracle9i Architecture For Developers (RT 284)