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';
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(二)