Oracle优化器统计信息收集

这里的统计信息指的是优化器（OPTIMIZER)统计信息，当Oracle数据库工作在CBO（COST BASED OPTIMIZER）模式下,优化器根据数据字典中记录的对象统计信息，评估SQL语句不同执行计划的成本，从而找到最优或者是相对最优的执行计划。从这一点来看统计信息的准确性就显得非常重要了，尤其是对于oracle10g以后的数据库，因为oracle10g以后，Oracle默认的优化器模式是all_rows,也就是基于成本的优化器。

优化器工作原理图：

统计信息包含的内容很多，比如表的，索引的，列的，列的直方图的等

收集Oracle数据库的统计信息，方法很多
1.oracle自动收集统计信息
a.oracle 10g,调用用gather_stats_job自动收集统计信息
工作日每天晚上10点到第二天早上六点，周末全天，oracle自动收集没有统计信息的和统计信息过

旧的对象的统计信息
这样做可能会对性能有影响，收集统计信息是要消耗大量资源的，所以很多DBA选择关掉这个job，

采用手动收集统计信息
默认这个job是打开的，关掉的方法如下：
EXEC DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

b.oracle 11g,使用自动任务来完成，并且窗口细化到每天一个，当然时间也可以调整
SQL> select client_name from dba_autotask_client;

CLIENT_NAME
----------------------------------------------------------------
auto optimizer stats collection
auto space advisor
sql tuning advisor
默认这个自动任务是打开的，当然也可以关掉
禁用任务：
BEGIN
dbms_auto_task_admin.disable(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
启用任务：
BEGIN
dbms_auto_task_admin.enable(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;

2.用户手动收集统计信息
手动收集统计信息适合于对于新建表、刚做完数据装载的表和表里数据变化比较频繁的

表（一天超过10%），如果仅仅是为了收集统计信息，建议使用dbms_stats包来实现
a.使用DBMS_STATS程序包
对于新建的表，默认是没有统计信息的
SQL> create table e as select empno,ename,sal from emp where rownum<=8;
Table created.

SQL> select table_name,num_rows,

2 blocks,to_char(last_analyzed,'yyyy-mm-ddhh24:mi:ss')
3 from user_tables
4 where table_name='E';

TABLE_NAME NUM_ROWS BLOCKS TO_CHAR(LAST_ANALYZ
------------------------------ ---------- ---------- -------------------
E
使用exec dbms_stats.gather_table_stats收集统计信息
SQL> exec dbms_stats.gather_table_stats('SCOTT','E');
PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')
2 from user_tables
3 where table_name='E';

TABLE_NAME NUM_ROWS BLOCKS TO_CHAR(LAST_ANALYZ
------------------------------ ---------- ---------- -------------------
E 8 4 2012-10-31 11:06:56

b.ANALYZE 命令
往表里插入一条记录，在没有对表从新分析前，user_tables里的统计信息是不变的，或者说不准确的
SQL> insert into e select empno,ename,sal from emp where rownum=1;
1 row created.

SQL> commit;
Commit complete.

SQL> select table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')
2 from user_tables
3 where table_name='E';
TABLE_NAME NUM_ROWS BLOCKS TO_CHAR(LAST_ANALYZ
------------------------------ ---------- ---------- -------------------
E 8 4 2012-10-31 11:06:56

使用analyze table 收集表的统计信息，下面的两个语句使用其中一个就可以了
compute 完全采样，也就是100%，适合于小表和索引
SQL> analyze table e compute statistics;
Table analyzed.
或者
estimate 部分采样，可以指定采样比率，本例采样20%，适合于大表
SQL> analyze table e estimate statistics sample 20 percent;
Table analyzed.

不管使用上面语句当中的哪一个，都会看到准确的统计信息
SQL> select table_name,num_rows,blocks,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss')
2 from user_tables
3 where table_name='E';
TABLE_NAME NUM_ROWS BLOCKS TO_CHAR(LAST_ANALYZ
------------------------------ ---------- ---------- -------------------
E 9 8 2012-10-31 11:13:48

3.动态采样
对于没有统计信息的表，oracle为了能够得到相对准确的执行计划，会在执行SQL语句之前对语句设计

到的表做动态采样


从10g开始，oracle默认把optimizer_dynamic_sampling的等级设为2，那就会出现如果对象没有统计信

息，oracle就会在每次查询的时候进行采样，所以oracle看起来会相当的智能。
SQL> show parameter dyna
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21957280/viewspace-749433/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21957280/viewspace-749433/