Personal tools
You are here: Home Education Optimizing Oracle SQL, Intensive
Document Actions

Optimizing Oracle SQL, Intensive

Objective

This class is a 5-day intensive combination of material that would typically be covered in 7 days.  Focus is directed towards the optimizer and understanding how it behaves when developing query execution plans. The importance of optimizing SQL at the resource usage level is learned and the Hotsos SQL Test Harness and Tools Pack scripts are used to facilitate the comparison of multiple approaches to writing SQL statements to discover the "performance optimal" choice among several approaches.  Different approaches to writing high performance SQL are demonstrated as well as review of database design elements that can affect performance.

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 are wanting 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.

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.

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, data distribution and execution plans
    – Extended SQL trace data (10046) and optimizer trace data (10053)
    – The Hotsos SQL Test Harness
    – How to install and use to measure and store test results
  4. The Optimizer
  5. – Oracle query optimizer technologies (RBO, CBO)
    – Adaptive Query Optimization
    – Optimizer hints
    – Statistics collection
    – Selectivity
    – Histograms
    -Frequency, Height Based, Top Frequency and Hybrid 
  6. 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
  7. 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
  8. 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
  9. Writing SQL "right"
    – IN-Lists: how they work and alternatives
    – SEMI and ANTI joins
    – Analytic functions and SQL modeling
    – Scalar subqueries
    – WITH clause subquery factoring
    – Using ROWNUM
  10. Conclusion
    – Course wrap-up

Class Forum

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

Instructional Format

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

The instructors place a special emphasis on teaching students to think and test for themselves instead of relying on widely believed but unfortunately unreliable rules of thumb.

The course is conducted in English.

Materials

Each student receives the following materials:

Course Notes

A PDF copy of the presentation is provided prior to the beginning of training. Please down load this prior to start of training.

Tools

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

Pricing

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

Schedule

Location Starts Ends Instructors Register
Online [Map] 2014-07-28 2014-08-01
MI-Ann Arbor [Map] 2014-08-11 2014-08-15
Online [Map] 2014-08-18 2014-08-22
UT - Salt Lake City [Map] 2014-08-25 2014-08-29
CA - San Francisco [Map] 2014-09-08 2014-09-12
TX - Grapevine [Map] 2014-09-15 2014-09-19
Online [Map] 2014-09-22 2014-09-26
IL - Chicago [Map] 2014-10-06 2014-10-10
MN - Minneapolis [Map] 2014-10-20 2014-10-24
Online [Map] 2014-10-20 2014-10-24
WA - Seattle [Map] 2014-11-03 2014-11-07
FL - Orlando [Map] 2014-11-10 2014-11-14
[Map] 2014-12-01 2014-12-05
Online [Map] 2014-12-08 2014-12-12

Powered by Plone, the Open Source Content Management System

This site conforms to the following standards: