Recent site activity

Copyright

Advanced Oracle SQL Tuning Seminar


Seminar overview

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.

Seminar Duration and Details

  • Seminar duration: 8 x 4 hours (sepatated into two parts, on 2 separate weeks, 4 half-days each week)
  • Venue: Online, virtual seminar at your desk (no travel!)
  • Audience: Senior DBAs, senior developers, database designers
  • Skill level: Intermediate to Advanced
  • Speaker: Tanel Poder
  • Price:
    • 2600 USD per attendee (8 sessions)

Check the next seminar dates and sign up here:

Objectives

  1. Gain deep understanding of SQL plan execution flow
  2. Know what and how to change to get efficient execution plans
  3. Be able to manually control SQL execution plans and guarantee their stability
  4. Be able to systematically index tables for access performance
  5. 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 or optimizer problem out there, instead you will be able to systematically work out the problem root causes yourself!

Non-objectives

  1. This is not a beginners “How to write SQL” class (although we will talk about rewriting SQL for optimization purposes)
  2. This is not a database/instance tuning class (Visit the seminar schedule page for other classes)

Pre-requisites

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.

You will learn the following...

Week 1

(4 x 0.5 days on 1st week)

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

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?

Week 2

(4 x 0.5 days on 2nd week)

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

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