| |
 |
|
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. |
