Oracle DBMS_STATS 包和 Analyze 命令的区别-白红宇

Oracle DBMS_STATS 包和 Analyze 命令的区别

阅读量：5966 次

发布时间：2019-06-19

本文共 6647 字，大约阅读时间需要 22 分钟。

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

一. 当索引处于Monitoringusage时

官网对MonitoringIndex Usage 的说明如下：

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 view V$OBJECT_USAGE canbe queried for the index being monitored to see if the index has been used. Theview contains a USED column whose value is YES or NO,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 a MONITORING column (YES/NO) to indicate if usagemonitoring is currently active.

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

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

V$OBJECT_USAGE displaysstatistics 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 specify MONITORING USAGE, the V$OBJECT_USAGE view 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 next ALTER INDEX...MONITORING USAGE statement isissued, the view information is left unchanged.

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

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

测试如下：

SYS@anqing1(rac1)> desc tt;

Name Null? Type

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

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(19)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(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_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING

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

IDX_TT_ID TT 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_NAME TABLE_NAME MON USESTART_MONITORING END_MONITORING

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

IDX_TT_ID TT 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_NAME TABLE_NAME MON USESTART_MONITORING END_MONITORING

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

IDX_TT_ID TT 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_NAME TABLE_NAME MON USESTART_MONITORING END_MONITORING

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

IDX_TT_ID TT NO YES 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：

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意义不大