Analyze Table option "Collect Histograms with Tables" box is not checked, but DBMS_STATS is still (4309219)

When Analyze Table | Options | Select 'Use DBMS stats' | 'Collect Histograms with tables' is unchecked, histograms are still collected.

If this is unchecked it should not collect histograms.

The parameter 'METHOD_OPT' in DBMS_STATS is used to specify the level of histogram collection. If METHOD_OPT is not specified then the default is get_param('METHOD_OPT'), hence picks up the default for the database. There appears to be no option to not collect histograms.

METHOD_OPT only accepts:

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...], where size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

integer--Number of histogram buckets. Must be in the range [1,254].

REPEAT--Collects histograms only on the columns that already have histograms.

AUTO--Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

SKEWONLY--Oracle determines the columns to collect histograms based on the data distribution of the columns.

The default is FOR ALL COLUMNS SIZE AUTO. The default value can be changed using the SET_PARAM Procedure.

So , there is no option in Oracle to NOT COLLECT histograms.


The option Analyze Table | Options | Select 'Use DBMS stats' | 'Collect Histograms with tables' is misleading. When this option is unchecked the DBMS_STATS will run with the default i.e. FOR ALL COLUMNS SIZE AUTO

WORKAROUND:

none

STATUS:

Waiting for fix in a future release of Toad for Oracle