James's Blog

Oracle: Histogram-1

A new feature of the dbms_stats package is the ability to look for columns that should have histograms, and then automatically create the histograms. Oracle introduced some new method_opt parameter options for the dbms_stats package. These new options are auto, repeat and skewonly and are coded as follows:

method_opt=>'for all columns size auto'
method_opt=>'for all columns size repeat'
method_opt=>'for all columns size skewonly'


The auto option is used only when monitoring has been invoked via the alter table…monitoring command. Histograms are created based upon both the data distribution (see Figure A) and the workload on the column as determined by monitoring, like this.

execute dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size auto',
degree => DBMS_STATS.DEFAULT_DEGREE);


Using the repeat option, histograms are collected only on the columns that already have histograms. Histograms are static, like any other CBO statistic, and need to be refreshed when column value distributions change. The repeat option would be used when refreshing statistics, as in this example:

execute dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size repeat',
degree => DBMS_STATS.DEFAULT_DEGREE);

The skewonly option introduces a very time-consuming build process because it examines the data distribution of values for every column within every index. When the dbms_stats package finds an index whose column values are distributed unevenly, it creates histograms to help the CBO make a table access decision (i.e., index versus a full-table scan). From the earlier vehicle_type example, if an index has one column value (e.g., CAR) that exists in 65 percent of the rows, a full-table scan will be faster than an index scan to access those rows, as in this example:

execute dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'for all columns size skewonly',
degree => DBMS_STATS.DEFAULT_DEGREE);

posted by James Pei @ 5:27 PM