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


 

HTML Text

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
COMPUTE STATISTICS FOR COLUMNS measurement SIZE 6;


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