Our 5-day lab-based SQL optimization course transforms application developers and database administrators into fearless SQL optimizers. Here, students learn to measure performance instead of guessing about it, using an efficient performance testing harness that the students get to take home. The course produces faster students writing faster SQL that will scale efficiently to handle your production workloads.

Course overview

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 10046 and 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.

Pricing

List price: $3,600 USD

Course outline

Introduction

Course welcome, agenda, instructor and student introductions.

Fundamentals

• 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.
• Tools for SQL Statement Problem Diagnosis and Repair: statistics report, block and row selectivity report, execution plans
• Oracle 10046 and 10053 trace data collection and analysis
• The SQL Test Harness: How to install and use to measure and store test results

The Optimizer

• Oracle query optimizer technologies (RBO, CBO)
• Optimizer query transformation behaviors
• view merging
• subquery unnesting
• predicate pushing
• Optimizer parameters
• Optimizer hints and stored outlines
• Statistics: Collecting, managing and understanding statistics
• Selectivity
• Histograms

Access Methods

• Compare and contrast access methods: table access full, access by rowid
• Index scan type overview: index unique scan, index range scan, index skip scan, index fast full scan, index full scan, index joins

Join Methods

• Compare and contrast join methods: nested loops, hash join, sort/merge join, and cartesian
• Review conditions under which each method offers best and worst performance

Execution Plans

• Capturing and displaying execution plans
• Review dynamic views associated with execution plans: V$SQL_PLAN, V$SQL_PLAN_STATISITICS, etc.
• How to read and analyze execution plans
• Creating and using plan tree diagrams

Indexes and performance

• Coding techniques that may prevent index use:
• column expressions
• implicit datatype conversion
• indexing null values
• impact of composite key order
• Function-based indexes
• Index-organized tables
• Bitmap indexes

Writing SQL "Right"

• IN-Lists: how they work and alternatives
• Expressions using CASE, COALESCE, and NULLIF
• ANTI and SEMI joins
• Scalar subqueries
• WITH clause subquery factoring
• Analytic and modeling functions
• Using ROWNUM
• Using MERGE

Conclusion

• Case study review
• Perspective
• Course wrap-up
• Graduation