Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson

Using Histograms

Histograms help optimize queries and other actions against data that is nonuniformly distributed about a mean. The common term for poorly distributed data is skewed data. In particular, in earlier versions of ORACLE7, the cost-based optimizer would go out to lunch if you handed it skewed data. There is a cost associated with histograms, so they should be used only for badly skewed data. Histograms are static and must be periodically renewed just like table statistics.

Histograms should not be used when:

  • All predicates on the column use bind variables.

  • The column data is uniformly distributed.

  • The column is not used in WHERE clauses of queries.

  • The column is unique and is used only in equality predicates.

Histograms are created in “bands” of value ranges. For example, if the data in your test_result tables measurement column is skewed into six general ranges, then you would want to create six bands of history:

ANALYZE TABLE test_result

If you know the exact number of keys, and the value is less than 255, set the size to that value; otherwise, set it to 255. Histogram statistics are stored in the DBA_, USER_, and ALL_ HISTOGRAMS views. Additional row statistics appear in the USER_TAB_COLUMNS, ALL_TAB_COLUMNS, and DBA_TAB_COLUMNS views.

This is an excerpt by Mike Ault’s book “Oracle Administration & Management” .  If you want more current Oracle tips by Mike Ault, check out his new book “Mike Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s Oracle Scripts Download.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter