Karl Reitschuster's Tech Space

Hi Reader,
with Oracle 10g all default parameters of the DBMS_STATS package are defined as default in the data dictionary accessible with the DBMS_STATS GET_PARAM Method :

PROCEDURE Gather_Table_Stats
(
  Ownname          VARCHAR2,
  Tabname          VARCHAR2,
  Partname         VARCHAR2 DEFAULT NULL,
  Estimate_Percent NUMBER DEFAULT To_Estimate_Percent_Type(Get_Param('ESTIMATE_PERCENT')),
  Block_Sample     BOOLEAN DEFAULT FALSE,
  Method_Opt       VARCHAR2 DEFAULT Get_Param('METHOD_OPT'),
  Degree           NUMBER DEFAULT To_Degree_Type(Get_Param('DEGREE')),
  Granularity      VARCHAR2 DEFAULT Get_Param('GRANULARITY'),
  Cascade          BOOLEAN DEFAULT To_Cascade_Type(Get_Param('CASCADE')),
  Stattab          VARCHAR2 DEFAULT NULL,
  Statid           VARCHAR2 DEFAULT NULL,
  Statown          VARCHAR2 DEFAULT NULL,
  No_Invalidate    BOOLEAN DEFAULT To_No_Invalidate_Type(Get_Param('NO_INVALIDATE')),
  Stattype         VARCHAR2 DEFAULT 'DATA',
  Force            BOOLEAN DEFAULT FALSE
);

For this reason you can change the default behaviour of the Gather routines without changing the package; The default setting which I changed first was the METHOD_OPT Parameter for sampling Table statistics; usually it’s set to 'FOR ALL COLUMNS SIZE AUTO' which means sample Histograms if you think it would be useful.

To disable the option and reset ot default 9.2 behaviour you can change for example the default value of METHOD_OPT to 'FOR ALL COLUMNS SIZE 1' which means generate no histograms on any column.

–- Disable automatic gathering of histograms ( horrible performance )
EXEC DBMS_STATS.SET_PARAM('METHOD_OPT','FOR ALL COLUMNS SIZE 1');

SELECT Sname,
       Spare4
  FROM Sys.Optstat_Hist_Control$
;

SQL> SELECT Sname,
  2         Spare4
  3    FROM Sys.Optstat_Hist_Control$;

SNAME                          SPARE4
—————————— ——————————————————————————–
SKIP_TIME                    
STATS_RETENTION              
TRACE                          0
DEBUG                          0
SYS_FLAGS                      1
CASCADE                        DBMS_STATS.AUTO_CASCADE
ESTIMATE_PERCENT               DBMS_STATS.AUTO_SAMPLE_SIZE
DEGREE                         NULL
METHOD_OPT                     FOR ALL COLUMNS SIZE 1
NO_INVALIDATE                  DBMS_STATS.AUTO_INVALIDATE
GRANULARITY                    AUTO
AUTOSTATS_TARGET               AUTO

12 rows selected

HTH

Karl Reitschuster