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
- As I am doing all my training courses using a new format now (self paced video learning + regular Q&A follow-up sessions), you can learn at any time, anywhere.
- Check out the new learning platform out here:
Objectives
- Be able to quickly diagnose and fix SQL performance problems
- Gain deep understanding of SQL plan execution internals
- Know what to change to improve SQL performance without trial and error
- Be able to control SQL execution plans and guarantee their stability
- Be able to systematically index tables for data access performance
- Help CBO to find good plans, to keep the manual tuning to minimum
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
- This is not a beginner “How to write SQL” class, although we will talk about rewriting SQL for optimization purposes
- This is not a PL/SQL writing or PL/SQL code optimization class, although we will cover the benefits of bind variables and array processing that include PL/SQL examples too
- This class does not go deep into Exadata-only features, but everything taught here applies to Exadata too (including full table scans and partition pruning topics)
- This is not a database troubleshooting & instance tuning class, visit https://tanelpoder.com/seminar for other classes
Recommended Prerequisites
- You’ll need to be familiar with Oracle Database concepts and SQL development
- You’ll need to generally know what the following Oracle keywords mean: shared pool, SQL parsing, cursor, buffer cache, table partition, bind variable, logical & physical IO, sort area & hash area, PGA
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:
- Part 1 - understanding physical SQL execution & achieving efficient data access
- Part 2 - optimizer topics, hinting, plan stability and helping CBO
The order of deep dives in the class is following:
- Intro: Quick start & what to check when SQL performance is bad
- Understanding SQL Plan Execution (reading plans of any complexity)
- Accessing Data Efficiently (indexing, join types)
- Processing Data Efficiently (optimizing sorts, aggregations, analytic functions)
- Controlling SQL Plan Execution (hints, adaptive stuff)
- Helping the Optimizer to help you (statistics strategy & CBO shortcomings)
Quick Start & Introduction
- Getting execution plans and related performance data
- Identifying where in the plan most of the work is done
- Identifying inefficiencies & Cost Based Optimizer mistakes at a glance
Deep Dive 1: Understanding SQL Plan Execution
- Deep understanding of how exactly Oracle executes SQL statements, SELECTs & DMLs
- Understand the fundamental factors affecting query execution performance
- How to use a systematic approach for tuning SQL of any complexity
Deep Dive 2: Accessing Data Efficiently
- Systematic indexing – which index type to use, when and how
- How to decide which columns and in which order to index without trial and error
- Understanding index-based data retrieval overhead
- Understanding index maintenance overhead
- When does a full table scan perform better than indexes?
- Static & dynamic partition pruning
Deep Dive 3: Processing Data Efficiently
- Optimizing table joining performance
- Monitoring SQL work-area (PGA) usage
- Keeping SQL work-areas small
- Optimizing GROUP BY and sorting performance
- Approximate data processing
- Optimizing Analytic functions (window functions)
Deep Dive 4: Controlling SQL Execution Plans
- How to make a SQL execution plan do exactly what you want
- How to achieve SQL plan and performance stability
- Controlling Adaptive features and dynamic sampling
- Understanding why SQL execution plans unexpectedly change and what to do about it
- Understanding why SQL performance can suddenly drop, when “nothing” has changed – and what to about it
- How to optimize SQL when you cannot change the application code?
Deep Dive 5: Helping the Cost Based Optimizer to Find Good Plans
- Understand what exactly the Optimizer Cost is, which input data is used for cost calculations and where does this number matter
- How to configure Oracle, statistics and CBO to keep the need for manual SQL tuning to the minimum
- How to troubleshoot CBO cost and row count misestimation
- Dealing with optimizer bugs
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.
