Oracle® Advanced SQL Performance Tuning




Oracle Advanced SQL Performance Tuning Training Class Summary

This hands-on course covers a number of advanced topics such as Index clustering factor, Oracle10g Tablespace options that affect index performance, various Oracle traces including the 10053 Cost-Based Optimizer trace and sizing the SORT_AREA_SIZE and HASH_AREA_SIZE using the 10053, 10033, 10034, and 10104 traces. Lectures include an in-depth look at various SGA structures such as buffer cache options and sizing, library cache and cursor sharing, and Oracle fragmentation and locking issues/data block internals. Students will also experience how to monitor their application environment, their indexes, and see how to find problem SQL in their applications using Stats Pack, SQL Trace, AWR, SQL Tuning Advisor, and the V$ dictionary tables (using a variety of supplied SQL*Plus scripts). This course takes a close look at indexes: how Oracle selects them, why they are sometimes not used, and how to tell if indexes are being used/not being used. Hands-on lab exercises that reinforce the lecture topics. This course shows how to use TOAD, SQL Developer and SQL*Plus for SQL Performance Tuning.

Audience: Database administrators that need additional background to solve the more difficult problems.

Prerequisites: A background in SQL tuning; working knowledge of Oracle's SQL language. Knowledge of SQL Explain Plans is helpful.

Class Length: 2 days

Oracle Advanced SQL Performance Tuning Training Class Objectives
  • Detail Oracle's architecture from a SQL performance point-of-view
  • Read and understand Explain Plans
  • Review CBO Hints
  • Explain how indexes work and how they are selected
  • Master advanced topics such as clustering factor and other reasons indexes may NOT be selected
  • Use Oracle's trace facility, StatsPack, wait events
  • Trace wait events back to the problem SQL
  • Take advantage of the Oracle AWR and new tools such as SQL T
  • Profile PL/SQL
  • Apply PL/SQL coding tips
  • Use Buffer Cache options and sizing
  • Apply knowledge of Library Cache utilization/internals including cursor-sharing
  • Use the Oracle11g Result Cache
  • Work with Oracle10g Tablespace options that affect indexes
  • Explain Oracle data block internals and fragmentation
  • Find various problems using the V$ dictionary tables with scripts
Oracle Advanced SQL Performance Tuning Training Class Detailed Outline
  1. Course Introduction
    • Course Objectives
    • Overview
    • Suggested References
  2. SQL Statement Tuning
    • Oracle RDBMS Architecture overview
    • Review Reading and Interpreting Explain Plans
    • Understanding the Rule-based Optimizer
    • Understanding the Cost-based Optimizer
  3. Working with Indexes and Hints
    • Advanced Index Review
    • Tips and Techniques
  4. SGA Review and SQL Trace
    • Review SGA Memory Structures including:
    • - Buffer Cache
    • - Result Cache
    • - Library Cache
    • SQL Trace File Analysis
    • - Using TOAD (new features), TKProf, and SQLDeveloper
    • - Using SQL TXPlan (new free Oracle SQL analysis tool)
  5. Additional Advanced Topics
    • A close look at other useful Oracle Traces
    • - 10053 CBO Trace
    • - 10030 & 31 Sort Traces
    • - 10104 Hash Join Traces
    • Oracle Internals: How Oracle writes
    • - Various Space Management Issues discussed
    • - Monitoring Sorting
    • - Why does my Oracle10g run slower than my Oracle9i
    • Finding Problem SQL using v$ information
    • Using Automated Workload Repository
    • Review the SQL Tuning Advisor