analyze 命令 -一个渣渣

官网链接：

http://docs.oracle.com/cd/E11882_01/server.112/e25494/general002.htm#ADMIN11524

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_4005.htm#SQLRF01105

1、 analyze 命令的作用：

You analyze a schema object (table, index, or cluster) to:

Collect and manage statistics for it
Verify the validity of its storage format
Identify migrated and chained rows of a table or cluster

收集管理统计信息、验证存储格式的正确、是否行迁移

Do not use the COMPUTE and ESTIMATE clauses of ANALYZE to collect optimizer statistics

不要用analyze的compute 和estimate收集优化统计信息，analyze命令已经过时，一般用dbms_stats 来收集优化统计信息，dbms_stats命令可以使用parallel，对分区对象收集全局统计信息， fine tune your statistics collection in other ways. 基于成本的优化器取决于统计信息

analyze命令收集的统计信息和基于成本优化器所需统计信息无关，下列情况用analyze比dbms_stats要好：

To use the VALIDATE or LIST CHAINED ROWS clauses
To collect information on freelist blocks

2、什么情况下用analyze命令

（1）收集或者删除索引，分区索引，表，分区表，cluster的统计信息，

analyze收集表的统计信息都放在 all_tables dba_tables user_tables 表里面了，表里的统计信息是指 NUM_ROWS、BLOCKS 、EMPTY_BLOCKS 、AVG_SPACE、CHAIN_COUNT、AVG_ROW_LEN

analyze table有如下限制：你不能收集数据字典的统计信息

你不能收集 external table的统计信息，但是可以用dbms_stats收集

analyze 不能收集临时表的默认统计信息

你不能compute or estimate , REF column types, varrays, nested tables, LOB column types 类型的统计信息

analyze 收集索引里的统计信息都放在 USER_INDEXES 、dba_indexes 、ALL_INDEXES表里，表里的统计信息指BLEVEL、LEAF_BLOCKS、DISTINCT_KEYS、AVG_LEAF_BLOCKS_PER_KEY、AVG_DATA_BLOCKS_PER_KEY、CLUSTERING_FACTOR

cluster 的统计信息都放在 ALL_CLUSTERS,USER_CLUSTERS, andDBA_CLUSTERS

(2)验证索引，分区索引，表，分区表索引组织表对象的结构

（3）确定表和cluster 的行连接或者迁移

3、什么情况下用dbms_stats命令

一般用dbms_stats 来收集优化统计信息，dbms_stats命令可以使用parallel，对分区对象收集全局统计信息， fine tune your statistics collection in other ways. 基于成本的优化器取决于统计信息

GATHER_INDEX_STATS

GATHER_TABLE_STATS

GATHER_SCHEMA_STATS

GATHER_DATABASE_STATS

4 analyze语法

analyze::=

Description of the illustration analyze.gif

ANALYZE
  { { TABLE [ schema. ] table
    | INDEX [ schema. ] index
    } [ partition_extension_clause ]
  | CLUSTER [ schema. ] cluster
  }
  { validation_clauses
  | LIST CHAINED ROWS [ into_clause ]
  | DELETE [ SYSTEM ] STATISTICS
  } ;

partition_extension_clause::=

Description of the illustration partition_extension_clause.gif

validation_clauses::=

Description of the illustration validation_clauses.gif

into_clause::=

Description of the illustration into_clause.gif

ANALYZE TABLE tablenameCOMPUTE|ESTIMATE|DELETE STATISTICS

ANALYZE INDEX indexnameCOMPUTE|ESTIMATE|DELETE STATISTICS

ANALYZE CLUSTER clusternameCOMPUTE|ESTIMATE|DELETE STATISTICS

ANALYZE INDEX <index_name>

[PARTITION <partition_name>]

[SUBPARTITION <subpartition_name>]

VALIDATE STRUCTURE CASCADE

INTO <table_name> <OFFLINE |ONLINE>

ANALYZE TABLE <table_name>

[PARTITION <partition_name>]

[SUBPARTITION <subpartition_name>]

VALIDATE STRUCTURE CASCADE

[INTO <table_name>] <OFFLINE |ONLINE>

ANALYZE CLUSTER <cluster_name>VALIDATE STRUCTURE CASCADE

INTO <table_name> <OFFLINE |ONLINE>

When you analyze an index from which a substantial number of rows has been deleted, Oracle Database sometimes executes aCOMPUTE statistics operation (which can entail a full table scan) even if you request anESTIMATE statistics operation. Such an operation can be quite time consuming.

当我们收集统计信息时，如果表或索引上有大量的数据被删除，那么如果采用compute或者 estimage 来收集，可以会进行full table scan，因此会使用很多的时间

To verify the integrity of the structure of a table, index, cluster, or materialized view, use theANALYZE statement with theVALIDATE STRUCTURE option. If the structure is valid, no error is returned. However, if the structure is corrupt, you receive an error message.

