using adaptive cursor sharing (acs) to produce multiple optimal plans.pptx.pdf

8/14/2019 Using Adaptive Cursor Sharing (ACS) to produce multiple Optimal Plans.pptx.pdf

1/51

Using Adaptive Cursor Sharing (ACS) toproduce multiple Optimal Plans

Carlos Sierra


2/51

Carlos Sierra

! Enkitec Consultant! Oracle Performance! SQL Tuning! PL/SQL


3/51

Adaptive Cursor Sharing (ACS)

! Motivation! Mechanics! Test Case! Demo! Remarks

3


4/51

ACS Motivation


5/51

SQL Processing

! Hard parse side effects CPU consumption Latch contention

! Excessive hard parsing Affects concurrency Restricts scalability

! Mitigating hard parsing Cursor sharing

Hard parsing is expensive!

5


6/51

Implementing Cursor SharingReplacing literals with bind variables

6


7/51

Before Bind Peeking

! 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

Before 9i CBO was blind to values passed

7


8/51

With Bind Peeking

! Predicate WHERE channel_id = :b1

! Plan is determined by peeked values EXEC :b1 := 9;

! Optimal plan for 1stexecution CBO can use low/high and histograms on channel_id

! Penalty Possible suboptimal plans for subsequent executions on skewed data

9i offers a partial solution

8


9/51

Real-life Problem with Bind PeekingPeople Soft Payroll Application

9

! WHERE employee BETWEEN :b1 AND :b2! Payroll for one employee

:b1 = 123456 :b2 = 123456

! Payroll for one company :b1 = 000001 :b2 = 999999

! Doing payroll for an employee first then entire company


10/51

With Adaptive 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

11g improves cursor sharing

10


11/51

ACS Mechanics


12/51

ACS high-level OverviewHigh 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 variessignificantly

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 plan12


13/51

Bind Sensitive

! 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

! >, >=,


14/51

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

How to become bind aware?

14


15/51

Plan Selection

! 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

Based on selectivity profile of predicates

15


16/51

V$ dynamic views for ACS

! 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

ACS non-persistent performance views

16


17/51

ACS Test Case


18/51

Our Query with LiteralsGuesstimate execution plan then verify it with demo 0

18


19/51

Possible Access Paths?

19


20/51

Optimal Execution Plan

20


21/51

Our Query with Bind VariablesHow many optimal execution plans can you foresee?

21


22/51

Multiple Optimal Plans for one QueryGuesstimate optimal plan (access paths) for each query

Query :b1 :b2 AP1 AP2

q1 9 33 N1 N2q2 5 32

q3 2 999

q4 9 999

q5 2 33

22


23/51

Multiple Optimal Plans for one QueryGuesstimate optimal plan (access paths) for each query


q1 9 33 N1 N2q2 5 32 N1 N2

q3 2 999 FTS FTS

q4 9 999 N1 FTS

q5 2 33 FTS N2

23


24/51

Multiple Optimal Plans for one QueryExecute demos 1-5 and verify access paths


q1 9 33 N1 N2q2 5 32 N1 N2

q3 2 999 FTS FTS

q4 9 999 N1 FTS

q5 2 33 FTS N2

24


25/51

Bind Sensitive: 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

Monitor v$sql_cs_statistics.rows_processed

Query :b1 :b2 Optimal RowsProcessed

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

25


26/51

ACS Demo


27/51

Demo 6: When Cursor becomes Bind Aware?

Query :b1 :b2 Optimal

RowsProcessed Bucket Aware Child Actual

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

Obtain rows processed from demo 1-5 then guesstimate aware flag

27


28/51




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


29/51




q1 9 33 N1/N2 37,382 1 N

q2 5 32 N1/N2 2 0 N

q3 2 999 FTS/FTS 8,021,324 2 Y

q4 9 999 N1/FTS 6,233,815 2 Y

q5 2 33 FTS/N2 1,825,131 2 Y


29


30/51




q1 9 33 N1/N2 37,382 1 N 0 N1/N2

q2 5 32 N1/N2 2 0 N 0 N1/N2

q3 2 999 FTS/FTS 8,021,324 2 Y 1 FTS/FTS

q4 9 999 N1/FTS 6,233,815 2 Y 2 N1/FTS

q5 2 33 FTS/N2 1,825,131 2 Y 3 FTS/N2


30


31/51




q1 9 33 N1/N2 37,382 1 N 0 N1/N2

q2 5 32 N1/N2 2 0 N 0 N1/N2

q3 2 999 FTS/FTS 8,021,324 2 Y 1 FTS/FTS

q4 9 999 N1/FTS 6,233,815 2 Y 2 N1/FTS

q5 2 33 FTS/N2 1,825,131 2 Y 3 FTS/N2


31


32/51




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

Compute bucket and guesstimate aware flag and actual plan

32


33/51




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


34/51




q5 2 33 FTS/N2 1,825,131 2 N

q4 9 999 N1/FTS 6,233,815 2 N

q3 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


35/51




q5 2 33 FTS/N2 1,825,131 2 N 0 FTS/N2

q4 9 999 N1/FTS 6,233,815 2 N 0 FTS/N2

q3 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


36/51




q5 2 33 FTS/N2 1,825,131 2 N 0 FTS/N2

q4 9 999 N1/FTS 6,233,815 2 N 0 FTS/N2

q3 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


37/51




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

Guesstimate aware flag and actual plan

37


38/51




q5 2 33 FTS/N2 1,825,131 2 N

q4 9 999 N1/FTS 6,233,815 2 N

q3 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


39/51




q5 2 33 FTS/N2 1,825,131 2 N 0 FTS/N2

q4 9 999 N1/FTS 6,233,815 2 N 0 FTS/N2

q3 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


40/51




q5 2 33 FTS/N2 1,825,131 2 N 0 FTS/N2

q4 9 999 N1/FTS 6,233,815 2 N 0 FTS/N2

q3 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


41/51

Real-life Problem with ACSPeople Soft Payroll Application

41

! WHERE employee BETWEEN :b1 AND :b2! Payroll for one employee

:b1 = 123456 :b2 = 123456

! Payroll for one company :b1 = 000001 :b2 = 999999

! Doing payroll for a few employees first then entire company


42/51

Closing Remarks


43/51

Understanding Selectivity ProfileFrom demo 6

Query :b1 :b2 Child

q3 2 999 1

q4 9 999 2

q5 2 33 3

43


44/51


Query :b1 :b2 Child

q3 2 999 1

q4 9 999 2

q5 2 33 3

44


45/51


Query :b1 :b2 Child

q3 2 999 1

q4 9 999 2

q5 2 33 3

45


46/51

Remarks on Bind Sensitivity

! 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)++

Based on experimental observation

46


47/51

Remarks on Bind Aware

! 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

! Or use /*+ BIND_AWARE */ CBO Hint What if we cannot modify code?

Based on experimental observation

47


48/51

Conclusions

!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

ACS can produce multiple optimal plans for one query

48


49/51

Give Away

! 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

! Consider using and customizing this free script

Script sqlt/utl/coe_gen_sql_patch.sql (MOS 215187.1)

49


50/51

References and Contact Info

! https://blogs.oracle.com/optimizer/ Insight into the workings of the Optimizer

Oracle Optimizer Blog

! [email protected]! http://carlos-sierra.net! @csierra_usa

50


51/51

using adaptive cursor sharing (acs) to produce multiple optimal plans.pptx.pdf

Documents