Check and change setting of Gather Statistics in Oracle

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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.