【转】OPTIMIZER

liuxiaoyun111

　　这里的统计信息指的是优化器（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 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