Everything You Want to Know About Oracle Histograms Part-1

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!

Comments (16)

16 Comments »

Hi,

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

Regards,
Anand

Comment by Anand — November 13, 2010 @ 10:55 | Reply
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
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
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
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
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
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
Fantastic One Neeraj…Eagerly waiting for the part 2 and 3.

Ta
Jag

Comment by jagatheesh — May 29, 2012 @ 17:49 | Reply
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
Wonderful article. Got a clear understanding on how Histograms work

Comment by Saravanan — February 5, 2014 @ 09:33 | Reply
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
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
Please, haven’t you produce part 2 of this wonderful article ?

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