[转]ORACLE ANALYZE使用小结

ANALYZE的介绍

使用ANALYZE可以收集或删除对象的统计信息、验证对象的结构、标识表或cluster中的行迁移/行链接信息等。官方文档关于ANALYZE功能介绍如下：

· Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.

· Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).

· Identify migrated and chained rows of a table or cluster.

ANALYZE的使用

ANALYZE TABLE可以指定分析： 表、所有字段、所有索引字段、所有索引。 若不指定对应对象则表示全部都分析

#完全分析，采样100%

ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS;

ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS FOR TABLE FOR ALL COLUMNS FOR ALL INDEXES;

注意：上面两者是等价的。

#抽样分析，采样40%

ANALYZE TABLE TABLE_NAME ESTIMATE STATISTICS SAMPLE 40 PERCENT;

对于大表而言，收集统计信息相当于全表扫描，相当耗费时间和资源，可以用抽样估算法来收集统计信息，这样就可以节省时间和资源，如果不是要求要有精确数据的话，尽量采用抽样分析法。可以指定采样比例。

分析表统计信息

#仅仅生成表的statistics，不生成列的直方图信息

ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS FOR TABLE;

分析索引字段统计信息

#分析了表，并仅对表上的索引列产生histograms

ANALYZE TABLE TEST COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

分析指定列统计信息

ANALYZE TABLE TEST COMPUTE STATISTICS FOR COLUMNS COLUMN_NAME1, COLUMN_NAME2;

分析所有列统计信息

#分析表，同时生成所有列的histograms

ANALYZE TABLE TEST COMPUTE STATISTICS FOR ALL COLUMNS;

分析索引统计信息

#指定索引分析

SQL> ANALYZE INDEX INDEX_NAME COMPUTE STATISTICS ;

#分析所有索引

SQL> ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS FOR ALL INDEXES;

删除统计信息

#删除统计分析数据

ANALYZE TABLE DELETE STATISTICS;

#删除索引对应的统计信息

ANALYZE INDEX DELETE STATISTICS;

#注意：下面这种语法是错误的，见过有些文章有介绍这种方法。

ANALYZE TABLE DELETE STATISTICS FOR TABLE;

ANALYZE TABLE DELETE STATISTICS FOR ALL INDEXES;

ANALYZE TABLE DELETE STATISTICS FOR ALL INDEXEXED COLUMNS;

#注意：TRUNCATE命令不会修改数据的统计信息,也就是如果我们想让CBO利用合理利用数据的统计信息的时候,需要我们及时的使用ANALYZE命令或者dbms_stats重新统计数据的统计信息

下面我们以具体的例子来看看ANALYZE命令会分析、收集那些统计信息：
SQL> CREATE TABLE TEST
2 AS
3 SELECT * FROM DBA_OBJECTS;
SQL>
SQL> CREATE INDEX IX_TEST_N1 ON TEST( OBJECT_NAME);

执行sosi.sql检查统计信息情况，只有索引有相关统计信息。

如下截图所示，删除统计信息后，然后只分析、收集表的统计信息(注意和后续截图对比)

分析索引列的统计信息

SQL> ANALYZE TABLE TEST COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

分析所有列的统计信息

SQL> ANALYZE TABLE TEST COMPUTE STATISTICS FOR ALL COLUMNS;

注意：对所有列都生成直方图是没有意义的，只有对WHERE语句中用到的列生成直方图是有意义的

另外，之前在一篇博客中看到过：新执行的ANALYZE命令会抹掉之前ANALYZE的结果，但是个人测试并没有发现这种情况，不清楚是否跟版本有关系。个人也没有为此大量去测试过不同环境。

分析索引的统计信息

SQL> ANALYZE TABLE TEST COMPUTE STATISTICS FOR ALL INDEXES;

验证索引结构

SQL> ANALYZE INDEX IX_TEST_N1 VALIDATE STRUCTURE;

Index analyzed.

验证表结构

SQL> ANALYZE TABLE TEST VALIDATE STRUCTURE CASCADE;

Table analyzed.

ANALYZE使用注意事项

When analyzing a table, the database skips all domain indexes marked LOADING or FAILED

在收集表的统计信息时，会跳过标记为Loading 或Failed 的域索引（全文索引）。

ANALYZE的优势

Use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer:

To use the VALIDATE or LIST CHAINED ROWS clauses

To collect information on freelist blocks

ANALYZE可以分析、收集行迁移、行链接的数量，以及freelist blocks数量

当某个索引处于monitoring usage的时候，如果使用DBMS_STATS去分析表并且同时分析索引，会将该索引的v$object_usage.USED 设置为TRUE，导致监控了N天的可疑索引前功近弃。如果使用ANALYZE，索引的状态不会被设置为USE = TRUE。具体参考博客Oracle DBMS_STATS 包 和 Analyze 命令的区别

ANALYZE的限制

Analyzing tables is subject to the following restrictions:

You cannot use ANALYZE to collect statistics on data dictionary tables.

不能分析、收集数据字典表统计信息

You cannot use ANALYZE to collect statistics on an external table. Instead, you must use the DBMS_STATS package.

不能收集、分析外部表统计信息，只能用DBMS_STATS分析、收集外部表统计信息

You cannot use ANALYZE to collect default statistics on a temporary table. However, if you have already created an association between one or more columns of a temporary table and a user-defined statistics type, then you can use ANALYZE to collect the user-defined statistics on the temporary table.

不能使用ANLYZE分析、收集临时表的默认统计信息

You cannot compute or estimate statistics for the following column types: REF column types, varrays, nested tables, LOB column types (LOB column types are not analyzed, they are skipped), LONG column types, or object types. However, if a statistics type is associated with such a column, then Oracle Database collects user-defined statistics.

不能计算或者估算如下类型列的统计信息：REF column types, varrays, nested tables, LOB column types (LOB column types are not analyzed, they are skipped), LONG column types, or object types

You cannot analyze a domain index that is marked IN_PROGRESS or FAILED.

不能分析标记为失败或IN_PROGRESS的域索引

ANALYZE命令不能正确地收集分区表的统计信息，而DBMS_STATS包却可以。ANALYZE命令只会收集最低层次对象的统计信息，然后推导和汇总出高一级的统计信息，比如对于有子分区的分区表而言，它只会先收集子分区统计信息，然后再汇总，推导出分区或表级的统计信息。有的统计信息是可以从当前对象的下一级对象进行汇总后得到的，比如表的总行数，可以由各分区的行数相加得到。但有的统计信息则不能从下一级对象得到，比如列上的distinct值数量NUM_DISTINCT以及DESNSITY等。

不能并行收集统计信息。

其实了解过ANALYZE命令的限制后，基本上就清楚了ANALYZE与DBMS_STATS的区别，官方也是推荐使用DBMS_STATS包来收集统计信息，因为DBMS_STATS包收集的更广，并且更准确。另外，自从Oracle 8i以后ANALYZE具备”VALIDATE”验证功能，并且和DBMS_STATS包在功能上进行了划分，ANALYZE主要负责验证表和索引的结构以及链式行(chained and migrated rows)信息，DBMS_STATS包主要负责统计信息的管理。

参考资料：

https://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_4005.htm

https://docs.oracle.com/cd/B28359_01/server.111/b28310/general002.htm

https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/ANALYZE.html#GUID-535CE98E-2359-4147-839F-DCB3772C1B0E

https://blog.csdn.net/tianlesoftware/article/details/7055233