Adaptive Optimization

Presented by: Kerry Osborne

Red Gate Webinar, Nov. 2013
whoami –
Never Worked for Oracle
Worked with Oracle DB Since 1982 (V2)
Working with Exadata since early 2010
Work for Enkitec (www.enkitec.com)
(Enkitec owns several Exadatan – V2/X2/X3)
(And Others BDA, Exalytics, ODA, etc…)
Worked on a couple of books
Hadoop Aficionado
Exadata Fan Boy

Blog: kerryosborne.oracle-guy.com
Twitter: @KerryOracleGuy

3
Top Secret Feature of BDA

4
What I Did Last Week

5
 What’s the Point?

Sometimes the Optimizer Makes Mistakes

It’s Often Pretty Easy to Spot the Mistakes
Why Not Let the DB Fix the Mistakes on the Fly?

6
 How Does the Optimizer Mess Up?

Cardinality – Misunderestimate

mostly …
and it’s pretty easy to recognize …

Estimated Rows ≠ Actual Rows

7
Cardinality – Misunderestimate

8
Cardinality – Misunderestimate

•  Wolfgang Breitling – Tuning By Cardinality Feedback

•  Randolf Geist – xplan_extended_display_cursor.sql
•  Adrian Billington – Xplan Wrapper
•  Kyle Halley – Display_Cursor Post

9
 Optimizer Evolution

•  Bind Variable Peeking

•  Dynamic Sampling
•  Adaptive Cursor Sharing
•  Cardinality Feedback
•  Tuning Advisor

•  Trend towards more dynamic plans

•  11g drawbacks
•  must run badly before it does anything
•  “fixes” – not persisted

10
11
 Adaptive Optimization

Improve Improve
Initial Adaptive Query Subsequent
Execution Optimization Executions

Adaptive Plans Adaptive Statistics

Join Parallel At At
Methods Distribution Compile Run Time
Methods Time

Cardinality Feedback

Dynamic Sampling

12
 But First – Some New Terms

•  Adaptive Optimization – any dynamic change to plan

•  Adaptive Plans – changed from default on 1st execution

•  Automatic Re-optimization – 2nd execution
•  Statistics Feedback = Cardinality Feedback
•  Dynamic Statistics = Dynamic Sampling
•  SQL Plan Directives = Persisted Dynamic Sampling (for now)
SPD = PDS

13
Adaptive Execution Plans
Join Methods

Adaptive Query
Optimization

Adaptive Plans Adaptive Statistics

Join Parallel At At
Methods Distribution Compile Run Time
Methods Time

14
 Adaptive Execution Plans
Join Methods

•  Optimizer Can Change Its Mind in Mid-Execution

•  2 Join Methods
•  Nested Loop
•  Hash Join

15
 Adaptive Optimization
Controls
optimizer_adaptive_features = false
- big switch - controls all adaptive stuff

optimizer_features_enable <= 12.1.0.1

- even bigger switch – please don’t use this one!

optimizer_adaptive_reporting_only = true

_optimizer_adaptive_plans=false
- individual control for adaptive plans

_optimizer_use_feedback=false
- individual control for cardinality feedback

optimizer_dynamic_sampling=0
- individual control for dynamic sampling

16
 Adaptive Execution Plans

Rows coming out via inner nested loop are buffered up to

a point. If row count exceeds threshold then switch to
hash join.

Alternative sub-plans are HASH JOIN

NESTED LOOPS
pre-computed
Sub-plans stored in the
cursor Statistics
Collector
Stats collector inserted
before join
Table scan Index Scan Table scan
Rows buffered until final
decision is made

17
 Adaptive Execution Plans

Statistics collector disabled after decision is made and

becomes a pass through operation.

Number of rows seen in

statistics collector exceeds
NESTED LOOPS HASH JOIN
threshold
Plan switches to hash join
Statistics collector disabled Statistics
Collector

Plan resolved on first

execution & remains the
Table scan Index Scan Table scan
same for subsequent
executions
Final Plan is a hash join

18
Adaptive Execution Plans
Finding Them (is easy)

19
Digression - OTHER_XML

20
Digression - OTHER_XML

21
 Adaptive Execution Plans
Displaying Default & Final Plans

Default – EXPLAIN PLAN + DBMS_XPLAN.DISPLAY

Default – Turn Off Feature - Standard DBMS_XPLAN.DISPLAY_CURSOR

Final - Standard DBMS_XPLAN.DISPLAY_CURSOR

Mixed - Use DBMS_XPLAN – with format “adaptive +report”

select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','adaptive +report'));

Produces plan which shows steps which were abandoned in final plan.
Abandoned steps are marked with a “-”

22
Adaptive Execution Plans
Displaying Default Plan

23
Adaptive Execution Plans
Displaying Final Plan

24
Adaptive Execution Plans
Displaying Adaptive Plans

Abandoned

25
Adaptive Execution Plans
Displaying Adaptive Plans (+report)

