统计信息的收集

1、STATISTICS：

       优化统计信息是为了更详细的描述数据库及数据库对象而收集的数据，这些信息被用于为sql语句选择最优的执行计划。优化统计信息包括的项有：
　　Table statistics（表统计信息）：Number of rows、Number of blocks、Average row length；
　　Column statistics（列统计信息）：Number of distinct values (NDV) in column、Number of nulls in column、Data distribution (histogram)；
　　Index statistics（索引统计信息）：Number of leaf blocks、Levels、Clustering factor；
　　System statistics（系统统计信息）：I/O performance and utilization、CPU performance and utilization。
　　其中表、列和索引统计信息都可以通过统计信息自动收集功能来收集，系统统计信息在oracle 10g中只能通过手动收集来完成。

2、如何得到STATISTICS：

Orcale 10g中，STATISTICS由GATHER_STATS_JOB作业收集得到，只有当数据库对象没有统计信息或者统计信息已经过期（Oracle 10G中是否过期的标准是数据库对象被修改的记录行数超过10%，该信息由Modification Monitoring来追踪完成）时才对该对象进行信息统计，该作业在数据库创建或升级时由Scheduler自动创建，这些作业可以从视图DBA_SCHEDULER_JOBS中查到。
　　如：SELECT d.owner,d.job_name,d.program_name,d.schedule_type,d.comments FROM DBA_SCHEDULER_JOBS d
　　默认情况下，Scheduler在维护窗口（maintenance window，默认启动时间为周内晚上10：00至次日早上6：00及整个周六周日）打开时运行GATHER_STATS_JOB作业，作业GATHER_STATS_JOB则是通过调用系统内部过程DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC来完成信息统计的，该过程可根据数据库对象统计信息需求的优先级（即数据库对象被修改的多少）按先后顺序来完成统计信息收集任务。GATHER_STATS_JOB作业是否随维护窗口的关闭而关闭则由属性stop_on_window_close决定，stop_on_window_close的默认值为TRUE，此时GATHER_STATS_JOB作业随维护窗口的关闭而关闭。统计信息的收集是资源相当密集的工作，因此您可能希望确保它不影响数据库的正常操作。
　　统计信息收集还可由过程DBMS_STATS.GATHER_DATABASE_STATS应用GATHER AUTO选项，来完成，与系统地内部过程DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC相比，DBMS_STATS.GATHER_DATABASE_STATS不区分数据库对象统计信息需求的优先级。
　　非默认情况时，Oracle10g可通过设置初始化参数 STATISTIC_LEVEL，来控制是否启用统计信息自动收集功能。STATISTIC_LEVEL参数的信息如（表-1）：
　　参数类型 String
　　语法 STATISTICS_LEVEL = {ALL | TYPICAL | BASIC}
　　默认值 TYPICAL
　　参数类别 动态ALTER SESSION,ALTER SYSTEM
　　（表-1）
　　该参数用于控制数据库统计信息收集的级别。当其为默认值TYPICAL时，系统将自动收集所有主要的有关自身管理的信息以使系统提供最优性能，该值适合于绝大多数情况；当取值ALL时，相对TYPICAL值系统增加timed OS statistics和plan execution statistics两项信息统计；当取值 BASIC时：有关系统特性和功能的许多信息统计功能都将被关闭（详细见附录1）。因此oracle强烈建意参数STATISTICS_LEVEL的值尽量不要设成BASIC。同时当修改参数以语法“alter system set statistics_level='typical';”完成时，修改后的STATISTICS_LEVEL的作用范围为整个系统，但当使用“ALTER SESSION”时，STATISTICS_LEVEL的作用范围仅为本SESSION。STATISTIC_LEVEL参数所控制的所有统计、报告功能的状态信息都可以从视图V$STATISTICS_LEVEL中得到，具体有关视图V$STATISTICS_LEVEL各字段的含义可参见网页http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2135.htm#I1030264。
　　举例如下：
　　BEGIN
　　EXECUTE IMMEDIATE 'ALTER SESSION SET statistics_level=all';
　　END;--设置本session statistics_level为‘all’
　　SELECT v.STATISTICS_NAME,v.SESSION_STATUS,v.SYSTEM_STATUS,v.ACTIVATION_LEVEL,v.SESSION_SETTABLE FROM V$STATISTICS_LEVEL v;
　　BEGIN
　　EXECUTE IMMEDIATE 'ALTER SESSION SET statistics_level=basic';
　　END;--设置本session statistics_level为‘basic’
　　SELECT v.STATISTICS_NAME,v.SESSION_STATUS,v.SYSTEM_STATUS,v.ACTIVATION_LEVEL,v.SESSION_SETTABLE FROM V$STATISTICS_LEVEL v;
　3、保存以前版本的统计信息在优化器收集统计信息时可能出现原来的优化方法在收集统计信息之前一直工作良好，但是在此之后，由于新收集的统计信息产生了不良计划，导致查询突然出错或效率降低。为避免这种情况，统计信息的收集作业在收集新信息之前保存当前的统计信息。如果出现问题，则可以返回到原有的统计信息，或者通过历史统计检查二者之间的不同之处，以解决问题。
　　例如，假设在 5 月 31 日晚上 10:00 开始运行表 emp 上的统计信息收集作业，而随后查询的性能变差。Oracle 保存了原有的统计信息，可以通过执行以下命令重新获取这些信息：
　　begin
　　dbms_stats.restore_table_stats (
　　'ARUP',
　　'REVENUE',
　　'10-DEC-08 10.00.00.000000000 PM -04:00');
　　end;
　　此命令将统计信息恢复到 12月 10 日晚上 10:00 为止的统计信息,时间信息是以 TIMESTAMP数据类型提供。历史统计信息能够恢复的时间长度是由保留参数所决定的。要查看当前的保留参数，可使用以下查询：
　　SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
　　GET_STATS_HISTORY_RETENTION
　　---------------------------
　　31
　　表示可以保存相当于 31 天的统计信息，但并不能予以保证。要了解统计信息所覆盖到的确切时间和日期，只需使用以下查询：
　　SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
　　GET_STATS_HISTORY_AVAILABILITY
　　---------------------------------------------------------------------
　　10-DEC-08 09.21.33.594053000 PM -04:00
　　该查询表明可用的最陈旧统计信息日期为 12 月 10 日上午9:21。 同时，也可以通过执行内建的函数将保留时间设为不同的值。例如，要将其设为 45 天，可使用：
　　execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45)

