How are the histograms generated in Oracle

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

method_opt syntax

Examples

How to set the Method_opt

Reader Interactions

Leave a Reply Cancel reply