26
Adaptive Execution Plans
Displaying Adaptive Plans (+report)

27
 Adaptive Execution Plans
SPM Interaction

Baselines Behave Pretty Much As You’d Expect

Adaptive Plans Can Be Captured (The Final Plan)

Once SQL Using Baseline – No Longer Marked Adaptive

If Capture Is On – Unaccepted Plans Flagged as Adaptive

• Note: Baselines Actually Store Plans Now – Not Just Hints

•  But Only Used for Display Purposes

28
Adaptive Execution Plans
SPM Interaction

29
Adaptive Execution Plans
Parallel Distribution Methods

Adaptive Query
Optimization

Adaptive Plans Adaptive Statistics

Join Parallel At At
Methods Distribution Compile Run Time
Methods Time

30
 Adaptive Distribution Methods
  New adaptive distribution method HYBRID-HASH
  Statistic collectors inserted in front of PX process
  If actual number of rows less than threshold, switch from HASH to Broadcast
  Threshold number of total rows < 2x DOP

  Enabled by default

31
 Adaptive Distribution Methods
  Distribution

Statistics
method decision
based on
expected number p1 p5
of rows

  Cardinality based
p2 p6
distribution skew
is common p3 p7
  Can result in very
uneven p4 p8
distribution
Hybrid-
Hash
Broadcast
32
 Adaptive Statistics
Dynamic Statistics (Sampling)

Adaptive Query
Optimization

Adaptive Plans Adaptive Statistics

Join Parallel At At
Methods Distribution Compile Run Time
Methods Time

Dynamic Sampling

33
 Dynamic Statistics
  Dynamic statistics are used to compensate for missing,
stale, or incomplete statistics

  They can be used for table scans, index access, and joins

  Optimizer computes a time budget for generating dynamic

statistics based on query run-time

  Statistics are stored in memory and can be shared across

queries

  My Blog: Randolf Geist on Dynamic Sampling

34
 Dynamic Statistics

  optimizer_dynamic_sampling now goes to 11

35
 Adaptive Statistics
Dynamic Statistics (Sampling)

Adaptive Query
Optimization

Adaptive Plans Adaptive Statistics

Join Parallel At At
Methods Distribution Compile Run Time
Methods Time

Cardinality Feedback

36
 Adaptive Statistics
Re-optimization

  During execution optimizer estimates are compared to

execution statistics
  If statistics vary significantly then a new plan will be chosen for
subsequent executions based on execution statistics
  Re-optimization uses statistics gathered from previous
executions
  First introduced as Cardinality Feedback in 11.2

37
 Cardinality Feedback – 11g
  Statistics gathered about data volume and data type seen during execution

  If execution statistics vary significantly statement will be hard parsed on

the next execution using the execution statistics instead

  Statements are only monitored once if they don’t show significant differences
initially they won’t change in the future

  Only individual table cardinalities and group by estimates examined – not joins

  Information is stored in the cursor only and is lost if cursor ages out

38
 Adaptive Statistics
New Re-optimization

  Join statistics are also monitored

  Works with adaptive cursor sharing for statements with binds

  New Column in V$SQL - IS_REOPTIMIZABLE

  Information found at execution time is persisted as SQL Plan Directives

39
 SQL Plan Directives
“SPD are objects generated automatically by Oracle. For example, if Oracle detects
that the single table cardinality estimated made by the optimizer is different from the
actual number of rows returned when accessing the table, it will automatically create
a directive to perform dynamic statistics for the table. When any SQL statement
referencing the table is compiled, the optimizer will perform dynamic statistics for
the table to get a more accurate estimate.”

~ PL/SQL Packages Reference (12c Release 1)

SPD = PDS (Persisted Dynamic Sampling)

40
SQL Plan Directives

41
SQL Plan Directives

42
SQL Plan Directives

43
SQL Plan Directives

44
 SQL Plan Directives
States
NEW - 1st pass

MISSING_STATS - needs extended stats

(gathered automagically)

HAS_STATS – extended stats have now been gathered

(Intermediate State – new statements may still need SPD’s)

PERMANENT - extended stats have now been gathered

(but SPD still needed because of != predicates)

45
 SQL Plan Directives
Management

Managed with DBMS_SPD

– but not really much to manage
- can put them in a staging table and move them to another DB
- can flush any in memory to disk (flushed every 15m by default)
- can drop specific directives

46
Well, How Did We Get Here?

The New Optimizer

47
 Wrap Up

Even More Automagical Stuff

Name Changes Can be Confusing

- “statistics” happy in naming
- Dynamic Sampling = Dynamic Statistics
- Cardinality Feedback = Statistics Feedback

Ideas are Sound

- learn from execution statistics
- eliminate “must run bad first” behavior
- add persistence

It’s the Default – so you will see it! 

48
 Questions?
Contact Information : Kerry Osborne
kerry.osborne@enkitec.com
kerryosborne.oracle-guy.com
www.enkitec.com

49