转载请注明出处:http://blog.csdn.net/guoyjoe/article/details/11358457
正确答案:D
Oracle 11g新特性之统计信息收集
在以前的数据库版本中,当收集到新的优化器统计信息时会自动发布。在11g中,这仍然是默认的动作,但你多了一个选择,
你可以将最新的统计信息挂起,直到人为地发布它们。DBMS_STATS.GET_PREFS函数运行你检查“PUBLISH”属性,查看统计信息
是否已经自动发布。默认返回的值为TRUE,意味着已经自动发布了,而FALSE表示它还处于挂起状态,等待发布。
检查当前数据库的PUBLISH”属性:
gyj@OCM> col get_prefs for a50
gyj@OCM> SELECT DBMS_STATS.get_prefs('PUBLISH','SH','CUSTOMERS') get_prefs FROM dual;
GET_PREFS
--------------------------------------------------
TRUE
使用存储过程DBMS_STATS.SET_TABLE_PREFS可以重新设置“PUBLISH”属性,设为FALSE表示它还处于挂起状态,等待发布:
gyj@OCM> BEGIN
2 DBMS_STATS.SET_TABLE_PREFS('SH', 'CUSTOMERS', 'PUBLISH', 'false');
3 END;
4 /
PL/SQL procedure successfully completed.
gyj@OCM> 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 constantDBMS_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 assize_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
column is defined ascolumn := 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 isFOR 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 toTRUE . The procedure invalidates the dependent cursors
immediately if set toFALSE . UseDBMS_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 toTRUE ;
· PUBLISH value for the partitioned table is set toTRUE ;
· User specifiesAUTO_SAMPLE_SIZE forESTIMATE_PERCENT andAUTO forGRANULARITY when gathering statistics on the table.
If theINCREMENTAL value for the partitioned table was set toFALSE (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. IfNULL 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 theANALYZE
ANY
systemprivilege.
·All arguments are of typeVARCHAR2
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
技术交流:
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:dbathink@hotmail.com
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
分享到:
相关推荐
作用:DBMS_STATS.GATHER_TABLE_STATS统计表,列,索引的统计信息. DBMS_STATS.GATHER_TABLE_STATS的语法如下: DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_...
ORACLE数据库使用dbms_stats包手动收集关于表的、索引的统计信息。
DBMS STATS Package Fails with Error 'ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors'
简单的dbms_stats操作,简单的dbms_stats操作简单的dbms_stats操作
通过此版本可以把oracle时区版本调整到为最新版本,一般配合时区补丁使用 可以参考https://blog.csdn.net/weixin_43885834/article/details/105745901 https://download.csdn.net/download/weixin_43885834/12360971...
Oracle 18c bug 执行 DBMS_PDB.CHECK_PLUG_COMPATIBILITY报错_ITPUB博客.mhtml
Oracle统计分析-dbms_stats.pdf
How to Move from ANALYZE (using DBMS_UTILITY) to DBMS_STATS Is ANALYZE on the Data Dictionary Supported (TABLES OWNED BY SYS)?
NULL 博文链接:https://huanyue.iteye.com/blog/2095594
支持ArcGIS10.2版本的PostgreSQL_DBMS_for_windows_922,ESRI官方原版资源。
oracle dbms_lob
OCPOCA认证考试指南全册:Oracle Database 11g(1Z0-051,1Z0-052,1Z0-053) 共2部分:此为第002部分 基本信息 原书名: OCA/OCP Oracle Database 11g All-in-One Exam Guide with CD-ROM: Exams 1Z0-051, 1Z0-052...
dbms_obfuscation_toolkit加密解密数据
Oracle DOM编程 文档,有要的没 Start from toc.htm DBMS_XMLDOM DBMS_XMLPARSER DBMS_XMLQUERY
dbms_comp_advisor.getratio 预估压缩比例的存储过程脚本 11gR2以前使用,11gR2后可以使用系统自带的存储。
NULL 博文链接:https://duqiangcise.iteye.com/blog/648491
在以前查看SQL执行计划的时候,我都是使用set autotrace命令,不过现在看来,DBMS_XPLAN包给出了更加简化的获取和显示计划的方式。 这5个函数分别对应不同的显示计划的方式,DBMS_XPLAN包不仅可以获取解释计划,它还...
下面来讲一个Truncate表后进行恢复的例子: SQL> CREATE TABLE T_TRUNCATE AS SELECT * FROM TAB; Table created. SQL> SELECT COUNT(*) FROM T_TRUNCATE; COUNT(*) ---------- 14 SQL> ALTER SYSTEM...