Oracle Database 10g Performance Tuning

The course is designed to introduce students to the full functional range of features and technologies within the Oracle 10g RDBMS needed to optimise and tune SQL statements.

This is done through explanation of the different access methods involved and practical based teaching to control the optimal retrieval paths chosen by Oracle.

Where necessary, this involves explanation of Oracle's internal mechanisms, and the supporting hierarchical structures required by a Relational Database.

A practical hands-on course, Oracle 10g SQL Tuning is an essential skill for all Oracle 10g SQL developers and Users to ensure that they maximise the performance of the data retrieval components of their business systems.

Course Content

Introduction
Cause of Performance Problems
Setting Performance Goals
The Tuning Cycle
ORACLE Architecture
Logical Storage Structures
Physical Structures
Memory Structures
The Shared Pool
Processes
Some Administration Terminology
back to top

Design
Data Design Phase
Data Model Design
Online Transaction Processing
Decision Support Systems
Multi-purpose Applications
back to top

Optimizing SQL
SQL Processing
Physical Retrieval of Data
Full Table Scan versus Index Reads
Performance Diagnostic Tools
Explain Plan
SQL Trace Facility
TKPROF
Autotrace
Join Methods
Sort/Merge Joins
Nested Loops
Hash Joins
Hash Join Example Data Access Methods
Basic Indexes
B-Tree Indexes
Bitmap Index
Creating and maintaining Bitmap Indexes.
Comparing B-Tree and Bitmap Indexes
Reverse Key Index
Creating Reverse Key Indexes
Index-Organised Tables
Creating Index-organised Tables
Clusters
Cluster Types
back to top

Automatic SQL Tuning
Query Optimizer Modes
Types of Tuning Analysis
SQL Tuning Advisor
SQL Tuning Sets
Top SQL
Identify high-load SQL
ADDM
Dynamic Performance views
Statspack
back to top

The Optimizer
Optimizing SQL
The ORACLE Optimizer
Initialisation parameters affecting the Optimizer.
Cost Based
Rule / Cost Comparisons
Choosing an Approach
Rule Based Approach
Multiple WHERE Clauses
Using Indexes without a WHERE clause
Using Indexes for Sorts
Multiple Table Joins
Disabling Indexes
Use of NOT IN / NOT EXISTS
Cost Based Optimizer
Cost Based Optimizer Operation
Hints.
Sharing SQL Statements
Other SQL Tuning Tips
back to top

Gathering Statistics
Analyzing Statistics
DBMS_STATS
Histograms
How to Generate Histograms Statistics
Migration from Rule to Cost based Optimization Plan stability:-Stored Outlines.
back to top

Optimising PL/SQL
Module Performance
Registering a Module
Tracking a Module
back to top

Advanced Tuning
Star Queries
Materialized Views
Refreshing Views
Materialized View Logs
Temporary Tables