1 / 44

Iordan K. Iotzov Senior Database Administrator News America Marketing ( NewsCorp )

Working with Confidence: How Sure Is the Oracle CBO About Its Cardinality Estimates, and Why Does It Matter?. Iordan K. Iotzov Senior Database Administrator News America Marketing ( NewsCorp ) iiotzov@newsamerica.com Blog: http://iiotzov.wordpress.com/. About me.

errol
Download Presentation

Iordan K. Iotzov Senior Database Administrator News America Marketing ( NewsCorp )

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. Working with Confidence: How Sure Is the Oracle CBO About Its Cardinality Estimates, and Why Does It Matter? Iordan K. Iotzov Senior Database Administrator News America Marketing (NewsCorp) iiotzov@newsamerica.com Blog: http://iiotzov.wordpress.com/

  2. About me • 10+ years of database administration and development experience • MS in Computer Science, BS in Electrical Engineering • Presented at Hotsos, NYOUG and Virta-Thon • Active blogger and OTN participant • Senior DBA at News America Marketing (NewsCorp)

  3. Agenda • Overview • Foundations of Estimating Cardinality • Confidence of Cardinality Estimates in Oracle • Cardinality Confidence in Action • Conclusion

  4. OverviewAsk Why Typical SQL tuning thought process: The optimizer generated suboptimal plan that takes a long time to execute. Why? What happened? In many cases, the optimizer did not get cardinalities correct– the actual number is very different from the estimated one (Tuning by Cardinality Feedback) Why did the optimizer miscalculate the cardinalities? It lacked statistics or it made assumptions/guesses that turned out to be incorrect

  5. OverviewAsk Why Guesswork in technology is (rightfully) considered bad… BAAG (Battle Against Any Guess ) party: “The main idea is to eliminate guesswork from our decision making process — once and for all.” … “Guesswork - just say no!” So if guesswork is so bad, should not we at least be aware of the guesswork done by software, such as the Oracle CBO? You bet…

  6. OverviewConfidence of Cardinality Estimates in Oracle, Current State Working assumption: Oracle CBO does not universally compute or use confidence level of its cardinality estimates • It is hard to prove a negative, but • There is no official documentation about confidence levels of cardinality estimates • Experts agree – Thanks Mr. Lewis! • 10053 trace shows no indication that such information is available

  7. Overview Confidence of Cardinality Estimates in Oracle, Current State Q1 – query that forces CBO to make wild assumptions Q2 – query that forces CBO to make reasonable assumptions select tab2.* from  tab1 , tab2 where tab1.str like '%BAA%' and tab1.id = tab2.id select tab2.* from tab1 , tab2 where tab1.NUM = 14 and tab1.id = tab2.id

  8. Overview Confidence of Cardinality Estimates in Oracle, Current State | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 38K | | | 1 | HASH JOIN | | 488K | 21M | 38K | 00:08:49 | | 2 | TABLE ACCESS FULL | TAB1 | 488K | 11M | 11K | 00:02:20 | | 3 | TABLE ACCESS FULL | TAB2 | 9766K | 210M | 10K | 00:02:06 | --------------------------------------+-----------------------------------+ Plan for Q1: | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 38K | | | 1 | HASH JOIN | | 488K | 15M | 38K | 00:08:45 | | 2 | TABLE ACCESS FULL | TAB1 | 488K | 4395K | 11K | 00:02:20 | | 3 | TABLE ACCESS FULL | TAB2 | 9766K | 210M | 10K | 00:02:06 | --------------------------------------+-----------------------------------+ Plan for Q2:

  9. Overview Confidence of Cardinality Estimates in Oracle, Current State What if Oracle CBO could give us more feedback about how it is handling imperfect information… SQL> EXPLAIN PLAN for Select tab1.col1 , … from tab1, tab2, … where func1(tab1.col1,tab2.col2) > (case when tab1.col2 > nvl(tab2.col3,9999) then func4(tab2.col2) else func5(tab2.col2) end) and tab1.col5 like ‘%D’ and … PLAN_TABLE_OUTPUT SQL_ID bb01hpyckva25, child number 0 ------------------------------------- Dude, you are taking the “Oracle” part waaay too literally…

  10. OverviewCardinality Confidence in Other Databases The Teradata Approach: Each step has one of four discrete levels of confidence Primary index, single selection criteria and statistics High Confidence: Some statistics needed If join, stats/indexes must exists for both sides Low Confidence: One side of join has stats, but the other ones does not have Index Join Confidence: No usable statistics exist No Confidence:

  11. Overview Cardinality Confidence in Other Databases A very simple query: select * from iiotzov.dep where dep_name = 'IT' Explanation 1) First, we lock a distinct iiotzov."pseudo table" for read on a RowHash to prevent global deadlock for iiotzov.dep. 2) Next, we lock iiotzov.dep for read. 3) We do an all-AMPs RETRIEVE step from iiotzov.dep by way of an all-rows scan with a condition of ("iiotzov.dep.DEP_NAME = 'IT '") into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 1 row. The estimated time for this step is 0.01 seconds. 4) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.01 seconds.

  12. Overview Cardinality Confidence in Other Databases A simple query: select b.* from iiotzov.dep a , iiotzov.emp b where a.dep_name = 'IT' and a.dep_id = b.dep_id Explanation 1) 2)3).... 4) We do an all-AMPs RETRIEVE step from iiotzov.b by way of an all-rows scan with no residual conditions into Spool 2 (all_amps),which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 isestimated with high confidence to be 2 rows. The estimated time for this step is 0.00 seconds. 5) We do an all-AMPs JOIN step from iiotzov.a by way of a RowHash match scan with a condition of ("iiotzov.a.DEP_NAME = 'IT '"), which is joined to Spool 2 (Last Use) by way of a RowHash match scan. iiotzov.a and Spool 2 are joined using a merge join, with a join condition of ("iiotzov.a.DEP_ID = DEP_ID"). The result goes into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 2 rows. The estimated time for this step is 0.00 seconds. 6) …..

  13. Overview Cardinality Confidence in Other Databases Trickle-down effect of No/Low Confidence: • Since increasing the confidence as the tables are joined is almost impossible, • lower confidence levels coming from previous steps will dominate the confidence • level of the steps they feed into. • Most non-trivial queries would end up with Low (or lower) • Confidence, regardless of the efforts we put into gathering statistics

  14. Overview Cardinality Confidence in Other Databases Cardinality confidence of a real query: Step/Confidence Step/Confidence 15)No 16)No 17)No 18)High/No 19)No 20)No 21)No 22)No 23)High/Index Join 24) Index Join 25)No 26)No 27)No 28)No 29) 1) 2)High 3)High 4)High 5)High 6)High 7)High/No 8)Low/No 9)No 10)High 11)No 12)No 13)No 14)No S t e p Working with Limited Visibility Confidence

  15. Foundations of Estimating Cardinality Joins Basic formula for join cardinality: Result Join Source 1 Source 2

  16. Foundations of Estimating Cardinality Joins - Accounting for Errors Formula for join cardinality accounting for errors: Result Join Source 1 Source 2

  17. Foundations of Estimating Cardinality Joins - Accounting for Errors Error propagation for multi-step joins: Each table cardinality estimate comes with (only!) 10% errors Join structure Errors 1.14 0.94 0.1 0.772 0.1 0.61 0.1 0.464 0.1 0.331 0.1 0.1 0.21 0.1 0.1

  18. Foundations of Estimating Cardinality Filters Basic formula for cardinality with two filters (AND) + Result Filter 1 Filter 2 Source

  19. Foundations of Estimating Cardinality Filters – Accounting for Errors Formula for cardinality with two filters (AND), accounting for errors Result Filter 1 Filter 2 Source

  20. Foundations of Estimating Cardinality Filters – Accounting for Errors Aggregation of errors for multiple filters Result Filter 1 Result Filter 2 Filter 1 Filter 3 Source Source

  21. Confidence of Cardinality Estimate in OracleAn Attempt to Measure XPLAN_CONFIDENCE package: • An effort to measure the maximum cardinality error, a proxy for confidence, for every step in an execution plan. • The maximum error is a continuous variable! • Absolutely no warranties (I wrote it) – for demonstration purposes only • Uses few basic factors and ignores many important ones: • Does not recognize sub-queries, inline views and other “complex” structures • Limited ability to parse complex filters • Not aware of profiles, dynamic sampling, adaptive cursor sharing • Limited ability to parse and handle functions(built-in and PL/SQL) • Not aware of query rewrite and materialized views, • Very limited ability to handle extended statistics • And many, many more limitation and restrictions

  22. Confidence of Cardinality Estimates in OracleAn Attempt to Measure A simple example: select * from table(xplan_confidence.display('61hfhfk5ts01r')) PLAN_TABLE_OUTPUT SQL_ID 61hfhfk5ts01r, child number 0 ------------------------------------- SELECT RPLW.A_ID FROM TAB1 RPLW , TAB2 RMW WHERE RMW.P_ID = RPLW.P_ID AND RMW.O_ID = :B2 AND RMW.R_ID = :B1 Plan hash value: 3365837995 ------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Max. Error | ------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 9 (100)| | .16| |* 1 | HASH JOIN | | 1 | 23 | 9 (12)| 00:00:01 | .16| |* 2 | TABLE ACCESS BY INDEX ROWID| TAB2 | 1 | 15 | 3 (0)| 00:00:01 | .1| |* 3 | INDEX RANGE SCAN | NUK_TAB2_R_ID | 83 | | 1 (0)| 00:00:01 | .05| | 4 | TABLE ACCESS FULL | TAB1 | 1179 | 9432 | 5 (0)| 00:00:01 | 0| ------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access(RMW.P_ID=RPLW.P_ID) 2 - filter(RMW.O_ID=:B2) 3 - access(RMW.R_ID=:B1)

  23. Confidence of Cardinality Estimate in OracleAn Attempt to Measure Technical notes: create or replace package xplan_confidence as ……  function display(sql_id varchar2 default null, cursor_child_no integer default 0, format varchar2 default 'typical') return sys.dbms_xplan_type_table pipelined; end xplan_confidence; • Input and Output similar to DBMS_XPLAN.DISPLAY_CURSOR • Works only for SQL statements that are in the cache – V$SQL_PLAN • Works best in SQL Developer

  24. Confidence of Cardinality Estimates in OracleAn Attempt to Measure Inside XPLAN_CONFIDENCE package: XPLAN_CONFIDENCE DB V$SQL_PLAN Reads plan DBA_TAB_COLUMNS Calculates max error (recursive operation) and saves results DBA_INDEXES DBA_HISTOGRAMS …. max. error info Generate plan and appends max. error info DBMS_XPLAN

  25. Confidence of Cardinality Estimates in OracleAssumptions

  26. Confidence of Cardinality Estimates in OracleAssumptions

  27. Confidence of Cardinality Estimates in OracleAssumptions

  28. Confidence of Cardinality Estimates in OracleAssumptions

  29. Confidence of Cardinality Estimates in OracleOutside of the Model

  30. Confidence of Cardinality Estimates in OracleA Proactive Design Approach • Review SQL coding standard and vet all new SQL features and constructs: • Can CBO reliably figure out the selectivity/cardinality of the new feature under • different circumstances? • Explain plan • 10053 traces • How easy it is to supply the CBO with the needed information?

  31. Cardinality Confidence in ActionNormal confidence deterioration

  32. Cardinality Confidence in ActionNormal Confidence Deterioration Reasons why the larger the SQL, the higher the chance of suboptimal execution plan: • The confidence of the cardinality get diminished as the query progresses • Most SQL constructs pass on or amplify the cardinality errors • Few SQL construct reduce cardinality errors Example: where col in (select max(col1) from subq) The cardinality errors in subq will not be propagated

  33. Cardinality Confidence in ActionNormal Confidence Deterioration • CBO cannot examine all join permutations Number of permutation for n tables is (n!). (n!) is growing really fast: (n) (n!) 13 6226020800 14 87178291200 15 1307674368000 Trends: • (+) Faster CPU allow for more permutations • (+) The optimizer uses better heuristics to reduce the search space • (-) More parameters are used to measure cost

  34. Cardinality Confidence in ActionNormal Confidence Deterioration Mitigating the effects of Normal Confidence Deterioration for large queries: Logically “split” the query

  35. Cardinality Confidence in ActionNormal Confidence Deterioration Logically “splitting” the query using NO_MERGE hint select subq1.id , sub12.name, … from (select .. from a,b ..) subq1 , (select .. from n,m ..) subq2 where subq1.col1 = subq2.sol2 and… select /*+ NO_MERGE(subq1) NO_MERGE(subq2) */ subq1.id , sub12.name, … from (select .. from a,b ..) subq1 , (select .. from n,m ..) subq2 where subq1.col1 = subq2.sol2 and…

  36. Cardinality Confidence in ActionRapid Confidence Deterioration

  37. Cardinality Confidence in ActionRapid Confidence Deterioration Certain predicates contribute disproportionately to confidence deterioration Step 31: filter(""RS"".""RS_ID""MEMBER OF:1 AND "RS"".""ST"" LIKE “%C" ) Step 18: filter((""S""."“D"" IS NULL OR LOWER(""S"".""D"")=‘fs1' OR LOWER(""S"".""D"")=‘fs2' OR (LOWER(""S"".""D"")='cmb‘ AND LOWER(""S""."“R"")=‘f1' ) OR ( LOWER(""S"".""D"")='cnld' AND LOWER(""S"".“”R"")=‘f2‘) OR LOWER(""S"".""D"")='err'))" In many cases, performance optimization is nothing more than finding the predicates that confuse the optimizer the most, and dealing with them

  38. Cardinality Confidence in ActionRapid Confidence Deterioration Ways to deal with “problem“ predicates: • Dynamic sampling • ->In some cases (Oracle 11gr2) Oracle decides to run dynamic sampling without explicit instructions • Utilize strategies to supply relevant information to the optimizer • ->Extended Statistics • ->Virtual columns • ->ASSOCIATE STATISTICS • Rewrite to a less “confusing” predicate • for example, this clause • and col1 <= nvl(col2,to_timestamp( '12-31-9999','mm-dd-yyyy')) • can be simplified to • and (col1 <= col2 or col2 is NULL)

  39. Cardinality Confidence in ActionRapid Confidence Deterioration • Push the “problem” predicate towards the end of the execution plan, so the damage it does is minimized B G G F C E E D F C A D A B

  40. Cardinality Confidence in ActionRapid Confidence Deterioration • Split the single SQL into multiple SQL G G F F E E Gather Stats D D C C A B A B

  41. Cardinality Confidence in Action Still Relevant with Oracle 12c? Adaptive Execution Plans looks like a very valuable feature that would likely change Oracle performance tuning as we know it. Time for another plan to kick in! Actual Size Estimated Size Threshold for Adaptive Execution Plans

  42. Cardinality Confidence in Action Still Relevant with Oracle 12c? • Resorting to Adaptive Execution Plans is, however, a reactive measure. • It is a backstop for executions gone bad due to cardinality problems. • Adaptive Execution Plans, like any advanced feature, comes with some costs. • The best way is to get the optimal execution plan the first time. • And to do that, we need to spare the CBO from any unnecessary guesses.

  43. ConclusionIn a Few Words Ask not what the optimizer can do for you - ask what you can do for the optimizer… Huge SQL statements are not the solution to our problem, huge SQL statements are the problem... Cool new features – trust, but verify…

  44. Thank you!

More Related