Histograms are generated in Oracle by using the method_opt parameter in the dbms_stats procedure
method_opt syntax
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [column_clause] [size_clause]
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
column_clause is defined as column_clause := column_name | extension name | extension
- integer : Number of histogram buckets. Must be in the range [1,2048].
- REPEAT : This Causes the histograms to be created with the same options as the last time you created them. It reads the data dictionary to figure out what to do.
- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns. it looks at the data and using a magical, undocumented, and changing algorithm, figures out all by itself what columns to gather stats on and how many buckets and all.
- SKEWONLY : Oracle determines the columns on which to collect histograms based on the data distribution of the columns.
– column_name : Name of a column
– extension : can be either a column group in the format of (column_name, Colume_name [, …]) or an expression
Let’s table example to understand this
Examples
FOR ALL COLUMNS SIZE 1
This disables the histograms on all the columns.
FOR ALL COLUMNS SIZE 255
This enables the histograms on all the columns and the bucket size is 255
FOR ALL INDEXED COLUMNS SIZE 255
This enables the histograms on all the columns which are present in the index and the bucket size is 255. But then it disables all the other column statistics also
FOR ALL COLUMNS SIZE AUTO
This enables the histograms on all the columns with the AUTO option for size
FOR ALL INDEXED COLUMNS SIZE AUTO
This enables the histograms on all the columns which are present in the index with the AUTO option for size. But then it disables all the other column statistics also
All the examples we saw so far can be set at the database level, schema level, or table level.
Now let’s look at some table-level specific method opt
FOR ALL COLUMNS SIZE 1 FOR COLUMNS STATUS_CODE size 255
This disables the histograms for all the columns except status_code in the table
How to set the Method_opt
The default for method_opt at the database level is FOR ALL COLUMNS SIZE AUTO. So if you are not giving the method_opt parameter in the gather stats procedure, histograms are generated in auto mode
We can change this setting at the schema level, global level, and table level using SET_*_PREFS
Some examples
DBMS_STATS.GATHER_TABLE_STATS( 'SCOTT', 'EMP', method_opt => 'FOR COLUMNS STATUS SIZE 255'); DBMS_STATS.GATHER_TABLE_STATS( 'SCOTT', 'EMP', method_opt => 'FOR ALL INDEXED COLUMNS SIZE 255');
I hope you like this article on How are the histograms generated in Oracle. Please do provide the feedback
Related Articles
Histograms in EBS
Histograms queries in Oracle
Skew in Database