如何使用dbms_stats分析统计信息？

    Dbms_stats是oracle8i新增的程序包，它使统计数据的生成和处理更加方便，很多人都在推荐使用dbms_stats替代analyze,我倒是不怎么用过，记录一下

    estimate_percent --估算抽样百分比

    method_opt：for table --只统计表　for all indexed columns --只统计有索引的表列 for all indexes --只分析统计相关索引

    --创建统计信息历史保留表

    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 => \'scott\',

    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 => \'scott\',tabname => \'work_list\',estimate_percent => 10,method_opt=> \'for all indexed columns\') ;

    pl/sql procedure successfully completed

    --分析索引

    SQL> exec dbms_stats.gather_index_stats(ownname => \'crm2\',indname => \'IDX_ADM_PERMISSION_PID_MID\',estimate_percent => \'10\',degree => \'4\') ;

    pl/sql procedure successfully completed

    --如果发现执行计划走错，删除表的统计信息

    SQL>dbms_stats.delete_table_stats(ownname => \'scott\',tabname => \'work_list\') ;

    pl/sql procedure successfully completed

    --导入表的历史统计信息

    sql> exec dbms_stats.import_table_stats(ownname => \'scott\',tabname => \'work_list\',stattab => \'stat_table\') ;

    pl/sql procedure successfully completed

    --如果进行分析后，大部分表的执行计划都走错，需要导回整个scheme的统计信息

    sql> exec dbms_stats.import_schema_stats(ownname => \'scott\',stattab => \'stat_table\');

    pl/sql procedure successfully completed

    --导入索引的统计信息

    SQL> exec dbms_stats.import_index_stats(ownname => \'crm2\',indname => \'IDX_ADM_PERMISSION_PID_MID\',stattab => \'stat_table\')

    --检查是否导入成功

    SQL> select table_name,num_rows,a.blocks,a.last_analyzed from all_tables a where a.table_name=\'WORK_LIST\';

    TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED

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

    WORK_LIST 4005 186 2007-10-12 15

    SQL>

    analyze和dbms_stats不同的地方:

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

    参考：

    http://www.dbafan.com/blog/?p=114

    http://zhouwf0726.itpub.net/post/9689/288301

    http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/stats.htm#33861