Optimizing Oracle SQL, Intensive

Synopsis

This course is intended to teach students a repeatable and reliable method for optimizing SQL and to assimilate the new habit of testing optimization hypotheses rapidly during the performance improvement process. Instead of relying on "rules of thumb" and hit-and-miss guessing, this course helps the student understand the inner workings of the Oracle optimizer and how SQL execution paths are determined. Particular emphasis is placed on discovering how to capture, read, and understand execution plans and how to use 10053 trace data to follow the optimizer's plan selection. The student's attention is focused not upon rote learning and superficial understanding of Oracle rules of thumb, but upon a thorough understanding how the Oracle database works.

The course is not just about learning how to write SQL. The course is about arming the student with the skills to be able to scientifically determine the efficiency of any SQL statement. While the course does offer alternative ways to write SQL in many cases, the primary objective is to equip the student to determine whether a statement is optimal and to achieve a deeper understanding of the cost-based optimizer. This course presents problem-solving strategies that can be carried forward directly to common problems and customized for solving less common ones. The result is a fortified foundation for optimizing SQL in ever more challenging situations.

Excerpt from the Course

Want a preview of the course? Watch this short video to see one of the sections of material.

Syllabus

The course is organized into the following segments:

  1. Introduction
    – Course welcome, instructor and student introductions.
  2. Fundamentals
    – Course unit testing environment
    – Terms and definitions:
    – LIO, PIO, latching, buffer cache, library cache, etc.
    – Fundamentals of testing:
    – Understanding and measuring statement resource consumption
    – SQL statement execution fundamentals
    – PARSE, EXECUTE, BIND, FETCH, arraysize issues, etc.
  3. Tools of the Trade
    – Tools for SQL statement problem diagnosis and repair:
    – Statistics, datat distribution and execution plans
    – Extended SQL trace data (10046) and optimzer trace data (10053)
    – The Hotsos SQL Test Harness
    – How to install and use to measure and store test results
  4. The Optimizer
    – Oracle query optimizer technologies (RBO, CBO)
    – Optimizer parameters
    – Optimizer hints
    – RESULT_CACHE hint and table clause
    – Statistics collection
    – Selectivity
    – Histograms
  5. Access and Join Methods
    – Compare and contrast access methods:
    – TABLE ACCESS FULL, INDEX ACCESS BY ROWID
    – Index scan type overview:
    –Index unique scan, index skip scan, index fast full scan, index full scan, index joins
    – Compare and contrast join methods:
    – Nested loop, hash join, sort/merge join, and cartesian
    – Review conditions under which each method offers best and worst performance
  6. Execution plans
    – Capturing and displaying execution plans
    – V$SQL_PLAN, V$SQL_PLAN_STATISTICS, etc.
    – How to read and analyze execution plans
    – Optimizer query transformation behaviors
    – View merging
    – Subquery unnesting
    – Predicate pushing
  7. Indexes and Performance
    – Coding techniques that may prevent index use:
    – Column expressions
    – Implicit datatype conversion
    – Indexing null values
    – Impact of composite key order
    – Invisible indexes
    – Function-based indexes
    – Bitmap indexes
  8. Writing SQL "right"
    – IN-Lists: how they work and alternatives
    – SEMI and ANTI joins
    – Analytic functions
    – Scalar subqueries
    – WITH clause subquery factoring
    – Using ROWNUM
  9. Conclusion
    – Course wrap-up


  10. Target Audience

    The course is designed for application developers and database administrators who want to respond faster and more permanently to Oracle system performance problems that are known to be caused by inefficient application SQL code. Whether you want to learn how to write new code that performs optimally from the start or are looking to update and maintain previously written code, this course teaches you the skills you need. We take you 'under the covers' to see how and why your SQL performs as it does and lead you to understand how to most effectively provide the Oracle optimizer with the information it needs to produce optimal execution plans.

    Simply put, if you write SQL, this class is for you!

    The course is most effective for students who will be motivated to solve real Oracle system performance problems immediately upon returning to work after the course.



    Prerequisites

    Students are expected to have a working knowledge of Oracle SQL through experience either as a database administrator or application developer.



    Instructional Format

    The five-day course is approximately 60% instructor-led lecture and discussion, 40% in-class exercises. The course is conducted in English.



    Materials

    Each student receives the following materials:

    • Course Notes
      A printed copy of all the presentation slides used in the course.


    • Tools
      Access to course software tools and supplemental documentation at hotsos.com.


    Class Forum

    For questions and discussion about the course, please visit our forum.

    Pricing

    • List Price: $3600.00
    • Alumni Price: $1000.00*
      *Alumni pricing is available to prior attendees of this course.


    Schedule

    Please visit our Calendar of Events to find when this course will be offered next.