oracle的数据分析简述

--查看表的统计信息（我们在analyze table命令中指定了for table或者不指定任何参数的时候,oracle数据库才会给我们统计基于表的统计信息）
analyze table  compute statistics for table

--查看字段的统计信息（在指定for all columns 和不指定任何参数的时候oracle会给所有字段做统计信息,在指定for indexed columns时,oracle只给有索引的字段进行字段信息统计,如果我们别有必要给所有字段统计信息时,这个属性就很有用了）
analyze table  compute statistics for all columns

--查看索引的统计信息
analyze table  compute statistics for all indexed columns;

2.包形式

--创建统计信息历史保留表
sql> exec dbms_stats.create_stat_table(ownname => 'scott',stattab => 'stat_table') ;

pl/sql procedure successfully completed

--导出整个scheme的统计信息
sql> exec dbms_stats.export_schema_stats(ownname => 'scott',stattab => 'stat_table') ;

pl/sql procedure successfully completed

--分析scheme
Exec dbms_stats.gather_schema_stats(
ownname => 'test',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns ',
degree => 6 )

--分析表
sql> exec dbms_stats.gather_table_stats(ownname => 'TEST',tabname => 'sm_user',estimate_percent => 10,method_opt=> 'for all indexed columns') ;

pl/sql procedure successfully completed

--分析索引
SQL> exec dbms_stats.gather_index_stats(ownname => 'TEST',indname => 'pk_user_index',estimate_percent => '10',degree => '4') ;

pl/sql procedure successfully completed

--如果发现执行计划走错，删除表的统计信息
SQL>dbms_stats.delete_table_stats(ownname => 'TEST',tabname => 'SM_USER') ;

pl/sql procedure successfully completed

--导入表的历史统计信息
sql> exec dbms_stats.import_table_stats(ownname => 'TEST',tabname => 'SM_USER',stattab => 'stat_table') ;

pl/sql procedure successfully completed

--如果进行分析后，大部分表的执行计划都走错，需要导回整个scheme的统计信息
sql> exec dbms_stats.import_schema_stats(ownname => 'TEST',stattab => 'SM_USER');

pl/sql procedure successfully completed

--导入索引的统计信息
SQL> exec dbms_stats.import_index_stats(ownname => 'TEST',indname => 'PK_USER_INDEX',stattab => 'stat_table')

analyze和dbms_stats不同的地方

analyze是同时更新表和索引的统计信息，而dbms_stats会先更新表的统计信息，然后再更新索引的统计信息，这里就有一个问题，就是当表的统计信息更新后，而索引的统计信息没有被更新，这时候cbo就有可能选择错误的plan

二、使用ANALYZE语句计算统计数据

    ANALYZE TABLE emp COMPUTE STATISTICS; --完全统计

    ANALYZE TABLE emp ESTIMATE STATISTICS; --默认的1064行统计样例

    ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 2000 ROWS; --用2000行做统计样例

    ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE 33 PERCENT; --用33%做统计样例

    统计得到的信息有以下这些(带*号的表示数据是精确的)

    表：

    ● 行数

    ● 已经使用的数据块数*

    ● 从未使用的数据块数

    ● 平均可用的空闲空间

    ● 链接行的数目

    ● 平均行长度

    ● 列中不同的值的数目

    ● 列的下限值*

    ● 列的上限值*

    索引：

    ● 索引层次*

    ● 叶子数据块的数目

    ● 不同的键的数目

    ● 每个键的叶子数据块的平均数目

    ● 每个键的数据块的平均数目

    ● 分簇因子

    注意：若一个索引已标记为UNUSABLE，则在分析时报错，必须删除或重建后才能分析。

    簇：

    ● 簇键链的平均长度

    注：当分析簇的统计数据时，簇中的表盒索引的统计信息会被自动收集

三、操作对象的统计数据

1、查看统计信息

    DBA|ALL|USER_INDEXES

    DBA|ALL|USER_TABLES

    DBA|ALL|USER_TAB_COLUMNS

    注意：这些表中的上面所列统计信息，如果不ANALYZE的话，是一直不变的。

