Oracle DBMS_STATS 包 和 Analyze 命令的区别

对于DBA 来说，这2个命令都不陌生，用这2个命令都可以收集表的统计信息。 这篇主要看一下这2个命令的区别。

相关内容参考：

Oracle Analyze 命令 详解

http://blog.csdn.net/tianlesoftware/article/details/7055164

Oracle Statistic 统计信息 小结

http://blog.csdn.net/tianlesoftware/article/details/4668723

Oracle 判断 并 手动收集 统计信息 脚本

http://blog.csdn.net/tianlesoftware/article/details/6445868

Oracle 分析及动态采样

http://blog.csdn.net/tianlesoftware/article/details/5845028

一. 当索引处于Monitoringusage时

官网对MonitoringIndex Usage 的说明如下：

http://docs.oracle.com/cd/E11882_01/server.112/e25494/indexes004.htm#ADMIN11735

Oracle Databaseprovides a means of monitoring indexes to determine whether they are beingused. If an index is not being used, then it can be dropped, eliminatingunnecessary statement overhead.

--DB 提供监控索引的方法，来判断索引是否使用，如果没有使用，可以drop。

To start monitoring the usage of an index,issue this statement:

ALTER INDEX indexMONITORING USAGE;

--开始监控索引

Later, issue the following statement tostop the monitoring:

ALTER INDEX indexNOMONITORING USAGE;

--停止监控索引

The viewV$OBJECT_USAGEcanbe queried for the index being monitored to see if the index has been used. Theview contains aUSEDcolumn whose value isYESorNO,depending upon if the index has been used within the time period beingmonitored. The view also contains the start and stop times of the monitoringperiod, and aMONITORINGcolumn (YES/NO) to indicate if usagemonitoring is currently active.

--可以通过v$object_usage 视图来查看monitor 的情况，其中v$object_usage视图有一个used 字段，其之表示索引是否使用。

官网对v$object_usage 视图的解释：

V$OBJECT_USAGEdisplaysstatistics about index usage gathered from the database for the indexes ownedby the current user. You can use this view to monitor index usage. All indexesthat have been used at least once can be monitored and displayed in this view.

Each time thatyou specifyMONITORING USAGE, theV$OBJECT_USAGEview is resetfor the specified index. The previous usage information is cleared or reset,and a new start time is recorded. When you specifyNOMONITORING USAGE, nofurther monitoring is performed, and the end time is recorded for the monitoringperiod. Until the nextALTER INDEX...MONITORING USAGEstatement isissued, the view information is left unchanged.

--每次指定某个索引，v$object_usage 视图中有关该视图的信息都将被重置。 先前收集的信息也就被clear 或者reset，同时从一个新的时间开始记录，当我们停止监控时，就记录结束监控的时间，直到下一次监控。

以上对Monitoring index的说明，只为证明一点：dbms_stats 和 analyze对monitoring index 的影响。

测试如下：

SYS@anqing1(rac1)> desc tt;

NameNull? Type

------------------------------------------------- ----------------------------

OWNERVARCHAR2(30)

OBJECT_NAMEVARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_IDNUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPEVARCHAR2(19)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMPVARCHAR2(19)

STATUSVARCHAR2(7)

TEMPORARYVARCHAR2(1)

GENERATEDVARCHAR2(1)

SECONDARYVARCHAR2(1)

SYS@anqing1(rac1)> select * from dba_indexes where table_name='TT';

no rows selected

SYS@anqing1(rac1)> create index idx_tt_id on tt(object_id);

Index created.

SYS@anqing1(rac1)> select index_name,table_name from dba_indexes where table_name='TT';

INDEX_NAME TABLE_NAME

------------------------------------------------------------

IDX_TT_ID TT

--查看v$object_usage:

SYS@anqing1(rac1)> select * from v$object_usage;

no rows selected

--启动监控：

SYS@anqing1(rac1)> alter index idx_tt_idmonitoring usage;

Index altered.

--查看监控：

SYS@anqing1(rac1)> col table_name formata12

SYS@anqing1(rac1)> col index_name formata12

SYS@anqing1(rac1)> select * fromv$object_usage;

INDEX_NAMETABLE_NAME MON USE START_MONITORING END_MONITORING

------------ ------------ --- ---------------------- -------------------

IDX_TT_IDTT YES NO 12/08/2011 13:48:56

--注意这里的USE是NO.

--使用Analyze来收集一下表的统计信息：

SYS@anqing1(rac1)> analyze table tt compute statistics;

Table analyzed.

SYS@anqing1(rac1)> select * from v$object_usage;

INDEX_NAMETABLE_NAME MON USESTART_MONITORING END_MONITORING

------------ ------------ --- ---------------------- -------------------

