Analyze clauses and dbms_stats package（一）

analyze的作用：
1.收集schema对象(table, index, or cluster)的统计信息
SQL> select table_name,LAST_ANALYZED from user_tables;

TABLE_NAME                     LAST_ANAL
------------------------------ ---------
EMP

SQL> analyze table emp compute statistics;

Table analyzed.

SQL> select table_name,LAST_ANALYZED from user_tables;

TABLE_NAME                     LAST_ANALYZED
------------------------------ -------------------
EMP                            2013-11-24 04:35:08

ORACLE 官方推荐我们不要再使用analyze ... compute statistics和estimate statistics来收集统计信息。这个语句已经过时。替代它的是dbms_stats package，它能并行的收集统计信息,collect global statistics for partitioned objects, and fine tune your statistics collection in other ways.The cost-based optimizer, which depends upon statistics, will eventually use only statistics that have been collected by DBMS_STATS.


2.验证table, index, cluster, or materialized view结构的完整性
SQL> ANALYZE TABLE emp VALIDATE STRUCTURE;
SQL> ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;   
---cascade 包含验证该表的依赖对象（index等）
SQL> ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE FAST;  
---This version checks for the existence of corruptions using an optimized check algorithm, but does not report details about the corruption. If the FAST check finds a corruption, you can then use the CASCADE option without the FAST clause to locate it.
SQL> ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE ONLINE;
---You can specify that you want to perform structure validation online while DML is occurring against the object being validated. There can be a slight performance impact when validating with ongoing DML affecting the object.

3.查找表中存在的行链接
创建存放行链接信息的表
@/u01/app/product/11.2.0.4/rdbms/admin/utlchain.sql  或者utlchn1.sql 
得到下面这张表
SQL> desc CHAINED_ROWS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER_NAME                                         VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 CLUSTER_NAME                                       VARCHAR2(30)
 PARTITION_NAME                                     VARCHAR2(30)
 SUBPARTITION_NAME                                  VARCHAR2(30)
 HEAD_ROWID                                         ROWID
 ANALYZE_TIMESTAMP                                  DATE

下面我们来的到一个有行链接的表:(scott用户的表）
SQL>create table emp2 as select * from emp;
SQL> select CHAIN_CNT,ROW_MOVEMENt,table_name from user_tables where table_name='EMP2';

 CHAIN_CNT ROW_MOVE TABLE_NAME
---------- -------- ------------------------------
         0 DISABLED EMP2
SQL>update emp2 set ename='';
SQL>alter table emp2 modify ename char(200);
SQL> update emp2 set ename='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
SQL> analyze table emp2 compute statistics;
SQL> select CHAIN_CNT,ROW_MOVEMENt,table_name from user_tables where table_name='EMP2';

 CHAIN_CNT ROW_MOVE TABLE_NAME
---------- -------- ------------------------------
         7 DISABLED EMP2
现在我们得到有7行行迁移的表。下面通过analyze分析表中哪些行产生了行迁移。

SQL> analyze table emp2 LIST CHAINED ROWS;

Table analyzed.

SQL>  select * from CHAINED_ROWS;

OWNER_NAME                     TABLE_NAME CLUSTER_NA PARTITION_ SUBPARTITION_NAME              HEAD_ROWID         ANALYZE_TIMESTAMP
------------------------------ ---------- ---------- ---------- ------------------------------ ------------------ -------------------
SCOTT                          EMP2                             N/A                            AAAD9zAAEAAAACrAAH 2013-11-24 16:09:37
SCOTT                          EMP2                             N/A                            AAAD9zAAEAAAACrAAI 2013-11-24 16:09:37
SCOTT                          EMP2                             N/A                            AAAD9zAAEAAAACrAAJ 2013-11-24 16:09:37
SCOTT                          EMP2                             N/A                            AAAD9zAAEAAAACrAAK 2013-11-24 16:09:37
SCOTT                          EMP2                             N/A                            AAAD9zAAEAAAACrAAL 2013-11-24 16:09:37
SCOTT                          EMP2                             N/A                            AAAD9zAAEAAAACrAAM 2013-11-24 16:09:37
SCOTT                          EMP2                             N/A                            AAAD9zAAEAAAACrAAN 2013-11-24 16:09:37

通过得到的rowid，我们可以通过以下手段消除行链接。
SQL> create table temp_emp2 as select * from emp2 where rowid in (select HEAD_ROWID from CHAINED_ROWS where TABLE_NAME='EMP2');

Table created.  

SQL> delete from emp2 where rowid in (select HEAD_ROWID from CHAINED_ROWS where TABLE_NAME='EMP2');

7 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into emp2 select * from temp_emp2;

7 rows created.

SQL> commit;

Commit complete.

SQL> drop table temp_emp2 purge;

Table dropped.

SQL> delete from CHAINED_ROWS where table_name='EMP2';

7 rows deleted.

SQL> commit;

Commit complete.   

验证消除行链接：
SQL> analyze table emp2 LIST CHAINED ROWS;

Table analyzed.

SQL> select * from CHAINED_ROWS where table_name='EMP2';

no rows selected

对于消除行链接的方法还有move，shrink和在线重定义表。

关与dbms_stats  package相关信息见Analyze clauses and dbms_stats package（二）