为了验证表，索引，cluster 物化视图的结构完整性，可以用analyze语句加上validate structure 选项来验证，如果是有效的则不返回错误，如果结构有问题，就是返回错误

Specify VALIDATE REF UPDATE to validate theREF values in the specified table, check the rowid portion in each REF, compare it with the true rowid, and correct it, if necessary. You can use this clause only when analyzing a table.

指定 VALIDATE REF UPDATE 去验证指定表的ref值，检测每个ref的rowid和它正在的rowid进行比较，如果有必要修改，就会修改，这个语句只能在分析表的时候用

If the owner of the table does not have SELECT object privilege on the referenced objects, then Oracle Database will consider them invalid and set them to null. Subsequently theseREF values will not be available in a query, even if it is issued by a user with appropriate privileges on the objects.

如果一个表的用户对依赖对象没有select权限，那么oracle会认为他们是不合法的，并且设置成null，随后在查询时ref值也不可用，即使对此对象有合适的权限。

SET DANGLING TO NULL SETDANGLING TO NULL sets to null any REF values (whether or not scoped) in the specified table that are found to point to an invalid or nonexistent object.

此处还是有点不是很明白？？？

ANALYZE TABLE emp VALIDATE STRUCTURE;

VALIDATE STRUCTURE 用来验证分析对象结构的合法性，此统计信息的收集不是给优化器使用

对于表，数据库验证了数据块和行的完整性，对于索引组织表，数据库也会生成主键的压缩统计信息

对于cluster，数据库自动验证cluster table 结构的合理性

对于分区表，数据库也会验证每一行属于正确的分区，如果某行分到不正确的分区，她的rowid就会插入到INVALID_ROWS表中；

对于临时表，数据库会在当前session中检测表和索引的合法性

对于索引，数据库会验证每个索引block 的完整性和block 是否损坏。这个命令不会确实每个表的row 是否和索引的row 匹配。你可以使用cascade来验证

oracle为每个普通索引计算压缩统计信息，并且存储索引的统计信息到INDEX_STATS andINDEX_HISTOGRAM

ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;

CASCADE 用来验证表或者cluster以及表或cluster上的相关索引信息的合法性，一般cascade会进行一个完整的验证合法性，需要消耗更多的资源

ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE FAST;

FAST 用来检测表上存在的损坏，而不用报告具体损坏的细节，你可以用fast选项确定是否损坏，用cascade不带fast来确定损坏的细节，如果你用此方法验证已经enable的函数索引，那么可能会返回错误，你必须重建索引

ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE ONLINE;

ONLINE选项用来在DML操作正在某个对象时，验证某个对象的合法性，为保证并发性，而降低了验证对象的合法性的性能，当你使用online验证对象的合法性时，不会收集对象的统计信息，当你使用offline的时候回收集统计信息。你不能使用online分析cluster

OFFLINE ：是默认值。当你使用offline的时候，会增加验证对象合法性的性能，但是会阻碍INSERT,UPDATE, and DELETE语句访问对象的并发性，不影响select语句，

INTO 语句仅对分区表有效，数据库会把分区表中不合法行的rowid放到一个表中去，如果你忽略用户模式，会认为指定的表是在当前用户下，如果你忽略用户模式和表，那么会认为是表名为 INVALID_ROWS ，此表的sql脚本是 $ORACLE_HOME/rdbms/admin/utlvalid.sql

create table INVALID_ROWS (
owner_name         varchar2(30),
table_name         varchar2(30),
partition_name     varchar2(30),
subpartition_name varchar2(30),
head_rowid         rowid,
analyze_timestamp date
);

ANALYZE CLUSTER emp_dept LIST CHAINED ROWS INTO CHAINED_ROWS;

LIST CHAINED ROWS 可以让你通过分析表和cluster 确定迁移和行连接的行，你不能把这个语句用在分析索引

INTO 语句会把行连接和行迁移的行放到表中，如果你忽略用户模式，会认为指定的表是在当前用户下，如果你忽略用户模式和表，那么会认为是表名为 CHAINED_ROWS，此表必须在你本地的数据库中，创建表的脚本是

You can create the CHAINED_ROWS table using one of these scripts:

UTLCHAIN.SQL uses physical rowids. Therefore it can accommodate rows from conventional tables but not from index-organized tables. (See the Note that follows.)
UTLCHN1.SQL uses universal rowids, so it can accommodate rows from both conventional and index-organized tables.

ANALYZE TABLE orders DELETE STATISTICS;

DELETE STATISTICS: 能够删除通过分析保存在数据字典里的信息

When you use this clause on a table, the database also automatically removes statistics for all the indexes defined on the table. When you use this clause on a cluster, the database also automatically removes statistics for all the cluster tables and all their indexes, including the cluster index.

当你用这个语句可以自动删除定义在表上的索引的统计信息

Specify SYSTEM if you want Oracle Database to delete only system (not user-defined) statistics. If you omit SYSTEM, and if user-defined column or index statistics were collected for an object, then the database also removes the user-defined statistics by invoking the statistics deletion function specified in the statistics type that was used to collect the statistics.

