Oracle® SQL Performance Tuning for Developers




Oracle SQL Performance Tuning for Developers Training Class Summary

The focus of this tuning course is to illustrate coding techniques that insure a consistent response time between instances and releases of the Oracle database. This course works closely with performance tuning of actual SQL statements. The course starts out with a complete overview of the Oracle architecture so students can get an understanding how their SQL and applications can take advantage of the computing environment. This course goes in-depth on understanding and controlling the explain plan (how Oracle retrieves data and in what order). The discussions include the differences of the various Explain Plan steps such as Merge-Join and Nested-Loop, and when is it best to use each. The course goes into considerable detail, with SQL examples, on how the optimizers (both rule-based and cost-based but mostly cost-based) make their decisions. Students will work with a variety of SQL statements, reviewing explain plans and making changes to make these SQL statements perform better. Lectures include index design, using hints and coding style to control the explain plans, and how to use useful tools such as index monitoring, SQL Trace, and the PL/SQL profiler. 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. The focus this course is tuning SQL via coding style. Tuning in this fashion maintains the performance of the SQL when migrating to other Oracle environments (upgrades to newer releases). Lectures and topics are enhanced with live illustrations and hands-on exercises.

Audience: Oracle application developers.

Prerequisites: Introduction to Oracle 11g SQL Programming and at least 6 months of Oracle experience recommended.

Class Length: 2 days

Oracle SQL Performance Tuning for Developers Training Class Objectives
  • Describe the Oracle architecture from a SQL performance point-of-view
  • Understand SQL tuning statement topics
  • Read and understand Explain Plans
  • Control both the Cost-based and Rule-based optimizers
  • Explain how indexes work and how they are selected
  • Tune SQL via coding style
  • Use Oracle's trace facility to collect SQL trace information
  • Interpreting trace information using TKProf
  • Profile PL/SQL
  • Apply PL/SQL coding tips
Oracle SQL Performance Tuning for Developers Training Class Detailed Outline
  1. Course Introduction
    • Course Objectives
    • Overview
    • Suggested References
  2. SQL Statement Tuning
    • Oracle RDBMS Architecture overview
    • Understanding/Reading/Interpreting Explain Plans
    • Understanding the Rule-based Optimizer
    • Understanding the Cost-based Optimizer
    • Working with Hints
  3. Index Review
    • index Review
    • Tips and Techniques
  4. Helping the CBO
    • A close look at sub-query coding techniques
    • Plan Stability
    • Histograms
    • SQL Coding Tips
  5. Oracle Tuning Tools
    • Tuning Tool Review: SQL Tracing/Tkprof
    • Profiling and tuning PL/SQL
    • PL/SQL Coding Tips