1.對(duì)索引結(jié)構(gòu)進(jìn)行統(tǒng)計(jì)
1.1analyze index indexname validate structure
聯(lián)機(jī)文檔的原文:Specify VALIDATE STRUCTURE to validate the structure of the analyzed object.
The statistics collected by this clause are not used by the Oracle Database optimizer, as are statistics collected by the COMPUTE STATISTICS and ESTIMATE STATISTICS clauses.
For an index, Oracle Database verifies the integrity of each data block in the index and checks for block corruption. This clause does not confirm that each row in the table has an index entry or that each index entry points to a row in the table. You can perform these operations by validating the structure of the table with the CASCADE clause.
這個(gè)命令主要是用來分析索引的數(shù)據(jù)塊是否有壞塊，以及根據(jù)分析得到的數(shù)據(jù)（存放在index_stats）來判斷索引是否需要重新建立。
1.2 validate structure有二中模式：online, offline， 默認(rèn)是offline模式。
聯(lián)機(jī)文檔原文:
Specify ONLINE to enable Oracle Database to run the validation while DML operations are ongoing within the object. The database reduces the amount of validation performed to allow for concurrency.
Specify OFFLINE, to maximize the amount of validation performed. This setting prevents INSERT, UPDATE, and DELETE statements from concurrently accessing the object during validation but allows queries. This is the default.
Restriction on ONLINE
You cannot specify ONLINE when analyzing a cluster or index.
Note:
When you validate the structure of an object ONLINE, Oracle Database does not collect any statistics, as it does when you validate the structure of the object OFFLINE.
以offline模式分析時(shí)，會(huì)對(duì)表加一個(gè)4級(jí)別的鎖（表共享），對(duì)run系統(tǒng)可能造成一定的影響。
而online模式則沒有表lock的影響，但當(dāng)以online模式分析時(shí)， 在視圖index_stats沒有統(tǒng)計(jì)信息。
2.對(duì)索引狀態(tài)進(jìn)行統(tǒng)計(jì)
analyze index indexname compute statistics
聯(lián)機(jī)文檔原文:
COMPUTE STATISTICS instructs Oracle Database to compute exact statistics about the analyzed object and store them in the data dictionary. When you analyze a table, both table and column statistics are collected.
Both computed and estimated statistics are used by the Oracle Database optimizer to choose the execution plan for SQL statements that access analyzed objects. These statistics may also be useful to application developers who write such statements.
Column statistics appear in the data dictionary views USER_TAB_COLUMNS, ALL_TAB_COLUMNS, and DBA_TAB_COLUMNS. Histograms appear in the data dictionary views USER_TAB_HISTOGRAMS, DBA_TAB_HISTOGRAMS, and ALL_TAB_HISTOGRAMS; USER_PART_HISTOGRAMS, DBA_PART_HISTOGRAMS, and ALL_PART_HISTOGRAMS; and USER_SUBPART_HISTOGRAMS, DBA_SUBPART_HISTOGRAMS, and ALL_SUBPART_HISTOGRAMS.
總的來說，compute statistics是用來統(tǒng)計(jì)index的分析信息，來為CBO服務(wù)的。9i之后推薦使用dbms_stats。
ps:
for table的統(tǒng)計(jì)信息存在于視圖：user_tables 、all_tables、dba_tables
for all indexes的統(tǒng)計(jì)信息存在于視圖: user_indexes 、all_indexes、dba_indexes
for all columns的統(tǒng)計(jì)信息存在于試圖：user_tab_columns、all_tab_columns、dba_tab_columns
3.一些sample
3.1使用validate structure分析一個(gè)索引是否需要重建
(1)analyze index index_name validate structure;
(2)select t.del_lf_rows_len /t.lf_blk_len from index_stats t where t.name = &index_name;
(3)如果結(jié)果大于20%,index就需要被rebuild了。
4.對(duì)于分區(qū)表，建議使用DBMS_STATS，而不是使用Analyze語句。
(1)可以并行進(jìn)行，對(duì)多個(gè)用戶，多個(gè)Table
(2)可以得到整個(gè)分區(qū)表的數(shù)據(jù)和單個(gè)分區(qū)的數(shù)據(jù)。
(3)可以在不同級(jí)別上Compute Statistics：?jiǎn)蝹€(gè)分區(qū)，子分區(qū)，全表，所有分區(qū)
(4)可以導(dǎo)出統(tǒng)計(jì)信息
(5)可以用戶自動(dòng)收集統(tǒng)計(jì)信息
5.DBMS_STATS的缺點(diǎn)
(1)不能Validate Structure
(2)不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，這兩個(gè)仍舊需要使用Analyze語句。
(3)DBMS_STATS 默認(rèn)不對(duì)索引進(jìn)行Analyze，因?yàn)槟J(rèn)Cascade是False，需要手工指定為True
6.對(duì)于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS來收集信息。