Friday, January 9, 2015

Histograms Tidbits

Make sure histograms exist on columns with uneven data distributions to ensure that the optimizer makes the best choice between indexes and table scans.

For range scans on data that is not uniformly distributed, the optimizers’ decisions will be improved by the presence of a histogram.

Histograms increase the accuracy of the optimizer’s cost calculations but increase the overhead of statistics collections. It’s usually worth creating histograms for columns where you believe the data will have an irregular distribution, and where the column is involved in WHERE or JOIN expressions.

CREATING HISTOGRAMS WITH METHOD_OPT

The METHOD_OPT option controls how column level statistics, in particular histograms, are created. The default value is ‘FOR ALL COLUMNS SIZE AUTO’,

which enables Oracle to choose the columns that will have a histogram collected and set the appropriate histogram bucket size.

No comments:

Collaborate14

Presenting at

My Book

Speaking at:

It's All About Passion

Oracle ACE. Exadata Certified Specialist, Oracle Blogger, Oracle DBA, OCP in 11g DBA and 10g DBA and working in Oracle products since 2001. Speaker at conferences like Oracle Open World, UKOUG, Collaborate, Insync etc around the globe. Currently working with Pythian Australia as Senior Oracle DBA.