1 / 50

Using Adaptive Cursor Sharing (ACS) to produce multiple Optimal Plans

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

elu
Download Presentation

Using Adaptive Cursor Sharing (ACS) to produce multiple Optimal Plans

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Using Adaptive Cursor Sharing (ACS) to produce multiple Optimal Plans Carlos SierraConsulting Technical Advisor

  2. 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. Adaptive Cursor Sharing (ACS) • Motivation • Mechanics • Test Case • Demo • Remarks

  4. ACS Motivation

  5. 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. Implementing Cursor Sharing Replacing literals with bind variables

  7. 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. 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. 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. ACS Mechanics

  11. ACS high-level Overview High level overview • If SQL with binds meetssome 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. 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. 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. 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. 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. ACS Test Case

  17. Our Query with Literals Guesstimate execution plan then verify it with demo 0

  18. Possible Access Paths?

  19. Optimal Execution Plan

  20. Our Query with Bind Variables How many optimal execution plans can you foresee?

  21. Multiple Optimal Plans for one Query Guesstimate optimal plan (access paths) for each query

  22. Multiple Optimal Plans for one Query Guesstimate optimal plan (access paths) for each query

  23. Multiple Optimal Plans for one Query Execute demos 1-5 and verify access paths

  24. 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. ACS Demo

  26. Demo 6: When Cursor becomes Bind Aware? Obtain rows processed from demo 1-5 then guesstimate aware flag

  27. Demo 6: When Cursor becomes Bind Aware? Obtain rows processed from demo 1-5 then guesstimate aware flag

  28. Demo 6: When Cursor becomes Bind Aware? Obtain rows processed from demo 1-5 then guesstimate aware flag

  29. Demo 6: When Cursor becomes Bind Aware? Obtain rows processed from demo 1-5 then guesstimate aware flag

  30. Demo 6: When Cursor becomes Bind Aware? Obtain rows processed from demo 1-5 then guesstimate aware flag

  31. Demo 7: When Cursor becomes Bind Aware? Compute bucket and guesstimate aware flag and actual plan

  32. Demo 7: When Cursor becomes Bind Aware? Compute bucket and guesstimate aware flag and actual plan

  33. Demo 7: When Cursor becomes Bind Aware? Compute bucket and guesstimate aware flag and actual plan

  34. Demo 7: When Cursor becomes Bind Aware? Compute bucket and guesstimate aware flag and actual plan

  35. Demo 7: When Cursor becomes Bind Aware? Compute bucket and guesstimate aware flag and actual plan

  36. Demo 8: When Cursor becomes Bind Aware? Guesstimate aware flag and actual plan

  37. Demo 8: When Cursor becomes Bind Aware? Guesstimate aware flag and actual plan

  38. Demo 8: When Cursor becomes Bind Aware? Guesstimate aware flag and actual plan

  39. Demo 8: When Cursor becomes Bind Aware? Guesstimate aware flag and actual plan

  40. Closing Remarks

  41. 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. 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. Understanding Selectivity Profile From demo 6

  44. Understanding Selectivity Profile From demo 6

  45. Understanding Selectivity Profile From demo 6

  46. 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. 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. References and Contact Info Oracle Optimizer Blog • https://blogs.oracle.com/optimizer/ • Insight into the workings of the Optimizer • carlos.sierra@oracle.com • http://carlos-sierra.net • @csierra_usa

More Related