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

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