Applies to:
Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]Information in this document applies to any platform.
Goal
This document provides information regarding the default parameters for gathering optimizer statistics in Oracle 11g and outlines different possible values for these parameters and how to change them. These default parameters are used when automatic gathering statistics within a maintenance window and define how these statistics will be collected.
For similar information on Oracle 10g, see:
Document 725845.1 How to Change Default Parameters for Gathering Statistics in 10g
Fix
Procedures for Changing Default Parameters
There are 4 main procedures to modify the default values for parameters used to gather statistics in Oracle 11g:
- SET_GLOBAL_PREFS
- SET_SCHEMA_PREFS
- SET_DATABASE_PREFS
- SET_TABLE_PREFS
Choice of a particular procedure over another is dependent on the 'level' at which you wish to change the defaults. For example if you wanted to modify the defaults for a single table then you would use the 'SET_TABLE_PREFS' procedure, whereas you would use 'SET_DATABASE_PREFS' to maintain settings at the database level. The required procedure needs to be run individually for each parameter that one wishes to change
SET_GLOBAL_PREFS
Enables you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for any object in the database where no existing table preference has been set.
All parameters will default to the global setting unless a table preference is set or the parameter is explicitly set in the DBMS_STATS.GATHER_*_STATS command.
Changes made by this procedure will affect any new objects created after it has been run.
New objects will use the GLOBAL_PREF values for all parameters.
Syntax
DBMS_STATS.SET_GLOBAL_PREFS (
pname IN VARCHAR2,
pvalue IN VARCHAR2);For example, to set 'NO_INVALIDATE' globally across all objects you would use:exec dbms_stats.set_global_prefs(pname=>'NO_INVALIDATE',pvalue=>'FALSE')SET_TABLE_PREFS
Enables you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for the specified table only.
Syntax
DBMS_STATS.SET_TABLE_PREFS (
ownname IN VARCHAR2,
tabname IN VARCHAR2,
pname IN VARCHAR2,
pvalue IN VARCHAR2);For example, to set the 'EMP' table owned by 'SCOTT' so that index statistics are NOT collected as part of gathering table statistics, you could set the following table preference:exec dbms_stats.set_table_prefs(ownname=>'SCOTT',tabname=>'EMP',pname=>'CASCADE',pvalue=>'FALSE')SET_SCHEMA_PREFS
Enables you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for all existing objects in the specified schema.
This procedure calls SET_TABLE_PREFS for each of the tables in the specified schema.
Note that because it uses SET_TABLE_PREFS, calling SET_SCHEMA_PREFS does not affect any new objects created after it has been run - new objects will use the GLOBAL_PREF values for all parameters.Syntax
DBMS_STATS.SET_SCHEMA_PREFS (For example, to set the preferences such that the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered for all the objects in the 'SCOTT' schema, set:
ownname IN VARCHAR2,
pname IN VARCHAR2,
pvalue IN VARCHAR2);
exec dbms_stats.set_schema_prefs(ownname=>'SCOTT',pname=>'STALE_PERCENT',pvalue=>'5')SET_DATABASE_PREFS
Enables you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for all user-defined schemas in the database.
You can include system-owned schemas such as SYS and SYSTEM by setting the ADD_SYS parameter to TRUE.
This procedure calls SET_TABLE_PREFS for each table in the database
Note that because it uses SET_TABLE_PREFS, calling SET_DATABASE_PREFS does not affect any new objects created after it has been run - new objects use the GLOBAL_PREF values for all parameters.Syntax
DBMS_STATS.SET_DATABASE_PREFS (For example, to set preferences to gather column statistics (histograms) for any columns that have a skewed data distribution in all user-defined schemas including system-owned schemas, you could use:
pname IN VARCHAR2,
pvalue IN VARCHAR2,
add_sys IN BOOLEAN DEFAULT FALSE);
exec dbms_stats.set_database_prefs(pname=>'METHOD_OPT',pvalue=>'FOR ALL COLUMNS SIZE SKEWONLY',add_sys=>TRUE)
Available Parameters
CASCADE
Determines whether or not index statistics are collected as part of gathering table statistics.
Default: TRUE
Possible Values:
- TRUE
- FALSE
DEGREE
Determines degree of parallelism used for gathering statistics.
Default: NULL
NULL means: "use the table default value specified by the DEGREE clause in the CREATE TABLE or ALTER TABLE statement".
Other Possible Values
- n - An integer specifying the degree of parallelism for gathering statistics
- DBMS_STATS.DEFAULT_DEGREE specifies the default value based on the initialization parameters.
- AUTO_DEGREE value determines the degree of parallelism automatically.
Between 1 (serial execution) and DEFAULT_DEGREE
Note: When using DEGREE=>NULL, DEGREE=>n, or DEGREE=>DBMS_STATS.DEFAULT_DEGREE, the current implementation of DBMS_STATS may use serial execution if the size of the object does not warrant parallel execution.ESTIMATE_PERCENT
Determines the percentage of rows to estimate
Default: DBMS_STATS.AUTO_SAMPLE_SIZE
Possible Values:
- DBMS_STATS.AUTO_SAMPLE_SIZE
- Valid range is 0.000001 to 100
- NULL - compute will be used (100%)
Note: When NULL is unquoted, then the parameter will be set to default for ESTIMATE_PERCENT , that is DBMS_STATS.AUTO_SAMPLE_SIZE
When the value is in quotes as follows" 'NULL', the value of the parameter itself to NULL so that
the above example indicates that estimate_percent=null (i.e compute) as opposed to NULL without
quotes that would imply using the default for this parameterMETHOD_OPT
Controls column statistics collection and histogram creation.
Default: FOR ALL COLUMNS SIZE AUTO
Possible Values:
- FOR ALL COLUMNS SIZE AUTO
- FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
- FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
column is defined as column := column_name | extension name | extension
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
- column_name : Name of a column
- extension : Can be either a column group in the format of (column_name, colume_name [, ...]) or an expression
NO_INVALIDATE
Determines whether to invalidate dependent cursors or not
Default: DBMS_STATS.AUTO_INVALIDATE
Possible Values:
- DBMS_STATS.AUTO_INVALIDATE - Oracle decide when to invalidate dependent cursors.
- TRUE - Does not invalidate the dependent cursors
- FALSE - Invalidates dependent cursors
GRANULARITY
Determines granularity of statistics to collect (only pertinent if the table is partitioned).
Default: 'AUTO'
Possible Values:
- 'AUTO' - determines the granularity based on the partitioning type
- '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 even if it is a composite partitioned object.
- 'PARTITION '- gathers partition-level statistics
- 'SUBPARTITION' - gathers subpartition-level statistics.
Note: granularity of 'DEFAULT' is obsolete. This option gathers global and partition-level statistics. It is currently supported, but included in the documentation for legacy reasons only. Use the 'GLOBAL AND PARTITION' for this functionality.PUBLISH
Determines whether or not newly gathered statistics will be published once the gather job has completed.
From 11gR1 the user now has the ability to gather statistics but not publish them immediately. This allows the DBA to test the new statistics before publishing them.
Default: TRUE
Possible Values:
- TRUE
- FALSE
INCREMENTAL
Determines whether global statistics are gathered each time statistics on a partition are gathered (INCREMENTAL=>FALSE) or whether Oracle will update the global table statistics by scanning only the partitions that have been changed instead(INCREMENTAL=> TRUE)
Default: TRUE
Possible Values:
- TRUE
- FALSE
For more informatio see:
Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
13 Managing Optimizer Statistics
13.3.1.3.2 Incremental Statistics GatheringSTALE_PERCENT
Determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered.
Default: 10%
Possible Values:
Non-negative numbers.AUTOSTATS_TARGET
This parameter is applicable only for auto statistics collection and may only be set using the procedure: SET_GLOBAL_PREFS
The value of this parameter controls the objects considered for statistics collection
This parameter is only valid for
Default: 'AUTO'
Possible Values:
- 'AUTO' - Oracle decides for which objects to collect statistics
- 'ALL' - Statistics are collected for all objects in the system
- 'ORACLE' - Statistics are collected for all Oracle owned objects.
This option restricts the list of schemas for which the automatic stats
gathering job will gather statistics to a list of Oracle component system
E.g. SYS, SYSMAN, WMSYS and EXFSYS in a sample database
Resetting Parameters to Their Default Values
In order to reset the value of a parameter to the default value,use NULL (not in inverted comma) for the parameter value
Example:
exec dbms_stats.set_global_prefs('NO_INVALIDATE',NULL)
Retrieving values for Parameters
To determine the current settings for the parameters you can use the "DBMS_STATS.GET_PREFS" procedure as follows:
DBMS_STATS.GET_PREFS
This function returns the default value of the specified preference.
Syntax:
DBMS_STATS.GET_PREFS (When the owner(ownname) and table_name (tabname) are provided and a preference has been entered for the table, the function returns the preference as specified for the table. In all other cases it returns the global preference if it has been specified.
pname IN VARCHAR2,
ownname IN VARCHAR2 DEFAULT NULL,
tabname IN VARCHAR2 DEFAULT NULL)
Where no global preference has been set for the specified parameter , the default value is returned.
Examples
SQL> select dbms_stats.get_prefs('CASCADE','SCOTT','EMP') from dual;
DBMS_STATS.GET_PREFS('CASCADE','SCOTT','EMP')
--------------------------------------------------------------------------------
FALSE
SQL> select dbms_stats.get_prefs('STALE_PERCENT') from dual;
DBMS_STATS.GET_PREFS('STALE_PERCENT')
--------------------------------------------------------------------------------
10
New objects will use the GLOBAL_PREF values for all parameters.
Niciun comentariu:
Trimiteți un comentariu