Optimizing Oracle SQL (5 days US$3,600)

Our 5-day lab-based 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 keep. The course produces faster students writing faster SQL that will scale efficiently to handle your production workloads.

Course Overview

This course teaches students how to optimize Oracle SQL. Important features of the course include:

  • Reliability and repeatability. We teach students not to rely on “rules of thumb” and guesswork. We teach how the Oracle query optimizer works so that students can scientifically determine the efficiency of any SQL statement.
  • Better habits. We teach students how to test their hypotheses (“Which is better: A? or B?”) quickly and efficiently, so that they will test instead of succumbing to the temptation of guessing. We reinforce these habits by executing lots and lots of examples with the students.
  • Strong foundation. We teach students how to use Oracle’s most powerful tools to illuminate why the query optimizer behaves as it does. The result is a fortified foundation from which students can solve far more challenging problems than they could before the course.
  • Version resilience. Oracle continuously introduces new optimization features into its Oracle Database product. It is impossible for anyone but the most dedicated specialist to keep up. In this course, we teach you how to find out the optimal way to write SQL, regardless of database version.

Price

Price for the 5-day course is US$3,600 per student.

Course outline

  1. Introduction. Course welcome, agenda, instructor and student introductions.
  2. Fundamentals. Terms and definitions, fundamentals of testing, SQL statement execution fundamentals, tools for SQL statement problem diagnosis and repair, Oracle 10046 and 10053 trace data collection and analysis, SQL Test Harness.
  3. The Optimizer. Optimizer query transformation behaviors, optimizer parameters, optimizer hints and stored outlines, statistics, selectivity, histograms.
  4. Access Methods. Compare and contrast, index scan type overview.
  5. Join Methods. Compare and contrast.
  6. Execution Plans. Capturing and displaying execution plans, dynamic views associated with execution plans, how to read and analyze execution plans, creating and using plan tree diagrams.
  7. Indexes and Performance. Coding techniques that may prevent index use, index types.
  8. Writing SQL “Right.” IN-lists, expressions, ANTI and SEMI joins, scalar subqueries, WITH clause subquery factoring, analytic and modeling functions, using ROWNUM, using MERGE.
  9. Conclusion. Case study review, perspective, course wrap-up, graduation.