Oracle SQL 10g: Performance Tuning (2 day)

Scheduled Dates (sort by: location | date)

Course Overview

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.

Audience

• Oracle Analyst/Programmers
• Oracle Technicians
• Oracle DBA's
• Oracle Developers

Prerequisites

PC Fundamentals

Course Outline

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

Design

• Data Design Phase
• Data Model Design
• Online Transaction Processing
• Decision Support Systems
• Multi-purpose Applications

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

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

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

Gathering Statistics

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

Optimising PL/SQL

• Module Performance
• Registering a Module
• Tracking a Module

Advanced Tuning

• Star Queries
• Materialized Views
• Refreshing Views
• Materialized View Logs
• Temporary Tables

Oracle SQL 10g: Performance Tuning (2 day) / OD10-SPT2