统计信息DBMS_STATS包的一些参数解释

千家信息网最后更新 2024年11月17日统计信息DBMS_STATS包的一些参数解释DBMS_STATS.SET_GLOBAL_PREFS (
pname IN VARCHAR2,
pvalue IN VARCHAR2);

DBMS_STATS.SET_DATABASE_PREFS (
pname IN VARCHAR2,
pvalue IN VARCHAR2,
add_sys IN BOOLEAN DEFAULT FALSE);

DBMS_STATS.SET_SCHEMA_PREFS (
ownname IN VARCHAR2,
pname IN VARCHAR2,
pvalue IN VARCHAR2);

DBMS_STATS.SET_TABLE_PREFS (
ownname IN VARCHAR2,
tabname IN VARCHAR2,
pname IN VARCHAR2,
pvalue IN VARCHAR2);

DBMS_STATS.Constants
Name TypeValue
AUTO_CASCADE BOOLEAN NULL
AUTO_DEGREE NUMBER 32768
AUTO_INVALIDATE BOOLEAN NULL
AUTO_SAMPLE_SIZE NUMBER 0


pname：Preference name. The default value for following preferences can be set:CASCADE、DEGREE、ESTIMATE_PERCENT、METHOD_OPT、NO_INVALIDATE、GRANULARITY、PUBLISH、INCREMENTAL、STALE_PERCENT

pvalue：Preference value. If NULL is specified, it will set the Oracle default value.


CASCADE - Determines whether or not index statistics are collected as part of gathering table statistics.
确定是否收集索引统计信息作为收集表统计信息的一部分。
默认值是DBMS_STATS.AUTO_CASCADE,AUTO_CASCADE的默认值是NULL，也就是false，因为_optimizer_compute_index_stats隐含参数指明了只有创建或重建索引时才会自动收集索引的统计信息force index stats collection on index creation/rebuild

DEGREE - Determines degree of parallelism used for gathering statistics.
确定用于收集统计数据的并行度
默认值是DBMS_STATS.AUTO_DEGREE，oracle基于基于对象的大小，CPU的数量和初始化参数来自动决定的。based on size of the object, number of CPUs and initialization parameters

ESTIMATE_PERCENT - Determines the percentage of rows to estimate. The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.
确定要估计的行的百分比。 有效范围是[0.000001,100]。 使用常量DBMS_STATS.AUTO_SAMPLE_SIZE来拥有Oracle确定适当的样本量以获得良好的统计数据
默认值是DBMS_STATS.AUTO_SAMPLE_SIZE，oracle使用自动样本大小算法，Indicates that auto-sample size algorithms should be used


METHOD_OPT - Controls column statistics collection and histogram creation. It accepts either of the following options, or both in combination:
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
The default is FOR ALL COLUMNS SIZE AUTO.
控制列统计信息收集和直方图创建。 它接受以下选项之一，或两者组合
size_clause使用整数：就是把所有数据分成XX份来采样

size 1：就是把所有数据分成一份来采样
总计1200行，如果数据分布不均匀，比如生日月份字段，一般12个月，但是前面1000行都是1月份，后面200行是2-12月份
如果分成一份，那每个月就是1/12

size 12：就是把所有数据分成12份来采样
总计1200行，如果数据分布不均匀，比如生日月份字段，一般12个月，但是前面1000行都是1月份，后面200行是2-12月份
如果分成12份，那1月份就是10/12,其他月份都是(200/11)/1200

size 254：就是把所有数据分成254份来采样

默认值为ALL COLUMNS SIZE AUTO，收集所有数据倾斜且使用过的列


NO_INVALIDATE - The value controls the invalidation of dependent cursors of the tables for which statistics are being gathered. Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. Use DBMS_STATS.AUTO_INVALIDATE to have Oracle decide when to invalidate dependent cursors. This is the default.
该值控制正在收集统计信息的表的从属游标(即依赖该表的执行计划)的无效。 不会使依赖关系无效光标如果设置为TRUE。 如果设置为FALSE，则该过程立即使依赖游标无效。
true：当收集完统计信息后，收集对象的cursor不会失效（不会产生新的执行计划）
false:当收集完统计信息后，收集对象的cursor会立即失效（产生新的执行计划）
默认值DBMS_STATS.AUTO_INVALIDATE，受参数_optimizer_invalidation_period控制，默认是18000秒即5小时。

GRANULARITY - Determines granularity of statistics to collect (only pertinent if the table is partitioned)
确定要收集的统计信息的粒度（仅在表被分区时才相关）

PUBLISH - Determines whether or not newly gathered statistics will be published once the gather job has completed. Prior to Oracle Database 11g, Release 1 (11.1), once a statistic gathering job completed the new statistics were automatically published into the dictionary tables. 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.
确定收集作业完成后是否新发布的统计信息将被发布。 在Oracle Database 11g第1版（11.1）之前，一旦统计收集工作完成，新的统计数据就会自动发布到字典表中。 用户现在有能力收集统计，但不立即发布。 这样DBA可以在发布新的统计信息之前测试新的统计信息

INCREMENTAL - Determines whether or not the global statistics of a partitioned table will be maintained without doing a full table scan. With partitioned tables it is very common to load new data into a new partition. As new partitions are added and data loaded, the global table statistics need to be kept up to date. Oracle will update the global table statistics by scanning only the partitions that have been changed instead of the entire table if the following conditions hold:
INCREMENTAL value for the partitioned table is set to TRUE;
PUBLISH value for the partitioned table is set to TRUE;
User specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.
If the INCREMENTAL value for the partitioned table was set to FALSE (default value), a full table scan is used to maintain the global statistics which is a much more resource intensive and time-consuming operation for large tables.
确定在不进行全表扫描的情况下，是否维护分区表的全局统计信息。使用分区表将新数据加载到新分区中非常常见。 随着添加新分区和加载数据，全局表统计信息需要保持最新状态。如果满足以下条件，则仅扫描已更改的分区而不是整个表的更新全局表统计信息：
分区表的INCREMENTAL值设置为TRUE;
分区表的PUBLISH值设置为TRUE;
在收集表上的统计信息时，用户为ESTIMATE_PERCENT指定AUTO_SAMPLE_SIZE，并为GRANULARITY指定AUTO。
如果分区表的INCREMENTAL值设置为FALSE（默认值），则使用全表扫描来维护全局统计信息更大的资源密集和耗时的大型桌面操作。

STALE_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. The valid domain for stale_percent is non-negative numbers. The default value is 10%.
确定表中必须更改的行的百分比，该表之前的统计信息被视为过时且应该被归并。该stale_percent的有效域是非负数。 默认值为10%