Oracle 收集列直方图

收集方式
begin                                                           
        DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYSTEM',
        tabname=>'WH',
        estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt=>'FOR ALL COLUMNS SIZE AUTO',
        no_invalidate=>false,
        force=>true,
        cascade=>true,
        degree=>DBMS_STATS.AUTO_DEGREE);
end;
/
method_opt
Accepts either of the following options, or both in combination:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]
size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
column is defined as column := column_name | extension name | extension


- integer : Number of histogram buckets. Must be in the range [1,254].
- REPEAT : Collects histograms only on the columns that already have histograms
- AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.




AUTO会根据column数据分布情况以及column的使用情况（根据COL_USAGE$来判断，COL_USAGE$是被用来记录数据库的列级使用信息，这些信息被用来辅助统计信息收集）进行判断是不是收集这个column的histogram。而SKEWONLY只是根据column的数据分布情况决定是否收集histogram。






1.建立一个数据均匀分布的表
SQL> create table system.wh as select * from dba_objects;


Table created.


SQL> create index system.ll on system.wh(object_id);


Index created.






2.查看列情况
SQL> select o.OBJECT_ID from dba_objects o where o.OWNER = 'SYSTEM' and o.OBJECT_NAME = 'WH';


 OBJECT_ID
----------
    104897


SQL> select * from col_usage$ o where o.obj# = 104697;


no rows selected


3.做几次查询






SQL> select count(*) from system.wh where object_id=1;


  COUNT(*)
----------
         0


SQL> select count(*) from system.wh where object_id>100;


  COUNT(*)
----------
     74840


4.收统计信息


SQL> begin                                                           
  2          DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYSTEM',
  3          tabname=>'WH',
  4          estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
  5          method_opt=>'FOR ALL COLUMNS SIZE AUTO',
  6          no_invalidate=>false,
  7          force=>true,
  8          cascade=>true,
  9          degree=>DBMS_STATS.AUTO_DEGREE);
 10  end;
 11  /


PL/SQL procedure successfully completed.


5.查看列情况
SQL> select o.obj#,o.intcol#,o.equality_preds,o.range_preds from col_usage$ o where o.obj# = 104897;


      OBJ#    INTCOL# EQUALITY_PREDS RANGE_PREDS
---------- ---------- -------------- -----------
    104897          4              1           1


可以看到 等于查询与 范围查询，各记录了一次


6.查看一下列的统计信息




SQL> select s.HISTOGRAM from dba_tab_columns s where s.OWNER = 'SYSTEM' and s.TABLE_NAME = 'WH';


HISTOGRAM
---------------
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE


15 rows selected.


7.手工收一下列直方图
begin                                                           
        DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYSTEM',
        tabname=>'WH',
        estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt=>'FOR COLUMNS SIZE 10 OBJECT_ID',
        no_invalidate=>false,
        force=>true,
        cascade=>true,
        degree=>DBMS_STATS.AUTO_DEGREE);
end;
/
8.查看一下列信息


SQL> select s.HISTOGRAM from dba_tab_columns s where s.OWNER = 'SYSTEM' and s.TABLE_NAME = 'WH';


HISTOGRAM
---------------
NONE
NONE
NONE
HEIGHT BALANCED
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE
NONE


15 rows selected.


在oracle9i中，默认的统计信息收集是不收集直方图信息的，也就是说默认的MOTHOD_OPT模式为FOR ALL COLUMNS SIZE 1
在10g开始，dbms_stats包中默认的METHOD_OPT做了调整，默认的METHOD_OPT值为FOR ALL COLUMNS SIZE AUTO
是否收集列统计信息是由ORACLE自己判断的,感觉如果数据是均匀分布的,oracle 应该不会去收集




使用auto收集直方图有两个条件：1，当前列是倾斜的；2，当前列被where语句中作为谓词条件出现过；


---------- ---------- -------------- ----------- ---------- ---------- -------------- -----------


1.创建数据分布不均匀的表


SQL> create table system.wh(num1 number(10),num2 number(10));


Table created.
SQL>  begin
  2      for i in 1 .. 10000 loop
  3        if i < 9900 then
  4               insert into system.wh values(10000,i);
  5               commit;
  6        else
  7               insert into system.wh values(100,i);   
  8               commit; 
  9        end if;
 10      end loop;
 11   end;
 12   /




PL/SQL procedure successfully completed.




SQL> begin                                                           
  2          DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYSTEM',
  3          tabname=>'WH',
  4          estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
  5          method_opt=>'FOR ALL COLUMNS SIZE AUTO',
  6          no_invalidate=>false,
  7          force=>true,
  8          cascade=>true,
  9          degree=>DBMS_STATS.AUTO_DEGREE);
 10  end;
 11  /


PL/SQL procedure successfully completed.




2.查询数据
SQL> select count(*),num1  from system.wh where num1=100 group by num1;


  COUNT(*)       NUM1
---------- ----------
       101        100





3.看一下列查询信息
SQL> select o.obj#,o.intcol#,o.equality_preds from col_usage$ o where o.obj# = 104899;


      OBJ#    INTCOL# EQUALITY_PREDS
---------- ---------- --------------
    104899          1              1


4.看一下列统计信息情况
SQL> select s.HISTOGRAM from dba_tab_columns s where s.OWNER = 'SYSTEM' and s.TABLE_NAME = 'WH';


HISTOGRAM
---------------
FREQUENCY
NONE


看来列直方图是均匀分布,oracle是不收集的,如果是倾斜的,oracle 就会去收集






删除列直方图
dbms_stats.delete_column_stats(ownname => 'SYSTEM,tabname => 'WH',colname => 'OBJECT_ID',col_stat_type => 'HISTOGRAM')