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

Why Is the Optimizer Estimating the Wrong Numbe...

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

Chris

August 01, 2020
Tweet

More Decks by Chris

Other Decks in Technology

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. 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
  3. 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', … );
  4. blue-green green-red red-yellow Colours 10 10 10 select * from

    bricks where colour = 'red' Number of rows >= 1 and < 20
  5. 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!
  6. COLOUR SHAPE ( COLOUR, SHAPE ) red cube red cube

    blue cuboid red cube green star blue cuboid
  7. 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
  8. 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
  9. Copyright © 2020, Oracle and/or its affiliates. All rights reserved.

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