Check and change default setting of gather Statistics in Oracle

Following are the default parameter present in the Gather Stats job in Oracle:

Gather stats parameter value you can define while using it.

Syntax:
DBMS_STATS.GATHER_SCHEMA_STATS ( ownname VARCHAR2,
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,
options VARCHAR2 DEFAULT 'GATHER',
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (get_param('NO_INVALIDATE'),
force BOOLEAN DEFAULT FALSE,
obj_filter_list ObjectTab DEFAULT NULL);


ESTIMATE_PERCENT: Determines the percentage of rows to estimate
Values:
DBMS_STATS.AUTO_SAMPLE_SIZE – Oracle calculate percentage according the the table size.(Default Value)
Percent value(0 to 100) – Valid range is 0.000001 to 100. NULL value will be treated as 100%.

METHOD_OPT: used for column statistics and histogram creation for columns.
Values:
FOR ALL COLUMNS SIZE AUTO – Oracle calculate the histogram based on data and workload.(Default Value)
FOR ALL [INDEXED | HIDDEN] COLUMNS [size] – For calculate stats for indexed columns , hidden columns or skip them for all columns. SIZE value in Integer | AUTO | REPEAT | SKEWONLY.
FOR COLUMNS [size] columnname [size] – You can specify the column name for calculate histogram and specify with size also.
SIZE values: Integer | AUTO | REPEAT | SKEWONLY
Integer – Number of histogram buckets. value must be in 1 to 254.
AUTO – Collect column histograms based on data and workload of the columns.
REPEAT – Collects histograms only on the columns that already present histograms.
SKEWONLY – Collect histograms based on the data distribution of the columns.

DEGREE: Define the parallelism for gathering stats.
Values:
NULL – use table default value of degree for parallelism (Default Value)
n – Integer value for define the degree of parallelism
DBMS_STATS.DEFAULT_DEGREE – value based on the initialization parameters.
AUTO_DEGREE – determines the degree of parallelism automatically.

CASCADE: Define index statistics are collected or not as part of gathering stats job.
Values:
TRUE – Gather index stats associated with gather stats job. (Default Value)
FALSE – Skip index gather stats with job.

GRANULARITY: Defines statistics to collect for global, partitioned and subpartition etc.
Values:
AUTO – determines the granularity based on the partitioning type(Default Value)
ALL – gathers all (subpartition, partition, and global) statistics
GLOBAL – gathers global statistics
GLOBAL AND PARTITION – gathers the global and partition level statistics. No subpartition level statistics are gathered.
PARTITION – gathers partition-level statistics
SUBPARTITION – gathers subpartition-level statistics.

PUBLISH: Define statistics will be published once the gather job has completed.
Values:
TRUE – Published after stats gathered (Default Value)
FALSE – Not published after gathered.

NO_INVALIDATE: Determines whether to invalidate dependent cursors or not
Values:
DBMS_STATS.AUTO_INVALIDATE – Oracle decide when to invalidate dependent cursors.(Default Value)
TRUE – Does not invalidate the dependent cursors
FALSE – Invalidates dependent cursors

INCREMENTAL: Define the gather statistics by scanning only changed partition instead of complete all partitions.
Values:
FALSE – Oracle will gathered full statistics on all partition everytime.(Default Value)
TRUE – Oracle will only update the global table statistics by tracking the changed partition.

STALE_PERCENT: Define percentage of rows of the table that is changed to declare table have stale statistics.
Values:
10% (Default Value)

AUTOSTATS_TARGET: only for auto statistics collection and may only be set using the procedure: SET_GLOBAL_PREFS
Values:
AUTO – Oracle decides for which objects to collect statistics(Default Value)
ALL – Statistics are collected for all objects in the system
ORACLE – Statistics are collected for all Oracle owned objects.

Check the default stats value of Parameters
Note: if specify the table name and owner name then it show result for table otherwise it go for global perferences.

Syntax:
DBMS_STATS.GET_PREFS (pname IN VARCHAR2,ownname IN VARCHAR2 DEFAULT NULL,tabname IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;

Example:
-- For global
select dbms_stats.get_prefs('CASCADE') from dual;
select dbms_stats.get_prefs('DEGREE') from dual;

--For table level
select dbms_stats.get_prefs('CASCADE','HR','EMP') from dual;
select dbms_stats.get_prefs('DEGREE','HR','EMP') from dual;

select dbms_stats.get_prefs('ESTIMATE_PERCENT') from dual;
select dbms_stats.get_prefs('METHOD_OPT') from dual;
select dbms_stats.get_prefs('NO_INVALIDATE') from dual;
select dbms_stats.get_prefs('GRANULARITY') from dual;
select dbms_stats.get_prefs('PUBLISH') from dual;
select dbms_stats.get_prefs('INCREMENTAL') from dual;
select dbms_stats.get_prefs('INCREMENTAL_LEVEL') from dual;
select dbms_stats.get_prefs('STALE_PERCENT') from dual;

---New introduced in 12C/18C/19C
select dbms_stats.get_prefs('AUTOSTATS_TARGET') from dual;
select dbms_stats.get_prefs('CONCURRENT') from dual;
select dbms_stats.get_prefs('INCREMENTAL_STALENESS') from dual;
select dbms_stats.get_prefs('GLOBAL_TEMP_TABLE_STATS') from dual;
select dbms_stats.get_prefs('TABLE_CACHED_BLOCKS') from dual;
select dbms_stats.get_prefs('OPTIONS') from dual;
select dbms_stats.get_prefs('STAT_CATEGORY') from dual;
select dbms_stats.get_prefs('PREFERENCE_OVERRIDES_PARAMETER') from dual;
select dbms_stats.get_prefs('APPROXIMATE_NDV_ALGORITHM') from dual;
select dbms_stats.get_prefs('AUTO_STAT_EXTENSIONS') from dual;
select dbms_stats.get_prefs('WAIT_TIME_TO_UPDATE_STATS') from dual;
select dbms_stats.get_prefs('ROOT_TRIGGER_PDB') from dual;
select dbms_stats.get_prefs('COORDINATOR_TRIGGER_SHARD') from dual;
select dbms_stats.get_prefs('AUTO_TASK_STATUS') from dual;
select dbms_stats.get_prefs('AUTO_TASK_MAX_RUN_TIME') from dual;
select dbms_stats.get_prefs('AUTO_TASK_INTERVAL') from dual;


Change the value of these parameters
SET_GLOBAL_PREFS : Change the default value of the parameters used by gather stats for any object in database.

Syntax:
EXEC DBMS_STATS.SET_GLOBAL_PREFS (pname IN VARCHAR2,pvalue IN VARCHAR2);
Example:
exec dbms_stats.set_global_prefs(pname=>'NO_INVALIDATE',pvalue=>'FALSE');


SET_TABLE_PREFS : Procedure is used to change the default values for the tables only.

Syntax:
EXEC DBMS_STATS.SET_TABLE_PREFS (ownname IN VARCHAR2,tabname IN VARCHAR2,pname IN VARCHAR2,pvalue IN VARCHAR2);
Example:
exec dbms_stats.set_table_prefs(ownname=>'HR',tabname=>'EMPLOYEES',pname=>'CASCADE',pvalue=>'FALSE');


SET_SCHEMA_PREFS : Procedure is used to change default value for schemas objects only.

Syntax:
EXEC DBMS_STATS.SET_SCHEMA_PREFS (ownname IN VARCHAR2,pname IN VARCHAR2,pvalue IN VARCHAR2);
Example:
exec dbms_stats.set_schema_prefs(ownname=>'HR',pname=>'STALE_PERCENT',pvalue=>'20');


SET_DATABASE_PREFS : Procedures used to change default value for all user defined schemas.

Syntax:
DBMS_STATS.SET_DATABASE_PREFS (pname IN VARCHAR2,pvalue IN VARCHAR2,add_sys IN BOOLEAN DEFAULT FALSE);
Example:
exec dbms_stats.set_database_prefs(pname=>'METHOD_OPT',pvalue=>'FOR ALL COLUMNS SIZE SKEWONLY',add_sys=>TRUE)


Note: Reset the value of a parameter to the default value,use NULL
exec dbms_stats.set_global_prefs('NO_INVALIDATE',NULL)