SQL Tuning Workshop

 Click here

Course Overview

SQL Tuning Workshop is designed to focus on the techniques needed to improve the performance of Oracle applications. The course begins by providing an conceptual view of the Oracle database architecture, SQL statement processing and the ins-and-outs of cost-based query optimization.

The students are given detailed instruction on how to use Oracle's diagnostic tools and utilities, such as EXPLAIN PLANs, SQL*Trace and TKPROF, SQL*Plus AUTOTRACE. They are also introduced to the automatic tuning mechanisms now available in the Oracle Database.

The tuning techniques covered in the course include: proactive monitoring, access paths, join methods, query transformations, indexing strategies, SQL hints, etc. The course also includes a series of hands-on exercises to familiarize students with the behavior of the cost-based optimizer.

Course Goals

• Understand basic steps in processing SQL statements
• Describe the causes of performance problems
• Understand where SQL tuning fits in an overall tuning methodology
• Describe Automatic SQL Tuning
• Influence the physical data model so as to avoid performance problems
• Understand Optimizer behavior
• Use the diagnostic tools to gather information about SQL statement processing
• Introduce Oracle automated tuning features such as AWR, ADDM and SQL Tuning Advisor

Prerequisites

• Introduction to SQL course

Who Should Attend?

• Database Administrators
• PL/SQL Developers
• Forms Developers
• Technical Consultants
• Reports Developers
• Business Intelligence Developers
• Java Developers

Course Materials

• Student Handbook
• Reference Book

Duration

• Three (3) days

Course Outline

• Introduction
  • Server Architecture Overview
  • Shared Memory Structures
  • Automatic Shared Memory Management
  • Program Global Area (PGA)
  • Automatic SQL Execution Memory (PGA) Management
  • SQL Statement Processing
  • The Oracle Query Optimizer
• SQL Tuning Methodology
  • Performance Management
  • Tuning Goals
  • SQL Tuning Overview
  • Manual SQL Tuning
  • Automatic SQL Tuning
  • SQL Tuning Advisor
  • SQL Access Advisor
• Designing for Scalability
  • Understanding Scalability
  • Implementing a Scalable System
  • Proactive Tuning Methodology
  • Data Modeling
  • Table Design
  • Index Design
  • Using Views
  • SQL Execution Efficiency
• Introduction to the Optimizer
  • Oracle Query Optimizer
  • Optimizer Query Functions
  • Enabling Optimizer Features
  • Controlling Optimizer Behavior
  • Optimizing SQL Statements
• Optimizer Operations
  • Optimizer Execution Factors
  • Access Paths
  • Join Order
  • Join Methods
  • Subqueries
  • Sort Operations
  • Memory and Optimizer Operations
• Execution Plans
  • DBMS_XPLAN Package
  • EXPLAIN PLAN Command
  • V$SQL_PLAN View
  • V$SQL_PLAN_STATISTICS View
  • Automatic Workload Repository
  • SQL*Plus AUTOTRACE
• Collecting Statistics
  • Optimizer Statistics
  • Automated Statistics Collection
  • Statistics Collection Configuration
  • Manual Statistics Collection
  • Dynamic Sampling
  • Generating System Statistics
  • Data Dictionary Statistics
• Application Tracing
  • Overview of Application Tracing
  • Using Enterprise Manager
  • Using DBMS_MONITOR
  • Using SQL Trace
  • The TKPROF Command
  • The trcsess Utility
• High-Load SQL
  • The SQL Tuning Process
  • Identifying High-Load SQL
  • Automatic Database Diagnostic Monitor
  • Top SQL
  • Spot SQL
  • Period SQL
  • Statspack
  • Dynamic Performance Views
• Automatic SQL Tuning
  • The SQL Tuning Process
  • Automatic SQL Tuning
  • Automatic Tuning Optimizer
  • SQL Tuning Advisor
  • SQL Tuning Sets
  • SQL Access Advisor
• Index Basics
  • Index Guidelines
  • Types of Indexes
  • Using Indexes
  • Basic Access Methods
  • Monitoring Index Usage
• Indexing Techniques
  • Composite Indexes
  • Skip Scanning
  • Bitmap Indexes
  • Bitmap Join Index
  • Function-Based Indexes
  • Index-Organized Tables
• Optimizer Hints
  • Optimizer Hint Syntax
  • Using Optimizer Hints
  • Hints for Optimizer Behavior
  • Hints for Access Paths
  • Hints for Query Transformation
  • Hints for Join Orders
  • Hints for Join Operations
  • Other Hints
  • Hints and Views
• Materialized Views
  • Types of Materialized Views
  • Using Materialized Views
  • Creating Materialized Views
  • Query Rewrites
  • SQL Access Advisor
  • Using the DBMS_MVIEW Package
  • Designing for Query Rewrite
  • Materialized View Hints