IDX_TT_IDTT YES NO 12/08/2011 13:48:56

--这里的USE 还是为NO。

--使用DBMS_STATS包收集统计信息：

SYS@anqing1(rac1)> exec dbms_stats.gather_table_stats('SYS','TT');

PL/SQL procedure successfully completed.

SYS@anqing1(rac1)> select * fromv$object_usage;

INDEX_NAMETABLE_NAME MON USESTART_MONITORING END_MONITORING

------------ ------------ --- ---------------------- -------------------

IDX_TT_IDTT YES YES 12/08/201113:48:56

--注意这里的USE变成了YES。

--现在停止监控索引

SYS@anqing1(rac1)> alter index idx_tt_idnomonitoring usage;

Index altered.

SYS@anqing1(rac1)> col table_name formata12

SYS@anqing1(rac1)> col index_name formata12

SYS@anqing1(rac1)> select * fromv$object_usage;

INDEX_NAMETABLE_NAME MON USESTART_MONITORING END_MONITORING

------------ ------------ --- ---------------------- -------------------

IDX_TT_IDTT NOYES 12/08/2011 13:48:56 12/08/2011 13:59:48

--这个就是我们monitoringindex 的最终状态。

小结一下：

当某个索引处于monitoring usage的时候，如果使用dbms_stats去分析表并且同时分析索引，会将该索引的v$object_usage.USED 设置为TRUE，导致监控了N天的可疑索引前功近弃。如果使用analyze，索引的状态不会被设置为USE = TRUE

二. DBMS_STATS 和 Analyze 收集信息的不同

这部分内容直接转自ML的blog：

http://www.oracledatabase12g.com/archives/what-dbms_stats-can-not-but-analyze-can-do.html

DBMS_STATS仅仅收集对CBO有用的统计信息，所以一些空间使用情况信息和使用FreeList管理的信息都不会被收集，这些信息包括:

If statistics unrelated to the cost basedoptimizer are required, then these must still be collected using the theANALYZE command. These statistics include:

Space Usage information :

EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT

Information on freelistblocks

AVG_SPACE_FREELIST_BLOCKS,NUM_FREELIST_BLOCKS

--这部分内容就是我们之前Analyze 里面提到的部分。

因为以上信息对于CBO计算成本并没有帮助，所以DBMS_STATS也就无意也无法收集它们，但是Analyze命令还是可以做到收集以上这些信息。

此外因为CBO其实并不会参考Cluster类型对象的统计信息来计算Cost成本，而是使用cluster中各个表的统计信息(DBMS_STATS does not gathercluster statistics, but you can use DBMS_STATS to gather statistics on theindividual tables instead of the whole cluster. )

所以DBMS_STATS也不支持收集Cluster的统计信息。

Oracle公司已经明确了Analyze作为”validate”验证命令的功能定位，且很多内部的工具和脚本仍在使用AnalyzeTable/Cluster/Index的特有功能，所以可以预期Analyze命令在未来的一段时间内也不会被废弃。

DBMS_STATS无法实现，而传统的Analyze命令可以做到的功能：

（1）Analyze validate structure 验证表、簇、索引的结构的完整性，使用cascade选项可以交叉验证表和索引的数据完整，online选项可以做到在线验证

（2）Analyze list chained rows 收集表、簇上的Migrated and Chained Rows链式或迁移行信息

（3）Analyze table compute statistics 收集表上的 empty_blocks、avg_space 空间使用信息

（4）Analyze Cluster 收集簇的信息，其实cluster上唯一可统计的信息是DBA_CLUSTERS.AVG_BLOCKS_PER_KEY(Numberof blocks in the table divided by number of cluster keys) ， 所以收集cluster的statistics意义不大

其他的一些区别：

1.对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。

（1）可以并行进行，对多个用户，多个Table
（2）可以得到整个分区表的数据和单个分区的数据。
（3）可以在不同级别上ComputeStatistics：单个分区，子分区，全表，所有分区。

（4）可以倒出统计信息
（5）可以用户自动收集统计信息

2. DBMS_STATS的缺点
（1）不能Validate Structure

（2）不能收集CHAINEDROWS, 不能收集CLUSTERTABLE的信息，这两个仍旧需要使用Analyze语句。
（3）DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True

3. 对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息.

-------------------------------------------------------------------------------------------------------

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

Blog: http://blog.csdn.net/tianlesoftware

Weibo: http://weibo.com/tianlesoftware

Email: [e-mail]

Skype: tianlesoftware

-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----

DBA1 群：62697716(满); DBA2 群：62697977(满)DBA3 群：62697850(满)

DBA 超级群：63306533(满); DBA4 群：83829929 DBA5群： 142216823

DBA6 群：158654907 DBA7 群：172855474 DBA8群：102954821