如果你只想删除系统的统计信息而不删除用户定义的统计信息，可以指定system用户来删除，如果你忽略了system，那么用户定义在的列或者索引的统计信息也会删除

5 、实验

SQL> create table t1 as select *From emp;

表已创建

SQL> select OWNER,TABLE_NAME,STATUS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from dba_tables where table_name='T1';

OWNER TABLE STATUS NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ----- -------- ---------- ---------- ------------ ---------- ---------- -----------
SCOTT T1 VALID

SQL> ANALYZE TABLE T1 COMPUTE STATISTICS;

表已分析。

SQL> select OWNER,TABLE_NAME,STATUS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from dba_tables where table_name='T1';

OWNER TABLE STATUS NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ----- -------- ---------- ---------- ------------ ---------- ---------- -----------
SCOTT T1 VALID 12 4 4 7533 0 41

SQL> ANALYZE TABLE T1 DELETE STATISTICS;

表已分析。

SQL> select OWNER,TABLE_NAME,STATUS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from dba_tables where table_name='T1';

OWNER TABLE STATUS NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ----- -------- ---------- ---------- ------------ ---------- ---------- -----------
SCOTT T1 VALID

SQL> exec dbms_stats.gather_table_stats('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> select OWNER,TABLE_NAME,STATUS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from dba_tables where table_name='T1';

结论：可以发现analyze 可以收集dba_tables 表里的统计信息，而dbms_stats.gather_table_stats 只能收集到 AVG_ROW_LEN

SQL> create unique index ii on t1(empno);

索引已创建。

SQL> select OWNER,INDEX_NAME,INDEX_TYPE,COMPRESSION,BLEVEL,STATUS,NUM_ROWS,DISTINCT_KEYS,LEAF_BLOCKS,DEGREE from dba_indexes where table_name='T1'

OWNER INDEX_NAME INDEX_TYPE COMPRESS BLEVEL STATUS NUM_ROWS DISTINCT_KEYS LEAF_BLOCKS DEGREE
---------- ------------------------------ --------------------------- -------- ---------- -------- ---------- ------------- ----------- ----------
SCOTT II NORMAL DISABLED 0 VALID 12 12 1 1

SQL> ANALYZE TABLE T1 DELETE STATISTICS;

表已分析。

SQL> select OWNER,TABLE_NAME,STATUS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from dba_tables where table_name='T1';

OWNER TABLE STATUS NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ----- -------- ---------- ---------- ------------ ---------- ---------- -----------
SCOTT T1 VALID

结论：删除表上的统计信息，那么定义在此表上的索引的统计信息也一起删除

SQL> analyze table t1 validate structure cascade;

表已分析。

SQL> select OWNER,TABLE_NAME,STATUS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from dba_tables where table_name='T1';

OWNER TABLE STATUS NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ----- -------- ---------- ---------- ------------ ---------- ---------- -----------
SCOTT T1 VALID

结论：分析表结构的时候，不会收集统计信息

SQL> analyze table t1 list chained rows ;
analyze table t1 list chained rows
*
第 1 行出现错误:
ORA-01495: 未找到指定的链接行表

SQL> @?/rdbms/admin/utlchain.sql

表已创建。

SQL> Analyze table t1 list chained rows;

表已分析。

SQL> select *From CHAINED_ROWS;

未选定行

结论：如果不指定表名，那么必须手动创建默认的表 CHAINED_ROWS

SQL> drop table t1 purge;

表已删除。

SQL> create table t1 as select *From emp;

表已创建。

SQL> create index ii on t1(empno);

索引已创建。

SQL> select *From V$OBJECT_USAGE
2 ;

未选定行

SQL> alter index ii monitoring usage;

索引已更改。

SQL> analyze table t1 compute statistics;

表已分析。

SQL> alter index ii monitoring usage;

索引已更改。

SQL> select *From V$OBJECT_USAGE
2 ;

INDEX_NAME TABLE MON USE START_MONITORING END_MONITORING
------------------------------ ----- --- --- ------------------- -------------------
II T1 YES NO 03/15/2013 15:31:13

SQL> exec dbms_stats.gather_table_stats('SCOTT','T1');

PL/SQL 过程已成功完成。

SQL> select *From V$OBJECT_USAGE ;

INDEX_NAME TABLE MON USE START_MONITORING END_MONITORING
------------------------------ ----- --- --- ------------------- -------------------
II T1 YES NO 03/15/2013 15:31:13

结论：dbms_stats.gather_table_stats 在收集统计信息的时候会收集索引的统计信息，但是analyze在收集统计信息的时候不会收集索引的统计信息，但是我们一定要记住，使用dbms_stats收集的统计信息是给基于成本的优化器使用的，但是analyze 收集的统计信息是是dba_table 里，analyze 命令可以验证表的结构 validate，可以检查行迁移行连接

转载自原文链接, 如需删除请联系管理员。

原文链接：analyze 命令，转载请注明来源！