Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Why Is the Optimizer Estimating the Wrong Number of Rows?

D7b6e701f0155fc189bbca6c89223b3c?s=47 Chris
August 01, 2020

Why Is the Optimizer Estimating the Wrong Number of Rows?

An overview of data features that can lead the optimizer to estimate incorrectly and actions you can take to improve these.

Watch the video at https://www.youtube.com/watch?v=DGAisQZxk-s

D7b6e701f0155fc189bbca6c89223b3c?s=128

Chris

August 01, 2020
Tweet

Transcript

  1. Your SQL Office Hours begins soon… Why Is the Optimizer

    Estimating the Wrong Number of Rows? Chris Saxon @ChrisRSaxon & @SQLDaily https://www.youtube.com/c/TheMagicofSQL https://blogs.oracle.com/sql
  2. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Welcome to Ask TOM Office Hours!
  3. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | DEMO
  4. _ ∗ ∗ …

  5. Stats problems Value skew Range skew Correlation 1 2 3

  6. Copyright © 2020 Oracle and/or its affiliates. Value Skew

  7. Copyright © 2020 Oracle and/or its affiliates. red blue green

    blue blue blue green green green green green green green red red red red red red red red red red red red red
  8. blue green red Colours 4 8 14 Number of rows

  9. blue green red Colours select * from bricks where colour

    = 'blue' 4 8 14 Number of rows
  10. blue green red Colours select * from bricks where colour

    = 4 8 14 'pink' 2 Number of rows
  11. blue green red Colours insert into bricks values ( 'yellow',

    … ); Number of rows Ignore
  12. blue green red Colours Number of rows insert into bricks

    values ( 'yellow', … ); insert into bricks values ( 'yellow', … ); insert into bricks values ( 'yellow', … ); insert into bricks values ( 'yellow', … ); insert into bricks values ( 'yellow', … );
  13. blue-green green-red red-yellow Colours 10 10 10 Number of rows

  14. blue-green green-red red-yellow Colours 10 10 10 select * from

    bricks where colour = 'red' Number of rows >= 1 and < 20
  15. blue-green green-red red-yellow Colours Number of rows

  16. blue-green red yellow Number of rows Colours 4 14 12

    4 yellow 8 green 14 red
  17. Where clause Index columns BRICK_ID HYBRID COLOUR FREQUENCY SHAPE FREQUENCY

    WEIGHT NONE NOTES NONE
  18. Where clause Index columns BRICK_ID HYBRID COLOUR FREQUENCY SHAPE FREQUENCY

    WEIGHT NONE NOTES NONE Primary key?!
  19. Copyright © 2020 Oracle and/or its affiliates. Range Skew

  20. select * from bricks where brick_id between 5 and 10

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 We got nothing!
  21. 1-11 12-16 17-20 Brick_id 4 5 5 Many values Number

    of rows
  22. select * from bricks where colour = 'red' and shape

    = 'cylinder';
  23. Copyright © 2020 Oracle and/or its affiliates. Correlation

  24. COLOUR SHAPE red cube red cube blue cuboid red cube

    green star blue cuboid
  25. COLOUR SHAPE ( COLOUR, SHAPE ) red cube red cube

    blue cuboid red cube green star blue cuboid
  26. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | DEMO
  27. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | • Basic stats have limitations • Histograms & extended stats address these • Histograms created based on usage • Correlation/expressions you need to specify • Control with set_table_prefs Summary
  28. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | Understanding Optimizer Statistics With Oracle Database 19c https://www.oracle.com/technetwork/database /bi-datawarehousing/twp-stats-concepts-19c- 5324209.pdf Databases for Developers: Performance https://devgym.oracle.com/pls/apex/dg/class/ databases-for-developers-performance.html Further Reading
  29. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

    | asktom.oracle.com #MakeDataGreatAgain Ryan McGuire / Gratisography