Using Adaptive Cursor Sharing (ACS) to produce
Download
Report
Transcript Using Adaptive Cursor Sharing (ACS) to produce
1
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Using Adaptive Cursor
Sharing (ACS) to produce
multiple Optimal Plans
Carlos Sierra
Consulting Technical Advisor
Carlos Sierra
Oracle Server Technologies(ST) Center of Expertise(CoE)
SQL Tuner handyman: developer, advisor, trainer, support
IT: Oracle(17), UNISYS(12), Ford(3), others(3)
Florida(17), Venezuela(3), Puerto Rico(6), Michigan(1), Mexico(X)
Tools: SQLTXPLAIN(SQLT), SQLHC, TRCANLZR(TRCA), others
Motto: Life is good!
3
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Adaptive Cursor Sharing (ACS)
Motivation
Mechanics
Test Case
Demo
Remarks
4
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
ACS Motivation
5
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
SQL Processing
Hard parsing is expensive!
Hard parse side effects
– CPU consumption
– Latch contention
Excessive hard parsing
– Affects concurrency
– Restricts scalability
Mitigating hard parsing
– Cursor sharing
6
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Implementing Cursor Sharing
Replacing literals with bind variables
7
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Before Bind Peeking
Before 9i CBO was blind to values passed
Predicate
– WHERE channel_id = :b1
Unknowns
– Is :b1 between low and high values of channel_id?
– Is :b1 a popular value of channel_id?
– Are there any rows with value :b1 for channel_id?
Penalty
– Possible suboptimal plans
8
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
With Bind Peeking
9i offers a partial solution
Predicate
– WHERE channel_id = :b1
Plan is determined by peeked values
– EXEC :b1 := 9;
Optimal plan for 1st execution
– CBO can use low/high and histograms on channel_id
Penalty
– Possible suboptimal plans for subsequent executions on skewed data
9
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
With Adaptive Cursor Sharing
11g improves cursor sharing
Some queries are ACS candidates
Sophisticated non-persistent mechanism
Selectivity of predicates determine plan
Multiple optimal plans for a query!
– If ACS is successfully applied
Penalty
– Marginal increase in CPU and memory overhead
10
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
ACS Mechanics
11
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
ACS high-level Overview
High level overview
If SQL with binds meets some requirements
– Flag cursor as bind sensitive
– Start monitoring data volume manipulated by cursor
If bind sensitive and data volume manipulated by cursor varies
significantly
– Flag cursor as bind aware
– Start generating multiple optimal plans for this query on next hard parse
If bind aware then use selectivity of predicates to decide on plan
12
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Bind Sensitive
Minimum requirements
SQL has explicit binds
– Or literals and cursor_sharing is “force”
Predicate: column + operand + bind_variable
– Equality operand “=“ and histogram on column
Ex: channel_id = :b1
– Non-equality operand (range) regardless of histogram on column
“>”, “>=“, “<“, ‘<=“, BETWEEN, LIKE
13
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Bind Aware
How to become bind aware?
Significant changes in data volume manipulated by cursor
– A few rows versus a few thousands of rows
– A few thousands of rows versus a few millions of rows
Specifying /*+ BIND_AWARE */ CBO Hint
– Bypasses the monitoring phase on data volume
14
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Plan Selection
Based on selectivity profile of predicates
Evaluate selectivity of predicates at soft parse
Compare to a non-persistent selectivity profile
If within ranges of a known profile then select associated plan
Else hard parse
– Compute and execute newly generated plan
– Create selectivity profile for new plan or update profile of existing plan
If ranges on selectivity profiles overlap then merge profiles
15
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
V$ dynamic views for ACS
ACS non-persistent performance views
V$SQL
– Shareable, bind sensitive and bind aware flags
V$SQL_CS_STATISTICS
– Data volume manipulated (rows processed)
V$SQL_CS_HISTOGRAM
– Record keeping of data volume per execution (small, medium, large)
V$SQL_CS_SELECTIVITY
– Predicates selectivity profiles
16
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
ACS Test Case
17
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Our Query with Literals
Guesstimate execution plan then verify it with demo 0
18
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Possible Access Paths?
19
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Optimal Execution Plan
20
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Our Query with Bind Variables
How many optimal execution plans can you foresee?
21
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Multiple Optimal Plans for one Query
Guesstimate optimal plan (access paths) for each query
22
Query
:b1
:b2
AP1
AP2
q1
9
33
N1
N2
q2
5
32
q3
2
999
q4
9
999
q5
2
33
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Multiple Optimal Plans for one Query
Guesstimate optimal plan (access paths) for each query
23
Query
:b1
:b2
AP1
AP2
q1
9
33
N1
N2
q2
5
32
N1
N2
q3
2
999
FTS
FTS
q4
9
999
N1
FTS
q5
2
33
FTS
N2
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Multiple Optimal Plans for one Query
Execute demos 1-5 and verify access paths
24
Query
:b1
:b2
AP1
AP2
q1
9
33
N1
N2
q2
5
32
N1
N2
q3
2
999
FTS
FTS
q4
9
999
N1
FTS
q5
2
33
FTS
N2
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Bind Sensitive: Rows Processed
Monitor v$sql_cs_statistics.rows_processed
Data volume manipulated
– Fuzzy representation
– S: few rows
– M: thousands or rows
– L: millions of rows
v$sql_cs_histogram
– Bucket(0): S
– Bucket(1): M
– Bucket(2): L
25
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Rows
Processed
Query
:b1
:b2
Optimal
q1
9
33
N1/N2
37,382
q2
5
32
N1/N2
2
q3
2
999 FTS/FTS
8,021,324
q4
9
999
N1/FTS
6,233,815
q5
2
33
FTS/N2
1,825,131
ACS Demo
26
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Demo 6: When Cursor becomes Bind Aware?
Obtain rows processed from demo 1-5 then guesstimate aware flag
Rows
Processed
Query
:b1
:b2
Optimal
q1
9
33
N1/N2
37,382
q2
5
32
N1/N2
2
q3
2
999 FTS/FTS
8,021,324
q4
9
999
N1/FTS
6,233,815
q5
2
33
FTS/N2
1,825,131
27
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Bucket
Aware
Child
Actual
Demo 6: When Cursor becomes Bind Aware?
Obtain rows processed from demo 1-5 then guesstimate aware flag
Rows
Processed
Query
:b1
:b2
Optimal
q1
9
33
N1/N2
37,382
1
q2
5
32
N1/N2
2
0
q3
2
999 FTS/FTS
8,021,324
2
q4
9
999
N1/FTS
6,233,815
2
q5
2
33
FTS/N2
1,825,131
2
28
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Bucket
Aware
Child
Actual
Demo 6: When Cursor becomes Bind Aware?
Obtain rows processed from demo 1-5 then guesstimate aware flag
Query
:b1
:b2
Optimal
q1
9
33
N1/N2
q2
5
32
N1/N2
q3
2
q4
q5
29
Rows
Processed
Bucket
Aware
37,382
1
N
2
0
N
999 FTS/FTS
8,021,324
2
Y
9
999
N1/FTS
6,233,815
2
Y
2
33
FTS/N2
1,825,131
2
Y
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Child
Actual
Demo 6: When Cursor becomes Bind Aware?
Obtain rows processed from demo 1-5 then guesstimate aware flag
Query
:b1
:b2
Optimal
q1
9
33
N1/N2
q2
5
32
N1/N2
q3
2
q4
q5
30
Rows
Processed
Bucket
Aware
Child
Actual
37,382
1
N
0
N1/N2
2
0
N
0
N1/N2
999 FTS/FTS
8,021,324
2
Y
1
FTS/FTS
9
999
N1/FTS
6,233,815
2
Y
2
N1/FTS
2
33
FTS/N2
1,825,131
2
Y
3
FTS/N2
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Demo 6: When Cursor becomes Bind Aware?
Obtain rows processed from demo 1-5 then guesstimate aware flag
Query
:b1
:b2
Optimal
q1
9
33
N1/N2
q2
5
32
N1/N2
q3
2
q4
q5
31
Rows
Processed
Bucket
Aware
Child
Actual
37,382
1
N
0
N1/N2
2
0
N
0
N1/N2
999 FTS/FTS
8,021,324
2
Y
1
FTS/FTS
9
999
N1/FTS
6,233,815
2
Y
2
N1/FTS
2
33
FTS/N2
1,825,131
2
Y
3
FTS/N2
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Demo 7: When Cursor becomes Bind Aware?
Compute bucket and guesstimate aware flag and actual plan
Rows
Processed
Query
:b1
:b2
Optimal
q5
2
33
FTS/N2
1,825,131
q4
9
999
N1/FTS
6,233,815
q3
2
999 FTS/FTS
8,021,324
q2
5
32
N1/N2
2
q1
9
33
N1/N2
37,382
32
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Bucket
Aware
Child
Actual
Demo 7: When Cursor becomes Bind Aware?
Compute bucket and guesstimate aware flag and actual plan
Rows
Processed
Query
:b1
:b2
Optimal
q5
2
33
FTS/N2
1,825,131
2
q4
9
999
N1/FTS
6,233,815
2
q3
2
999 FTS/FTS
8,021,324
2
q2
5
32
N1/N2
2
0
q1
9
33
N1/N2
37,382
1
33
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Bucket
Aware
Child
Actual
Demo 7: When Cursor becomes Bind Aware?
Compute bucket and guesstimate aware flag and actual plan
Query
:b1
:b2
Optimal
q5
2
33
FTS/N2
q4
9
999
q3
Rows
Processed
Bucket
Aware
1,825,131
2
N
N1/FTS
6,233,815
2
N
2
999 FTS/FTS
8,021,324
2
N
q2
5
32
N1/N2
2
0
N
q1
9
33
N1/N2
37,382
1
Y
34
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Child
Actual
Demo 7: When Cursor becomes Bind Aware?
Compute bucket and guesstimate aware flag and actual plan
Query
:b1
:b2
Optimal
q5
2
33
FTS/N2
q4
9
999
q3
Rows
Processed
Bucket
Aware
Child
Actual
1,825,131
2
N
0
FTS/N2
N1/FTS
6,233,815
2
N
0
FTS/N2
2
999 FTS/FTS
8,021,324
2
N
0
FTS/N2
q2
5
32
N1/N2
2
0
N
0
FTS/N2
q1
9
33
N1/N2
37,382
1
Y
1
N1/N2
35
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Demo 7: When Cursor becomes Bind Aware?
Compute bucket and guesstimate aware flag and actual plan
Query
:b1
:b2
Optimal
q5
2
33
FTS/N2
q4
9
999
q3
Rows
Processed
Bucket
Aware
Child
Actual
1,825,131
2
N
0
FTS/N2
N1/FTS
6,233,815
2
N
0
FTS/N2
2
999 FTS/FTS
8,021,324
2
N
0
FTS/N2
q2
5
32
N1/N2
2
0
N
0
FTS/N2
q1
9
33
N1/N2
37,382
1
Y
1
N1/N2
36
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Demo 8: When Cursor becomes Bind Aware?
Guesstimate aware flag and actual plan
Rows
Processed
Query
:b1
:b2
Optimal
q5
2
33
FTS/N2
1,825,131
2
q4
9
999
N1/FTS
6,233,815
2
q3
2
999 FTS/FTS
8,021,324
2
q1
9
33
N1/N2
37,382
1
q2
5
32
N1/N2
2
0
37
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Bucket
Aware
Child
Actual
Demo 8: When Cursor becomes Bind Aware?
Guesstimate aware flag and actual plan
Query
:b1
:b2
Optimal
q5
2
33
FTS/N2
q4
9
999
q3
Rows
Processed
Bucket
Aware
1,825,131
2
N
N1/FTS
6,233,815
2
N
2
999 FTS/FTS
8,021,324
2
N
q1
9
33
N1/N2
37,382
1
N
q2
5
32
N1/N2
2
0
N
38
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Child
Actual
Demo 8: When Cursor becomes Bind Aware?
Guesstimate aware flag and actual plan
Query
:b1
:b2
Optimal
q5
2
33
FTS/N2
q4
9
999
q3
Rows
Processed
Bucket
Aware
Child
Actual
1,825,131
2
N
0
FTS/N2
N1/FTS
6,233,815
2
N
0
FTS/N2
2
999 FTS/FTS
8,021,324
2
N
0
FTS/N2
q1
9
33
N1/N2
37,382
1
N
0
FTS/N2
q2
5
32
N1/N2
2
0
N
0
FTS/N2
39
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Demo 8: When Cursor becomes Bind Aware?
Guesstimate aware flag and actual plan
Query
:b1
:b2
Optimal
q5
2
33
FTS/N2
q4
9
999
q3
Rows
Processed
Bucket
Aware
Child
Actual
1,825,131
2
N
0
FTS/N2
N1/FTS
6,233,815
2
N
0
FTS/N2
2
999 FTS/FTS
8,021,324
2
N
0
FTS/N2
q1
9
33
N1/N2
37,382
1
N
0
FTS/N2
q2
5
32
N1/N2
2
0
N
0
FTS/N2
40
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Closing Remarks
41
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Remarks on Bind Sensitivity
Based on experimental observation
Monitor V$SQL_CS_STATISTICS.rows_processed
– If small number of rows then
V$SQL_CS_HISTOGRAM.bucket_id(0)++
– If medium number of rows then
V$SQL_CS_HISTOGRAM.bucket_id(1)++
– If large number of rows then
V$SQL_CS_HISTOGRAM.bucket_id(2)++
42
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Remarks on Bind Aware
Based on experimental observation
Some cases where cursor may become bind aware
– bucket_id(0) = bucket_id(1) > 0
– bucket_id(1) = bucket_id(2) > 0
– bucket_id(0) > 0 and bucket_id(2) > 0
– /*+ BIND_AWARE */ CBO Hint
43
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Understanding Selectivity Profile
From demo 6
44
Query
:b1
:b2
Child
q3
2
999
1
q4
9
999
2
q5
2
33
3
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Understanding Selectivity Profile
From demo 6
45
Query
:b1
:b2
Child
q3
2
999
1
q4
9
999
2
q5
2
33
3
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Understanding Selectivity Profile
From demo 6
46
Query
:b1
:b2
Child
q3
2
999
1
q4
9
999
2
q5
2
33
3
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Conclusions
ACS can produce multiple optimal plans for one query
ACS only applies to a subset of queries with binds
ACS requires a ramp-up process (few executions)
In some cases cursor may fail to become bind aware
To force a cursor become bind aware use CBO Hint
ACS is not persistent
ACS works well with SQL Plan Management
47
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
Give Away
Script sqlt/utl/coe_gen_sql_patch.sql (MOS 215187.1)
Creates a SQL Patch for one SQL_ID
Turns “on” EVENT 10053 for SQL_ID
Hints on SQL Patch
– GATHER_PLAN_STATISTICS
– MONITOR
– BIND_AWARE
Instructions how to drop SQL Patch and turn “off” EVENT 10053
48
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
References and Contact Info
Oracle Optimizer Blog
https://blogs.oracle.com/optimizer/
– Insight into the workings of the Optimizer
[email protected]
http://carlos-sierra.net
@csierra_usa
49
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.
50
Copyright © 2013, Oracle and/or its affiliates. All rights reserved.