SQL性能：统计信息

一、统计信息

统计信息主要是描述数据库中表、索引的大小，规模，数据分布状况等的一类信息。

last_analyzed字段表示上次统计信息搜集的时间，可以根据这个字段快速的了解最近一次统计信息搜集的时间。

二、收集统计信息的方法

1、使用gather_stats_job自动收集

在创建数据库时自动创建，由调度程序进行管理，收集数据库中优化程序统计信息缺失或已过时的所有对象的统计信息。

2、使用dbms_stats程序包手动收集

收集的是系统统计信息

3、通过设置数据库初始化参数进行收集

4、通过从另一个数据库导入统计信息进行收集

三、oracle自动收集统计信息

optimizer_mode参数的值是choose，choose不是优化器模式，它表示在分析数据库中的语句时，如果在对象上有统计信息，就使用CBO方式生产执行计划，如果没有，就使用RBO模式。

select window_name as "任务名",repeat_interval "任务重复间隔时间",duration "持续时间" from dba_scheduler_windows;
select * from dba_scheduler_wingroup_members;

1、停止任务
BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."FRIDAY_WINDOW"',
force=>TRUE);
END;

2、修改任务的持续时间，单位是分钟

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(180, 'minute'));
END;
 3、开始执行时间，BYHOUR=2,表示2点开始执行
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;BYMINUTE=0;BYSECOND=0');
END;
 4、开启任务
BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."FRIDAY_WINDOW"');
END;

5、禁用自动统计功能（。。。）

BEGIN
  DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/

四、DBMS_STATS包

DBMS_STATS包，提供了搜集（gather）、删除（delete）、导出（export）、修改（set）统计信息的方法。

dbms_stats与analyze的区别：

dbms_stats能正确收集分区表的统计信息，也就是说能够收集global statistic，而analyze只能收集最低层次对象的统计信息，然后推导和汇总出高一级对象的统计信息，如分区表只会收集分区统计信息，然后再汇总出所有分区的统计信息，得到表一级的统计信息。

1、global statistic

global statistic是指直接从对象本身收集到的统计信息，而不是从下一级对象推导和汇总出来的统计信息，对于优化器来说非常重要，一个SQL，除非其查询条件限制了数据只在分区上，否则大多数情况下需要golbal statistic才能得到正确的执行计划。有的统计值可以从下一级对象进行汇总后得到，如表的总行数，可以通过各分区的行数相加得到。但有的统计值不能通过下一级对象得到，比如列上的唯一值数量（distinct value）以及密度值（density）。

2、使用dbms_stats.gather_database_stats收集整个数据库的统计信息

BEGIN
  dbms_stats.gather_database_stats(estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE,
                                   method_opt       => 'for all indexed columns',
                                   options          => 'GATHER AUTO',
                                   cascade          => TRUE);
END;
/

参数说明：

（1）estimate_percent:采样的百分比，使用dbms_stats.auto_sample_size选项允许Oracle自动估算要采样的一个segment的最佳百分比。

（2）method_opt选项适合在表和索引数据发生变化时刷新统计数据：

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