統(tǒng)計(jì)信息DBMS_STATS包的一些參數(shù)解釋

發(fā)布時(shí)間：2020-08-12 11:13:09 來源：ITPUB博客閱讀：236 作者：lusklusklusk 欄目：關(guān)系型數(shù)據(jù)庫

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.
確定是否收集索引統(tǒng)計(jì)信息作為收集表統(tǒng)計(jì)信息的一部分。
默認(rèn)值是DBMS_STATS.AUTO_CASCADE,AUTO_CASCADE的默認(rèn)值是NULL，也就是false，因?yàn)開optimizer_compute_index_stats隱含參數(shù)指明了只有創(chuàng)建或重建索引時(shí)才會(huì)自動(dòng)收集索引的統(tǒng)計(jì)信息force index stats collection on index creation/rebuild

DEGREE - Determines degree of parallelism used for gathering statistics.
確定用于收集統(tǒng)計(jì)數(shù)據(jù)的并行度
默認(rèn)值是DBMS_STATS.AUTO_DEGREE，oracle基于基于對象的大小，CPU的數(shù)量和初始化參數(shù)來自動(dòng)決定的。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.
確定要估計(jì)的行的百分比。有效范圍是[0.000001,100]。使用常量DBMS_STATS.AUTO_SAMPLE_SIZE來擁有Oracle確定適當(dāng)?shù)臉颖玖恳垣@得良好的統(tǒng)計(jì)數(shù)據(jù)
默認(rèn)值是DBMS_STATS.AUTO_SAMPLE_SIZE，oracle使用自動(dòng)樣本大小算法，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.
控制列統(tǒng)計(jì)信息收集和直方圖創(chuàng)建。它接受以下選項(xiàng)之一，或兩者組合
size_clause使用整數(shù)：就是把所有數(shù)據(jù)分成XX份來采樣

size 1：就是把所有數(shù)據(jù)分成一份來采樣
總計(jì)1200行，如果數(shù)據(jù)分布不均勻，比如生日月份字段，一般12個(gè)月，但是前面1000行都是1月份，后面200行是2-12月份
如果分成一份，那每個(gè)月就是1/12

size 12：就是把所有數(shù)據(jù)分成12份來采樣
總計(jì)1200行，如果數(shù)據(jù)分布不均勻，比如生日月份字段，一般12個(gè)月，但是前面1000行都是1月份，后面200行是2-12月份
如果分成12份，那1月份就是10/12,其他月份都是(200/11)/1200

size 254：就是把所有數(shù)據(jù)分成254份來采樣

默認(rèn)值為ALL COLUMNS SIZE AUTO，收集所有數(shù)據(jù)傾斜且使用過的列

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.
該值控制正在收集統(tǒng)計(jì)信息的表的從屬游標(biāo)(即依賴該表的執(zhí)行計(jì)劃)的無效。不會(huì)使依賴關(guān)系無效光標(biāo)如果設(shè)置為TRUE。如果設(shè)置為FALSE，則該過程立即使依賴游標(biāo)無效。
true：當(dāng)收集完統(tǒng)計(jì)信息后，收集對象的cursor不會(huì)失效（不會(huì)產(chǎn)生新的執(zhí)行計(jì)劃）
false:當(dāng)收集完統(tǒng)計(jì)信息后，收集對象的cursor會(huì)立即失效（產(chǎn)生新的執(zhí)行計(jì)劃）
默認(rèn)值DBMS_STATS.AUTO_INVALIDATE，受參數(shù)_optimizer_invalidation_period控制，默認(rèn)是18000秒即5小時(shí)。

GRANULARITY - Determines granularity of statistics to collect (only pertinent if the table is partitioned)
確定要收集的統(tǒng)計(jì)信息的粒度（僅在表被分區(qū)時(shí)才相關(guān)）

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.
確定收集作業(yè)完成后是否新發(fā)布的統(tǒng)計(jì)信息將被發(fā)布。在Oracle Database 11g第1版（11.1）之前，一旦統(tǒng)計(jì)收集工作完成，新的統(tǒng)計(jì)數(shù)據(jù)就會(huì)自動(dòng)發(fā)布到字典表中。用戶現(xiàn)在有能力收集統(tǒng)計(jì)，但不立即發(fā)布。這樣DBA可以在發(fā)布新的統(tǒng)計(jì)信息之前測試新的統(tǒng)計(jì)信息

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.
確定在不進(jìn)行全表掃描的情況下，是否維護(hù)分區(qū)表的全局統(tǒng)計(jì)信息。使用分區(qū)表將新數(shù)據(jù)加載到新分區(qū)中非常常見。隨著添加新分區(qū)和加載數(shù)據(jù)，全局表統(tǒng)計(jì)信息需要保持最新狀態(tài)。如果滿足以下條件，則僅掃描已更改的分區(qū)而不是整個(gè)表的更新全局表統(tǒng)計(jì)信息：
分區(qū)表的INCREMENTAL值設(shè)置為TRUE;
分區(qū)表的PUBLISH值設(shè)置為TRUE;
在收集表上的統(tǒng)計(jì)信息時(shí)，用戶為ESTIMATE_PERCENT指定AUTO_SAMPLE_SIZE，并為GRANULARITY指定AUTO。
如果分區(qū)表的INCREMENTAL值設(shè)置為FALSE（默認(rèn)值），則使用全表掃描來維護(hù)全局統(tǒng)計(jì)信息更大的資源密集和耗時(shí)的大型桌面操作。

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%.
確定表中必須更改的行的百分比，該表之前的統(tǒng)計(jì)信息被視為過時(shí)且應(yīng)該被歸并。該stale_percent的有效域是非負(fù)數(shù)。默認(rèn)值為10%

向AI問一下細(xì)節(jié)

統(tǒng)計(jì)信息DBMS_STATS包的一些參數(shù)解釋

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