4、注意点：

    不是所有的数据库对象都适合使用统计信息自动收集功能，如当在如下情况时，统计信息自动收集功能就无法达到需求：
　　（1） 某些表在工作时间内被删除（delete）或截断（truncate）并且被重新创建；
　　（2） 某些重负荷表在工作时间内被大量修改（insert、update），修改量超过10%。
　　对于这些修改频率较高的表，要保持其统计信息不过期，可采用如下两个方法：
　　对于（1）可采用下方法：
　　方法1：将这些表的统计信息设为NULL，这样Oracle会以查询优化的一部分来动态的收集这些无统计信息表的统计信息。Oracle动态收集统计信息功能由参数OPTIMIZER_DYNAMIC_SAMPLING控制，并且只有当该参数的值大于或等于2时（默认值为2），Oracle动态收集统计信息功能才能启动。
　　要将数据库对象统计信息设为NULL，只需删除原有的统计信息，然后给其统计信息功能加锁，语法如下：
　　BEGIN
　　DBMS_STATS.DELETE_TABLE_STATS('OE','ORDERS');--删除统计信息
　　DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS'); --不再收集统计信息
　　END;
　　类似的加锁、解锁还有LOCK_SCHEMA_STATS、LOCK_TABLE_STATS、UNLOCK_SCHEMA_STATS、UNLOCK_TABLE_STATS。
　　方法2：将数据库表的最能代表该表状况的典型统计信息固定，以作为该表优化统计信息。我们可以在任何时候去收集这些典型的信息并将其锁定，这样就可达到目的，这样的统计信息有可能比统计信息自动收集功能在晚上所收集到的统计信息更能表达数据表的状况。
　　对问题（2）可采用如下方法：
　　对于那些修改量较大的表，统计信息的收集最好是能紧跟在修改操作之后，而作为sql程序或者数据库作业的一部分来手动完成；

     

对与易变对象的变化，主要三种处理方式：

a 、可以人工收集统计信息(DBMS_STATS)

      eg：

     begin dbms_stats.gather_index_stats

     (ownname => 'HELIOS', indname => 'PK_ODD_CONTROL',estimate_percent =>20);

     end;

b、一种就是删除统计信息。使它的统计信息为空，对于任何统计信息缺失的表,oracle会用动态取样特性自动产生统计信息。如果使用久的统计信息 就可能产生错误的执行计划。需要设置optimizer_dynamic_sampling为2（ORACLE10G默认值）或以上都可以启动此特性。

optimizer_dynamic_sampling ,提供在SQL分析的时候，自动根据不同的Level（0-10）以不同的准确度分析SQL中未被analyze过的表，意在为CBO提供更多的统计信 息。在Oracle9iR2中引入，默认为Level 1，10g默认为2 。http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1135.htm#REFRN10140

Level 0: Do not dynamically sample the table(s)

Level 1：Sample tables that have not been analyzed if there is more than one table in the query,the table in question has not been analyzed and it has no indexes,and the optimizer determines that the query plan would be affected based on the size of this objects

Level 2:Sample all unanalyzed tables referenced in the query using default sampling amounts(small sample)

Level 3 -- Level 10 ........更详细的sample而已。

例如：dbms_stats.delete_table_stats(‘table_name’ ,‘VOLATILE_TABLE’)

c、另一种就是设置为表进行锁定,这样就可以不更新统计信息（注意LOCK表以后，表就不能修改了）。

dbms_stats.lock_table_stats(‘table_name’ ,‘VOLATILE_TABLE’)；