ORALCE DBMS_STATS与ANALYZE介绍

 对于ORACLE数据库来说统计信息一个对数据库性能有很大影响的特别是对于CBO（基于成本的数据库）模式的数据库，统计信息太旧了会严重降低SQL的执行效率，因为过时的统计信息会让CBO模式的数据库做出错误的判断去选择一条非常糟糕的执行计划从而影响效率。对于DML操作特别频繁的数据库对象要定期去更新它的统计信息，以便让ORACEL能够按照新的统计信息去做出正确的判断，提升数据库的执行效率。ORACLE数据库目前主要有两个收集统计信息的程序ANALYZE（早期的版本），DBMS_STATS（新版本ORACLE现在力推的）

DBMS_STATS与ANALYZE介绍：

从Oracle8i开始引入DBMS_STATS包，ORACLE专家们就开始推荐使用DBMS_STATS取代ANALYZE理由如下：

1． dbms_stats可以并行分析

2． dbms_stats有自动分析的功能(alter table monitor )

3． analyze分析统计信息的有些时候不准确

第1,2比较好理解，且第2点实际上在大型数据库中是最吸引人的；第3点目前还不能完全肯定持保留意见因为对于非分区表来说孰优孰劣没结论有的时候analyze比dbms_stats还准确但是对于分区表来说最好不要用analyze，原因是dbms_stats会实在的去分析表全局统计信息（当指定参数）；而analyze是将表分区局部statistics汇总计算成表全局statistics，可能导致误差。没有分区表的情况下两个都可以使用(看个人习惯，当然也可以分区表使用dbms_stats,其他使用analyze )。在一些论坛上也有看到dbms_stats分析之后出现统计数据不准确的情况，而且确实有bug在dbms_stats上(可能和版本有关，有待查明)，应该是少数情况，需要我们注意。还有，一般不建议analyze和dbms_stats混用。 实验： 如果在分区表上用dbms_stats统计后，再使用analyze table来统计，就会出现表信息不被更新的问题。删除统计信息后再分析就更新了，或者直接用dbms_stats分析。

dbms_stats所有的功能包如下：

GATHER_INDEX_STATS：收集索引统计信息 GATHER_TABLE_STATS：收集表统计信息 GATHER_SCHEMA_STATS：收集用户所有的统计信息 GATHER_DATABASE_STATS：收集数据库的统计信息 EXPORT_COLUMN_STATS：导出列的统计信息 EXPORT_INDEX_STATS：导出索引统计信息 EXPORT_TABLE_STATS：导出表统计信息 EXPORT_SCHEMA_STATS：导出用户统计信息 EXPORT_DATABASE_STATS：导出数据库统计信息 delete_column_stats：删除列的统计信息 delete_index_stats：删除索引的统计信息 delete_table_stats：删除表的统计信息 delete_schema_stats：删除用户的统计信息 delete_database_stats：删除库的统计信息 dbms_stats.create_stat_table 创建一个专用表保存各表的统计信息，在统计信息导入和导出时必须要有此过程创建的表存在

有些论坛讲最好不要使用ANALYZE_DATABASE, 或者GATHER_DATABASE_STATS包来一次性解决问题。这个变态包会分析包括SYS的对象的，造成严重的数据库后果

收集表的统计信息简单介绍：

DBMS_STATS.GATHER_TABLE_STATS的语法如下: DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_ percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade BOOLEAN, stattab VARCHAR2, statid VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN, stattype VARCHAR2 force BOOLEAN); 参数说明: ownname:要分析表的拥有者 tabname:要分析的表名. partname:分区的名字,只对分区表或分区索引有用. estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定取最佳采样值. block_sapmple:是否用块采样代替行采样. method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下: for all columns:统计所有列的histograms. for all indexed columns:统计所有indexed列的histograms. for all hidden columns:统计你看不到列的histograms for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254]; REPEAT上次统计过的histograms;AUTO由oracle决定N的大小;SKEWONLY multiple end-points with the same value which is what we define by "there is skew in the data degree:决定并行度.默认值为null. granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned. cascace:是否收集索引的信息.默认为falase. Stattab:指定要存储统计信息的表 Statid：如果多个表的统计信息存储在同一个stattab中用于进行区分. Statown：存储统计信息表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典. no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE. force:即使表锁住了也收集统计信息.

FOR EXAMPLE：

--收集用户表的统计信息 exec dbms_stats.gather_table_stats(ownname =>'ownname',tabname =>'tablename',degree => 6,cascade => true); --删除用户统计信息 exec dbms_stats.delete_schema_stats('OWNER');

关于dbms_stats和Analyze的比较分析:

1． 对于分区表，建议使用DBMS-STATS，而不是使用Analyze语句。
a) 可以并行进行，对多个用户，多个Table
b) 可以得到整个分区表的数据和单个分区的数据。
c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区
d) 可以倒出统计信息
e) 可以自动收集统计信息(alter table monitor )
2． DBMS_STATS的缺点
a) 不能Validate Structure(注意：validate structure主要在于校验对象的有效性. compute statistics在于统计相关的信息)。

注：对于这个问题分区表一般遇到的情况比较多，过将一个表的某几个分区中的数据TRUNCATE后这个表的索引就会变成无效状态了，必须将索引重建后才能进行统计信息收集否则在收集的过程中会报索引状态无效
b) 不能收集CHAINED ROWS (行链接),, 不能收集CLUSTER TABLE (簇表)的信息，这两个仍旧需要使用Analyze语句。

注：识别表或cluster的行迁移与行链接

为了能使用analyze….list chained rows语句识别行迁移与行链接﹐必须先在执行analyze语句所在的schema内执行$ORACLE_HOME/rdbms/admin/utlchain.sql(或utlchn1.sql)脚本建立chained_rows表。在chained_rows建立之后﹐就可以执行下面的语句﹕

analyze table table_name list chained rows into chained_rows;

ANALYZE TABLE tablename COMPUTE|ESTIMATE|DELETE STATISTICS

COMPUTE；ESTIMATE；DELETE分别代表什么意思
analyze table tablename compute statistics
------------------------------------------
对全表进行统计，然后生成统计信息，数据量大的时候
速度很慢，对temp 表要求也很高。

analyze table tablename ESTIMATE STATISTICS

SAMPLE XXX PERCENT

---------------------
抽取表中的部分数据进行统计，最好>=20%,否则得不到准确的统计信息

analyze table tablename estimate statistics sample 25 percent;

analyze table tablename delete statistics
-----------------------------------------
删除表上的统计信息
c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True

注：Analyze是同时更新表和索引的统计信息，而dbms_stats会先更新表的统计信息，然后再更新索引的统计信息(默认Cascade是False是不更新索引的统计信息)，这里就有一个问题，就是当表的统计信息更新后，而索引的统计信息没有被更新，这时候cbo就有可能选择错误的plan。
3． 对于oracle里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。

总结：对于10G及以上（特别是CBO模式）的版本建议最好还是尽量用DBMS_STATS毕竟现在是ORACLE数据库力挺的工具包了，到10G也算是一个比较成熟的产品了。