2、删除统计信息

    ANALYZE TABLE emp DELETE STATISTICS;

    删除后可以防止table再使用CBO

3、其他的统计方法

    使用PLSQL包也可以对表进行数据统计分析

    DBMS_STATS：这个当然是最强大的分析包了

    DBMS_UTILITY.ANALYZE_SCHEMA：可直接分析SCHEMA中所有对象

    DBMS_DDL.ANALYZE_OBJECT：收集对象的的统计信息

四、校验表、索引、簇和物化视图

    为了校验表、索引、簇和物化视图的结构的完整性，使用带有VALIDATE STRUCTURE选项的ANALYZE语句，如果返回错误消息，则说明该对象已损坏。如果对象损坏，则需要删除并重建。如果是物化视图，则仅需要重新完全刷新一遍

    校验的语句如下：

    ANALYZE TABLE emp VALIDATE STRUCTURE;

    如果需要校验与某对象有关联的所有对象是否有效，则使用CASCADE子句：

    ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;

    再加入联机结构校验：

    ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE ONLINE;

五、列出表和簇的链接行

    可使用LIST CHAINED ROWS选项的ANALYZE语句，查看表或簇中链接的或迁移的行。这条语句的执行结果存储在制定的表中，该表被明确得创建，以便直接接收由LIST CHAINED ROWS子句返回的值。

1、创建CHAINED_ROWS表

    创建用于接收由ANALYZE LIST CHAINED ROWS语句返回的数据的表，执行'D:\oracle\ora92\rdbms\admin\utlchain.sql'(这个脚本其实就是一个简单的table创建语句)。

    创建之后，使用ANALYZE语句的语法如下：

    ANALYZE CLUSTER emp_dept CHAINED ROWS INTO CHAINED_ROWS;

2、删除表中的迁移或链接行

    使用CHAINED_ROWS表中的信息，可减少或删除现存表中的迁移或链接行，步骤如下：

    ①使用ANALYZE语句收集迁移或链接行信息

    ANALYZE TABLE order_hist LIST CHAINED ROWS;

    ②查询输出表

    SELECT * FROM CHAINED_ROWS

    WHERE TABLE_NAME = 'ORDER_HIST';

    在输出结果中会显示迁移或者链接的所有行

    ③如果输出表显示出有许多迁移或链接行，则开始执行删除迁移行：

    ④创建一个与现存表相同列的中间表，以便保留迁移或链接行

    CREATE TABLE int_order_hist

      AS SELECT * FROM order_hist

     WHERE ROWID IN

           (SELECT HEAR_ROWID

              FROM CHAINED_ROWS

             WHERE TABLE_NAME = 'ORDER_HIST');

    ⑤从现存的表中删除迁移或链接行

    DELETE FROM order_hist

     WHERE ROWID IN

           (SELECT HEAR_ROWID

              FROM CHAINED_ROWS

             WHERE TABLE_NAME = 'ORDER_HIST');

    ⑥把中间表中的行插入到现存表中

    INSERT INTO order_hist

    SELECT * FROM int_order_hist;

    ⑦删除中间表

    DROP TABLE int_order_hist;

    ⑧从输出表中删除步骤1所收集的信息

    DELETE FROM CHAINED_ROWS

     WHERE TABLE_NAME = 'ORDER_HIST';

    ⑨再次使用ANALYZE语句，查询输出表

什么样的index需要rebuild？

当一个table经常进行DML操作时，它的索引会存在许多block空间的浪费，这是因为index block中的记录只有在全部表示为不可用时， block 才能被加入到freelist中去被重新利用。所以我们需要寻找那些浪费空间很严重的index。

方法是: 
1) analyze index  index_name validate structure;

2)  select del_lf_blk_len/lf_blk_len  from index_stats
      where name = :index_name;

3) 如果结果大于20%， 那你的Index就可以被rebuild了。