Tony Hasler 2-hour video "Stabilizing Oracle SQL Performance with TSTATS"

Tony Hasler photo #2.JPG
Tony Hasler photo #2.JPG

Tony Hasler 2-hour video "Stabilizing Oracle SQL Performance with TSTATS"


What will  be covered in "Stabilizing Oracle SQL Performance with TSTATS?"

  • Predictable performance is always the goal
  • The CBO is always guessing on the best execution plan
  • Gathering statistics changes the data on which the CBO makes its guesses, so its guesses might change
  • Wolfgang Breitling's "Tuning by Cardinality Feedback"
  • The "Stale Statistics" misconception
  • A history of Adaptive Features and Execution Plan Stability
  • TSTATS goal - the same statement has the same execution plan every time on all prod and non-prod systems
  • Temporary tables and Dynamic Sampling
  • Partitioned tables and partition maintenance
  • Tuning SQL in a TSTATS environment
Add To Cart

Stabilizing Oracle SQL Performance with TSTATS course outline

Who is Tony Hasler?

What is TSTATS?


       Understanding the problem

       The strategy of the CBO team

       Understanding TSTATS

       Tuning SQL statements in a TSTATS environment

Part 1: Understanding The Problem

Problem statement

       We are after stable, predictable, performance.  Not the best average.

       Predictable performance is always the goal.

A hard truth about the CBO

       The CBO is always guessing what the best execution plan is for a SQL statement is

       Every time you gather statistics you change the data on which the CBO makes its guesses and so its guesses might change

The Dave Ensor Paradox

A brief diversion – Tuning By Cardinality Feedback (TCF)

       A method for tuning SQL statements invented by Wolfgang Breitling

       One of the biggest contributions to the science of SQL tuning that has ever been made

TCF in a nutshell

The TCF corollary

Why else would we need a change in execution plan?

How many execution plans do we need for a single SQL statement?

The Stale Statistics Misconception

The catch 22



Part 2: Oracle’s Strategy for the CBO

A History of Adaptive Features in Oracle Database

Adaptive Algorithms in LGWR

Comparing LGWR and CBO Adaptive Algorithms

A history of Oracle’s Execution Plan Stability

Why not use a supported plan stability feature?

Part 3: Understanding TSTATS

Health Warning


       The same statement should give the same execution plan all the time and on all test and production systems

The Four Problems for TSTATS

Time-based columns

How the CBO evaluates equality predicates

The Tapering Algorithm

Time-Based Columns and Range Predicates

Time-Based Column Statistics Wrap Up

Temporary Tables and Dynamic Sampling

Partitioned Tables and Partition Maintenance

Partition Maintenance Wrap Up

The TABLE operator

Understanding TSTATS Wrap Up

Part 4: Tuning in a TSTATS environment

Early in the lifecycle: Adjust Statistics

Later in the lifecycle: Use hints

Where to go for more information