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 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：

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:             tianlesoftware@gmail.com

Skype:            tianlesoftware

 

-------加群需要在備忘說明Oracle資料表空間和資料檔案的關係，否則拒絕申請----

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

DBA 超級群：63306533(滿);  DBA4 群：83829929   DBA5群： 142216823

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