[每日一題] 11gOCP 1z0-052 :2013-09-6 DBMS_STATS.SET_TABLE_PREFS（）使用...............................A37

轉載請注明出處:http://blog.csdn.net/guoyjoe/article/details/11358457

正确答案:D

Oracle 11g新特性之統計資訊收集

   在以前的資料庫版本中，當收集到新的優化器統計資訊時會自動釋出。在11g中，這仍然是預設的動作，但你多了一個選擇，

你可以将最新的統計資訊挂起，直到人為地釋出它們。DBMS_STATS.GET_PREFS函數運作你檢查“PUBLISH”屬性，檢視統計資訊

是否已經自動釋出。預設傳回的值為TRUE，意味着已經自動釋出了，而FALSE表示它還處于挂起狀态，等待釋出。

  檢查目前資料庫的PUBLISH”屬性:

[email protected]> col get_prefs for a50
[email protected]> SELECT DBMS_STATS.get_prefs('PUBLISH','SH','CUSTOMERS') get_prefs FROM dual;

GET_PREFS
--------------------------------------------------
TRUE
           

使用存儲過程DBMS_STATS.SET_TABLE_PREFS可以重新設定“PUBLISH”屬性,設為FALSE表示它還處于挂起狀态，等待釋出:

[email protected]> BEGIN
  2  DBMS_STATS.SET_TABLE_PREFS('SH', 'CUSTOMERS', 'PUBLISH', 'false');
  3  END;
  4  /

PL/SQL procedure successfully completed.

[email protected]> SELECT DBMS_STATS.get_prefs('PUBLISH','SH','CUSTOMERS') get_prefs FROM dual;

GET_PREFS
--------------------------------------------------
FALSE
           

參考官方文檔:

SET_TABLE_PREFS Procedure

This procedure isused to set the statistics preferences of the specified table in the specifiedschema.

Syntax

DBMS_STATS.SET_TABLE_PREFS (      

    ownname    IN  VARCHAR2,      

    tabname    IN  VARCHAR2,      

    pname      IN  VARCHAR2,      

    pvalue     IN  VARCHAR2);      

Parameters

Table 141-93SET_TABLE_PREFS Procedure Parameters

Parameter	Description
ownname	Owner name
tabname	Table name
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
.	CASCADE  - Determines whether or not index statistics are collected as part of gathering table statistics .
.	DEGREE  - Determines degree of parallelism used for gathering statistics.
.	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.
.	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 .
.	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.
.	GRANULARITY  - Determines granularity of statistics to collect (only pertinent if the table is partitioned). 'ALL'  - Gathers all (subpartition, partition, and global) statistics 'AUTO' - Determines the granularity based on the partitioning type. This is the default value. 'DEFAULT'  - Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the ' GLOBAL AND PARTITION ' for this functionality. Note that the default value is now ' AUTO '. '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.
.	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.
.	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.
.	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 default value is 10%.
pvalue	Preference value. If  NULL  is specified, it will set the Oracle default value.

Exceptions

ORA-20000: Object doesnot exist or insufficient privileges

ORA-20001: Invalid orillegal input values

Usage Notes

·        To run this procedure, you need to connect as owner of the tableor should have the 

ANALYZE

ANY

 systemprivilege.

·        All arguments are of type 

VARCHAR2

 and values are enclosed in quotes, even when they representnumbers.

Examples

DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'CASCADE', 'DBMS_STATS.AUTO_CASCADE');      

DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES','ESTIMATE_PERCENT','9');      

DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'DEGREE','99');      

 
QQ：252803295

技術交流QQ群：
DSI&Core Search  Ⅰ 群：127149411（2000人技術群:未滿）
DSI&Core Search  Ⅱ 群：177089463（1000人技術群:未滿）
DSI&Core Search  Ⅲ 群：284596437（500人技術群:未滿）
DSI&Core Search  Ⅳ 群：192136702（500人技術群:未滿）
DSI&Core Search  Ⅴ 群：285030382（500人閑聊群:未滿）








MAIL：[email protected]

BLOG： http://blog.csdn.net/guoyjoe

WEIBO：http://weibo.com/guoyJoe0218

ITPUB： http://www.itpub.net/space-uid-28460966.html

OCM：   http://education.oracle.com/education/otn/YGuo.HTM