Tanel Poder Training

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

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

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

Full table scans, direct path reads

Direct path read metrics

Direct path read decision

Analyzing query IO patterns and behavior

Partition pruning

Partition pruning info in DBMS_XPLAN

Subquery, bloom filter based partition pruning

Partition-wise join demo FAILED

Partition-wise join demo SUCCESSFUL

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

Optimizing large plans by refactoring SQL

Controlling SQL execution plans introduction

Controlling Subquery Unnesting

Unnesting complex subqueries experiment

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

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

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

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

Insert Index ITL contention

Freezing Plans with Baselines

SQL Patches Profiles SQL Translation

Demo Adjusting Plan With Cardinality Estimate

Questions Answers

Analyzing Plan Performance Changes

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