Advanced Oracle SQL Tuning 2.0

by Tanel Põder

This seminar 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.

You will learn the full range of techniques for SQL tuning starting from optimizer stats placement strategy all the way to advanced topics such as comprehensive SQL hinting and CBO’s estimated row count adjustments.

We start from the low level fundamentals, explaining how exactly Oracle SQL plan execution works, what are the main factors affecting query performance and how to systematically diagnose and fix SQL performance problems.

Some SQL performance problems cannot be cured by just an easy change to application code or optimizer stats, so a significant part of this seminar also concentrates on good database design and indexing practices.

Finally, this class is not about a limited list of "tips and tricks", instead it will give you the knowledge and tools for systematic tuning and troubleshooting SQL of any complexity.

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

Objectives

Gain deep understanding of SQL plan execution flow
Know what and how to change to get efficient execution plans
Be able to manually control SQL execution plans and guarantee their stability
Be able to systematically index tables for access performance
Help CBO to find good plans, to keep the manual tuning to minimum

Non-Objectives

This is not a beginners "How to write SQL" class (we will talk about rewriting SQL for optimization purposes)
This is not a database/instance tuning class

Course Topics

1. Understanding SQL Plan Execution

Deep understanding of how exactly Oracle executes SQL statements, both SELECT and DML statements
Understand the fundamental factors affecting query execution performance
Monitoring SQL execution activity and where the response time is spent
Using ASH, SQL Monitoring reports, V$SQL_PLAN_STATISTICS and Snapper for measuring SQL resource usage
Understanding common query transformations
Finding where the optimizer has misestimated the number of rows to process (cardinality)
How to use a systematic approach for tuning SQL of any complexity

2. Controlling SQL Execution Plans

How to make a SQL execution plan do what you want
How to achieve SQL performance and plan stability
Understand why SQL execution plans unexpectedly change even when nothing else has apparently changed – and what to do about it
Understand why can the SQL performance suddenly drop, when nothing, even the execution plan itself has not changed – and what to do about it
How to optimize SQL when you cannot change the code?

3. 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
Identifying best candidates for index-organized tables

4. Helping the Cost Based Optimizer to Find Good Plans

Understand what exactly is the Optimizer Cost, 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
Advanced troubleshooting of CBO Cost and row count misestimation and bugs

Upcoming Sessions

There are no sessions scheduled at this time. Contact us to schedule a private or custom version of this course.

Duration

Online: 4 days

Classroom training typically runs from 9:00 AM until 4:00 PM daily. Online training typically runs from 12:00 PM ET until 4:00 PM ET daily.

Please note that actual times may vary.

Prerequisites

You should already have a general understanding of Oracle architecture (what is SGA, PGA, logical IO, Physical IO) and have at least tried to do some SQL tuning - or have attended Enkitec's Pro SQL Tuning foundation class. This seminar is for Intermediate to Advanced Senior DBAs, senior developers, database designers.