Histograms Intro

The following are three great posts by Jonathan Lewis explaining all about histograms up to all Oracle versions prior to 12c. You will learn about the two main types of histograms: frequency histograms and height-balanced histograms, what they are, how you create them, and how they are used.

• Histograms - Part 1
• Histograms - Part 2
• Histograms - Part 3

Also, a great article about optimizer statistics in general but including a nice intro on histograms also, can be found in the following OTN white paper "Understanding Optimizer Statistics".

Enjoy!

Oracle Learner

Update your stale statistics

Object statistics are very important for your data warehouse. Tables and indexes must have updated statistics in order for the query optimizer to choose the best execution plan for each query. So you must continously gather objects statistics (especially after ETL jobs have modified significantly the data).

The easiest way to do this is to utilize Oracle's automatic statistics gathering task. This can be easily configured to run at a specific maintenance window (that you will define, unless you want to use the default) from Enterprise Manager (Administration --> Automated Maintenance Tasks --> Optimizer Statistics Gathering).

Note that you should use an appropriate degree of parallelism so that the stats gathering process runs fast enough to fit in the maintenance window. You may have to experiment a bit with this, in order to find what is appropriate for your case.

But how can you monitor if the automated task has succesfully managed to gather statistics in the defined maintenance window?