Histogram Stats in Columns
The METHOD_OPT parameter is probably the most misunderstood parameter in the DBMS_STATS.GATHER_*_STATS procedures. It’s most commonly known as the parameter that controls the creation of histograms
Histogram is collecting statistics on columns for better query selectivity and optimal execution plan
The METHOD_OPT parameter syntax is made up of multiple parts. The first two parts are mandatory and are broken down in the diagram below.
FOR ALL INDEXED COLUMNS limits base column gathering to only those columns that are included in an index.
FOR ALL HIDDEN COLUMNS limits base column statistics gathering to only the virtual columns that have been created on a table.
FOR ALL COLUMNS is column statistics gathering on all columns in the table
The SIZE part of the METHOD_OPT syntax controls the creation of histograms
AUTO means Oracle will automatically determines the columns that need histograms based on the column usage information (SYS.COL_USAGE$)
SKEWONLY automatically creates a histogram on any column that shows a skew in its data distribution.
An integer (SIZE) value indicates that a histogram will be created with at most the specified number of buckets. Must be in the range [1,254]. To force histogram creation it is recommend that the number of buckets be left at 254. Note SIZE 1 means no histogram will be created.
Collecting histograms stats for only a set of columns
Example:
Below is the DBMS_STATS.GATHER_TABLE_STATS command in action. The only column that has a histogram created on it is the CUST_ID, even though several of the columns in the SALES table were used in the where clause of queries executed on the system.
BEGIN dbms_stats.Gather_table_stats(‘SH’, ‘SALES’, – method_opt => ‘FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 CUST_ID’);
END;
/
PL/SQL procedure successfully completed.
SELECT column_name, num_distinct, histogram
FROM user_tab_col_statistics
WHERE table_name = ‘SALES’;
