Advanced Oracle SQL Tuning Training

Tanel Poder

2018/08/16

When I delivered a previous version of this class last, it was year 2013 and the latest Oracle version was 11.2. Since plenty of people have asked if I ever do this training again - and now that Oracle 18c is out with a lot of new (and adaptive) stuff added since version 11, I’ve decided to update the material and run it again as an online training class :)

Training overview

This training session is entirely about making Oracle SQL execution run faster and more efficiently, understanding the root causes of SQL performance problems and Cost Based Optimizer misbehavior.

Instead of looking into increasing system utilization (by using more buffer cache or more parallelism) as a first “tuning” choice, this class is mostly about decreasing resource usage of SQL execution plans by improving their shape and available access paths.

You will learn the full range of techniques for SQL optimization starting from optimal data access and processing strategy all the way to advanced topics such as comprehensive SQL hinting and adjusting optimizer’s row-count estimates.

Some SQL performance problems cannot be cured by simply adjusting optimizer behavior or improving stats, so a significant part of this training also concentrates on good database design, indexing practices and writing high performance SQL statements.

Finally, this class is not just a limited list of “tips and tricks”, it will go deep into SQL execution fundamentals and data flow internals. This gives you the knowledge and tools for systematic tuning of SQL with any complexity.

Training Duration and Details

Objectives

After this class you won’t need to memorize every single SQL performance problem or optimizer issue out there, instead you will be able to systematically work out the problem root causes yourself!

Non-objectives


Deep Dive Sessions

Like my other classes, the AST class will be delivered online, in 10 half-days spread over two weeks. In addition to leaving half of your day for normal work stuff, there’s a one week gap between the Part1 and Part 2 of the training for catching up with work (or vacation!) and for putting the first learned techniques in practice too!

In general, the two weeks of learning will be split into dealing with these categories:

The order of deep dives in the class is following:

  1. Intro: Quick start & what to check when SQL performance is bad
  2. Understanding SQL Plan Execution (reading plans of any complexity)
  3. Accessing Data Efficiently (indexing, join types)
  4. Processing Data Efficiently (optimizing sorts, aggregations, analytic functions)
  5. Controlling SQL Plan Execution (hints, adaptive stuff)
  6. Helping the Optimizer to help you (statistics strategy & CBO shortcomings)

Quick Start & Introduction

Deep Dive 1: Understanding SQL Plan Execution

Deep Dive 2: Accessing Data Efficiently

Deep Dive 3: Processing Data Efficiently

Deep Dive 4: Controlling SQL Execution Plans

Deep Dive 5: Helping the Cost Based Optimizer to Find Good Plans


More info

If you’d like to see detailed table of contents, thoughts and techniques of this training, download the full document containing more detailed flow and keywords used in this class here:

Note that some of the details are subject to change when I update the material with latest information.

Is this seminar for me? Will I be able to understand it?

I often get these questions. My answer is that if you like my blog and YouTube videos, you’ll like my training too! So, just browse around in the archives and see for yourself!

I have a YouTube channel too – you can check out some of my informal hacking sessions there:

If you have further questions about this class, just send us an email!


Go back to Tanel’s performance tuning & troubleshooting training overview page.


NB! Check out my 2018/2019 online training classes: Advanced Oracle SQL Tuning training, Advanced Oracle Troubleshooting training and the new Practical Linux Performance & Application Troubleshooting training!