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 <TABLE_NAME> DELETE STATISTICS;

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

ANALYZE INDEX <INDEX_NAME> DELETE STATISTICS;

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

ANALYZE TABLE <TABLE_NAME> DELETE STATISTICS FOR TABLE;

ANALYZE TABLE <TABLE_NAME> DELETE STATISTICS FOR ALL INDEXES;

ANALYZE TABLE <TABLE_NAME> DELETE STATISTICS FOR ALL INDEXEXED COLUMNS;

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

下面我们以具体的例子来看看ANALYZE命令会分析、收集那些统计信息：

SQL> CREATE TABLE TEST

代码100分  2  AS

  3  SELECT * FROM DBA_OBJECTS;

代码100分SQL> 

SQL> CREATE INDEX IX_TEST_N1 ON TEST( OBJECT_NAME);

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

***********

Table Level

***********

Table                   Number                 Empty Average    Chain Average Global User           Sample Date

Name                   of Rows   Blocks       Blocks   Space    Count Row Len Stats  Stats            Size MM-DD-YYYY

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

TEST                                                                          NO     NO

Column                    Column                       Distinct          Number     Number Global User           Sample Date

Name                      Details                        Values Density Buckets      Nulls Stats  Stats            Size MM-DD-YYYY

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

OWNER                     VARCHAR2(30)                                                     NO     NO

OBJECT_NAME               VARCHAR2(128)                                                    NO     NO

SUBOBJECT_NAME            VARCHAR2(30)                                                     NO     NO

OBJECT_ID                 NUMBER(22)                                                       NO     NO

DATA_OBJECT_ID            NUMBER(22)                                                       NO     NO

OBJECT_TYPE               VARCHAR2(19)                                                     NO     NO

CREATED                   DATE                                                             NO     NO

LAST_DDL_TIME             DATE                                                             NO     NO

TIMESTAMP                 VARCHAR2(19)                                                     NO     NO

STATUS                    VARCHAR2(7)                                                      NO     NO

TEMPORARY                 VARCHAR2(1)                                                      NO     NO

GENERATED                 VARCHAR2(1)                                                      NO     NO

SECONDARY                 VARCHAR2(1)                                                      NO     NO

NAMESPACE                 NUMBER(22)                                                       NO     NO

EDITION_NAME              VARCHAR2(30)                                                     NO     NO

                              B                                        Average     Average

Index                      Tree Leaf       Distinct         Number Leaf Blocks Data Blocks      Cluster Global User    Sample Date

Name            Unique    Level Blks           Keys        of Rows     Per Key     Per Key       Factor Stats  Stats     Size MM-DD-YYYY

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

IX_TEST_N1      NONUNIQUE     2  359         44,089         72,408           1           1       37,058 NO     NO      72,408 07-30-2018

Index           Column                     Col Column

Name            Name                       Pos Details

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

IX_TEST_N1      OBJECT_NAME                  1 VARCHAR2(128)

***************

Partition Level

***************

***************

SubPartition Level

***************

SQL> ANALYZE TABLE TEST DELETE STATISTICS;

SQL> ANALYZE TABLE TEST COMPUTE STATISTICS FOR TABLE; 

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