Course curriculum

  1. 1
    • Getting started with the learning platform

    • Download Slides & Scripts

    • Personal Lab Setup Instructions (optional)

  2. 2
    • Intoduction setting up environment

    • Introduction agenda, training philosophy

    • Demo optimizer inputs, complexity overview

    • SQL execution engine capabilities, plan shape limitations

    • Getting execution plans

    • Explain plan limitations

    • Better tools for getting the correct plan

    • Reading plans and metrics using SQL Monitoring reports

    • SQL Monitoring Duration vs DB Time, application think time

    • Getting current bind variable values for long-running SQL

    • Question child cursor metrics

    • Ashtop demo

    • ASH SQL plan line level breakdown

    • Demo asqlmon script

    • Profiling plans with DBMS_XPLAN and GATHER_PLAN_STATISTICS

    • Other tools and SQL Trace comments

    • xb.sql - eXplain Better script

    • Execution plan tools summary

    • Question getting plans of SQL executed in past

    • Question SQL_trace enables GATHER_PLAN_STATISTICS

  3. 3
    • Understanding Execution Plan Structure

    • Row source metrics E-Rows A-Rows Starts

    • Query blocks introduction

    • Filter Access predicates

    • Comparing plan hash values

    • Query blocks object aliases

    • Outline data OTHER_XML

    • Peeked binds

    • Column projections

    • Question filter access predicates in AWR

    • Plan operator types and row sending flow

    • Demo cascading row source

    • Demo blocking row source

    • Measuring UGA PGA workarea sizes

    • Joins introduction

    • Nested loops joins

    • Hash joins

    • Outer joins

    • Merge joins

    • Hash joins optimal vs multipass execution

    • Join types summary

    • Questions demos

    • Temp table transformation

    • Inlist Iterator

    • Nested subqueries intro

    • FILTER row source

    • Subquery unnesting transformations

    • INTERNAL_FUNCTION

    • Demo hash join vs nested looping for the same SQL

    • Question: Listing help about TPT scripts

  4. 4
    • Accessing data efficiently, access path metrics

    • Index range scan efficiency

    • Index tree structure and shape

    • Measuring the index slice

    • Overview of btree index access paths

    • Indexes as skinny tables

    • Index skip scanning overview

    • Index column order range scan efficiency

    • Clustering factor, accessing tables via indexes

    • Other schema considerations, constraints

    • Systematic indexing

    • Indexing demo 1

    • Indexing demo 2

    • Indexing demo 2 rerun going deeper

  5. 5
    • Full table scans, direct path reads

    • Direct path read metrics

    • Direct path read decision

    • Analyzing query IO patterns and behavior

  6. 6
    • Partition pruning

    • Partition pruning info in DBMS_XPLAN

    • Subquery, bloom filter based partition pruning

    • Partition-wise join demo FAILED

    • Partition-wise join demo SUCCESSFUL

  7. 7
    • Large hash joins PGA temp usage

    • Parallel hash join example

    • Hash join buffered example

    • PQ_DISTRIBUTE controlling PX row distribution

    • Partition-wise joins improving PX plan example

    • Other ways for reducing PGA memory usage

  8. 8
    • Optimizing large plans by refactoring SQL

  9. 9
    • Controlling SQL execution plans introduction

    • Controlling Subquery Unnesting

    • Unnesting complex subqueries experiment

  10. 10
    • The main properties of an execution plan

    • Controlling execution plans with hints

    • Troubleshooting why hints aren't used

    • Disabling old hardcoded hints

    • Controlling join orders

    • Controlling join methods demos

  11. 11
    • Giving optimizer the freedom to optimize

    • Picking the right join order

    • Picking the join order SH schema example

    • Manually optimizing the SH query join order

    • Plan tree structure with SWAP_JOIN_INPUTS introduction

    • Controlling transformations

    • OR-Expansion transformation example

  12. 12
    • Helping CBO Introduction

    • Battling Cardinality Misestimates

    • The main properties of an execution plan

    • Good join orders, estimating cardinalities

    • Easy detection of cardinality misestimates xb.sql

    • Finding the origin of cardinality misestimate, helping optimizer

    • Optimizer cardinality feedback

    • Dynamic sampling, SQL plan directives

    • SQL profiles OPT_ESTIMATE

    • Cost Based Optimizer inputs

    • Adaptive cursor sharing

    • Reasons for cardinality misestimate, CBO behavior changes

    • SQL performance analysis approach summary

    • CBO inputs object base stats

    • CBO inputs column histograms

    • Creating SQL plan baselines from a manually optimized SQL plan

    • CBO cardinality misestimate examples

  13. 13
    • CBO cardinality misestimate out of stats bounds value

    • CBO cardinality misestimate correlated columns

    • Other CBO inputs physical design, datatypes, constraints

    • Troubleshooting optimizer bug CBO behavior changes demo

  14. 14
    • Insert Index ITL contention

  15. 15
    • Freezing Plans with Baselines

    • SQL Patches Profiles SQL Translation

    • Demo Adjusting Plan With Cardinality Estimate

    • Questions Answers

    • Analyzing Plan Performance Changes

  16. 16
    • What are the optimizer days?

    • Optimizer Day 2021 - Part 1

    • Optimizer Day 2021 - Part 2

    • 2019 - What-Is-Optimizer-Cost

    • 2019 - System-Statistics-CPU-Costing

    • 2019 - Question-Noworkload-System-Stats

    • 2019 - Optimizer-Cost-Formula-General

    • 2019 - Index-Scan-Table-Scan-Costing

    • 2019 - CBO-Terminology

    • 2019 - Before-Reading-CBO-Traces

    • 2019 - Why-Is-CBO-Not-Using-My-Index

    • 2019 - Reading-CBO-Trace-Introduction

    • 2019 - CBO-Trace-Access-Path-Analysis

    • 2019 - Column-Groups-Correlation

    • 2019 - Join-Orders-And-Followup-Plan

How does this work?

Attending & Logistics

When you sign up, you will get access to the following (for the next 12 months):

  1. Online streamable videos
  2. Downloadable videos for personal "forever-use"
  3. Training slides, scripts and other material


The "videos only" products do not include follow up Q&A sessions and discussion forum/chat access.


More details, terms & conditions and logistics here:


Thanks and See You Soon!