Neeraj Bhatia's Blog

November 12, 2010

Everything You Want to Know About Oracle Histograms Part-1

Filed under: Performance Optimization — neerajbhatia @ 23:25

In the first part of Oracle Histograms series, I am discussing basic concepts like what is a histogram in general, why histograms are required in Oracle and how they are implemented etc.

Link to Part-1

This series may have two more parts about practical details and case studies.

Enjoy!

Advertisements

16 Comments »

  1. Hi,

    Excellent article.Eagerly waiting for rest of the parts in series.

    Regards,
    Anand

    Comment by Anand — November 13, 2010 @ 10:55 | Reply

  2. Very well explained. Keep up the good work. I’m looking forward for the other parts.

    Regards
    Hans

    Comment by Hans — November 16, 2010 @ 16:36 | Reply

  3. Thanks Hans, Anand for the appreciation. I’m working on part-2 and will release soon.

    Cheers,
    Neeraj

    Comment by neerajbhatia — November 21, 2010 @ 09:32 | Reply

  4. Thanks Neeraj. Nicely written and very informative article. When you are planning to release the part-2 of it?

    Thanks and Best Regards,
    Chandan

    Comment by Chandan — January 11, 2011 @ 22:49 | Reply

    • I appreciate that. I was busy in some Oracle internals work, hence this series delayed. But now I am planning to start work. You can expect something in January month itself. Thanks!

      Comment by neerajbhatia — January 12, 2011 @ 07:34 | Reply

  5. It is really good to have such a nice understanding of Histogram.

    Comment by Prerit — January 12, 2011 @ 16:27 | Reply

    • Thanks Prerit for the nice words. I am working on next part and soon will roll it out.

      Comment by neerajbhatia — January 12, 2011 @ 20:28 | Reply

  6. hi Neeraj,
    it was really nice exp for me to read this post …

    waiting for second part..

    Sachin ..

    Comment by Sachin — May 4, 2011 @ 00:48 | Reply

  7. Excellent. very good explanation.. waiting for your second part.. its been long time now 🙂

    Comment by Dax — May 15, 2012 @ 11:20 | Reply

    • Thanks Dax for the comments. Actually I am receiving several personal emails also asking for part-2, but unfortunately I don’t have any.

      Quite busy these days … most of my time is being spent in doing Capacity Planning of IT services (Banking Application to be specific) and performance management of virtualized environment. Let me see what best I can document as part-2 asap.

      Please bear with me!

      Neeraj

      Comment by neerajbhatia — May 15, 2012 @ 19:18 | Reply

  8. Fantastic One Neeraj…Eagerly waiting for the part 2 and 3.

    Ta
    Jag

    Comment by jagatheesh — May 29, 2012 @ 17:49 | Reply

  9. Hi Neeraj,
    I was googling sometime on histograms for the good articles.I think your write up is simply best by giving detailed information.
    It would be great if you can post Part 2 and 3.

    Regards,
    Niranjan

    Comment by Niranjan — May 13, 2013 @ 05:14 | Reply

  10. Wonderful article. Got a clear understanding on how Histograms work

    Comment by Saravanan — February 5, 2014 @ 09:33 | Reply

  11. Thanks for the great article,

    A question about HB histograms. Using HISTOGRAM table from your example (10000 rows) just tested predicate skew=10000 and it estimates 9000 rows. Though the formula for popular values in HBH – says that Cardinality = (popular endpoint number)/(bucket number)*row number. In this case, if i calculate correctly, it is 5/5* 10000=10000. So, why the optimizer estimates 9000 rows? db version is 11.2.0.3.0.
    Thanks in advance,
    Vato,

    Comment by vato — January 4, 2015 @ 03:21 | Reply

  12. hi,

    I think i got something interesting at http://www.hellodba.com/reader.php?ID=213&lang=EN
    It says:

    If value is pop value and is not maximum value,

    selectivity=PopBktNum/BktCnt;

    If value is pop value and is also maximum value,

    selectivity=(PopBktNum-0.5)/BktCnt;

    Which answers my question as in my case pop value 10000 is maximum value as well. Thus 4.5/5*100 =9000.

    Thanks,
    vato,

    Comment by vato — January 4, 2015 @ 14:45 | Reply

  13. Please, haven’t you produce part 2 of this wonderful article ?

    Comment by yannick — June 7, 2016 @ 20:08 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: