Adaptive Optimization
Presented by: Kerry Osborne
Hotsos Symposium 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
This is my 11th Presentation at Hotsos
Blog: kerryosborne.oracle-guy.com
Twitter: @KerryOracleGuy
Top Secret Feature of BDA
What I Did Last Week
Whats the Point?
Sometimes the Optimizer Makes Mistakes
Its Often Pretty Easy to Spot the Mistakes
Why Not Let the DB Fix the Mistakes on the Fly?
How Does the Optimizer Mess Up?
Cardinality Misunderestimate
mostly
and its pretty easy to recognize
Estimated Rows Actual Rows
Cardinality Misunderestimate
Cardinality Misunderestimate
Wolfgang Breitling Tuning By Cardinality Feedback
Randolf Geist xplan_extended_display_cursor.sql
Adrian Billington Xplan Wrapper
Kyle Halley Display_Cursor Post
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
Adaptive Query
Optimization
Adaptive Plans
Join
Methods
Parallel
Distribution
Methods
Adaptive Statistics
At
Compile
Time
At
Run 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
Join
Methods
Parallel
Distribution
Methods
Adaptive Statistics
At
Compile
Time
At
Run 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 dont 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
pre-computed
Sub-plans stored in the
cursor
Stats collector inserted
before join
Rows buffered until final
decision is made
NESTED LOOPS
HASH JOIN
Statistics
Collector
Table scan
Index Scan
Table scan
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
threshold
NESTED LOOPS
HASH JOIN
Plan switches to hash join
Statistics collector disabled
Plan resolved on first
execution & remains the
same for subsequent
executions
Statistics
Collector
Table scan
Index Scan
Table scan
Final Plan is a hash join
18
Adaptive Execution Plans
Finding Them (is easy)
19
Digression - OTHER_XML
20
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 -
21
Adaptive Execution Plans
Displaying Default Plan
22
Adaptive Execution Plans
Displaying Final Plan
23
Adaptive Execution Plans
Displaying Adaptive Plans
Abandoned
24
Adaptive Execution Plans
Displaying Adaptive Plans (+report)
25
Adaptive Execution Plans
Displaying Adaptive Plans (+report)
26
Adaptive Execution Plans
SPM Interaction
Baselines Behave Pretty Much As Youd 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: Do Baselines Actually Store Plans Now Not Just Hints ?
Maria Says Yes!
Seems to be true!
27
Adaptive Execution Plans
SPM Interaction
28
Adaptive Execution Plans
Parallel Distribution Methods
Adaptive Query
Optimization
Adaptive Plans
Join
Methods
Parallel
Distribution
Methods
Adaptive Statistics
At
Compile
Time
At
Run Time
29
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
30
Distribution
method decision
based on
expected number
of rows
Cardinality based
distribution skew
is common
Can result in very
uneven
distribution
p1
Statistics
Adaptive Distribution Methods
p5
p2
p6
p3
p7
p4
p8
HybridHash
Broadcast
31
Adaptive Statistics
Dynamic Statistics (Sampling)
Adaptive Query
Optimization
Adaptive Plans
Join
Methods
Parallel
Distribution
Methods
Adaptive Statistics
At
Compile
Time
At
Run Time
Dynamic Sampling
32
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
33
Adaptive Statistics
Dynamic Statistics (Sampling)
Adaptive Query
Optimization
Adaptive Plans
Join
Methods
Parallel
Distribution
Methods
Adaptive Statistics
At
Compile
Time
At
Run Time
Cardinality Feedback
34
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
35
Adaptive Statistics
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 dont show significant differences
initially they wont 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
36
Adaptive Statistics
New Re-optimization
Join statistics are also monitored
Works with adaptive cursor sharing for statement with binds
New Column in V$SQL IS_REOPTIMIZABLE
Information found at execution time is persisted as SQL Plan Directives
37
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)
38
SQL Plan Directives
39
SQL Plan Directives
40
SQL Plan Directives
41
SQL Plan Directives
42
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 SPDs)
PERMANENT - extended stats have now been gathered
(but SPD still needed because of != predicates)
43
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
44
Well, How Did We Get Here?
The New Optimizer
45
Wrap Up
Even More Automagical Stuff
Name Changes Can be Confusing
- statistics happy in naming
- Dynamic Sampling = Dynamic Statistics
- Statistics Feedback = Cardinality Feedback
Ideas are Sound
- learn from execution statistics
- eliminate must run bad first behavior
- add persistence
Its the Default so you will see it
46
Questions?
Contact Information
: Kerry Osborne
kerry.osborne@enkitec.com
kerryosborne.oracle-guy.com
www.enkitec.com
47
