col_usage$与直方图的收集

DB11202,两节点RAC

 

建表脚本,id列分布极度不均

create table t1(id number(10), name varchar2(20)) nologging tablespace IBMDBA

 begin

  for i in 1.. 99999 loop

    insert /*+ append */ into t1 values(999,lpad('I',20));

  end loop;

  for i in 1.. 100 loop

    insert /*+ append */into t1 values(i,lpad('I',20));

  end loop;

  commit;

end;

/

 

收集统计信息脚本,如无特殊说明,以下试验中均用此参数收集统计信息

exec dbms_stats.gather_table_stats('SYS','T1');

SQL> select dbms_stats.get_prefs('method_opt','SYS','T1') as pref from dual;

 

PREF

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

FOR ALL COLUMNS SIZE AUTO

SQL> select dbms_stats.get_prefs('estimate_percent','SYS','T1') from dual;

 

DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT','SYS','T1')

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

DBMS_STATS.AUTO_SAMPLE_SIZE

 

新建表后的信息,其统计信息和直方图均无

SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM from dba_tab_columns where table_name='T1' and column_name in('ID','NAME');

 

COLUM NUM_DISTINCT LOW_VALUE                                HIGH_VALUE                                  DENSITY NUM_BUCKETS LAST_ANAL SAMPLE_SIZE HISTOGRAM

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

NAME                                                                                                                                              NONE

ID                                                                                                                                                NONE

SQL> select num_rows,sample_size,last_analyzed from dba_tables where table_name='T1' and wner='SYS';

 

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED

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

 

 

以上是创建后的基本信息,统计信息为空;

 

 

1

没有执行任何查询,收集统计信息

SQL> select num_rows,sample_size,last_analyzed from dba_tables where table_name='T1' and wner='SYS';

 

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED

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

100099      100099 2013-03-21 23:22:14

SQL> select COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE from dba_tab_histograms where TABLE_NAME='T1' and wner='SYS' order by 1;

 

COLUM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU

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

ID                  1            999

ID                  0              1

NAME                1     1.6681E+35

NAME                0     1.6681E+35

SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM from dba_tab_columns where table_name='T1' and column_name in('ID','NAME');

 

COLUM NUM_DISTINCT LOW_VALUE                                HIGH_VALUE                                  DENSITY NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE HISTOGRAM

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

NAME             1 2020202020202020202020202020202020202049 2020202020202020202020202020202020202049          1           1 2013-03-21 23:22:14      100099 NONE

ID             101 C102                                     C20A64                                    .00990099           1 2013-03-21 23:22:14      100099 NONE

 

SQL> select * from col_usage$ where OBJ#=(select object_id from dba_objects where object_name ='T1' and wner='SYS');

 

no rows selected

 

没有收集直方图,col_usage$也没有记录任何信息

 

 

2

执行以表列为where条件的sql(必须为硬解析),再次收集统计信息

 

SQL>  select count(*) from t1 where name='I';

 

  COUNT(*)

----------

         0

收集统计信息

SQL>  select * from col_usage$ where OBJ#=(select object_id from dba_objects where object_name ='T1' and wner='SYS');

 

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

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

    132928          2              1              0                 0           0          0          0 2013-03-21 23:24:54

SQL> select num_rows,sample_size,last_analyzed from dba_tables where table_name='T1' and wner='SYS';

 

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED

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

100099      100099 2013-03-21 23:24:54

SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM from dba_tab_columns where table_name='T1' and column_name in('ID','NAME');

 

COLUM NUM_DISTINCT LOW_VALUE                                HIGH_VALUE                                  DENSITY NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE HISTOGRAM

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

NAME             1 2020202020202020202020202020202020202049 2020202020202020202020202020202020202049 5.1103E-06           1 2013-03-21 23:24:54        5376 FREQUENCY

ID             101 C102                                     C20A64                                    .00990099           1 2013-03-21 23:24:54      100099 NONE

 

SQL> select COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE from dba_tab_histograms where TABLE_NAME='T1' and wner='SYS' order by 1;

 

COLUM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU

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

ID                  0              1

ID                  1            999

NAME             5376     1.6681E+35

 

 

针对id列进行查询

SQL> select count(*) from t1 where id=999;

 

  COUNT(*)

----------

     99999

收集统计信息

SQL> select num_rows,sample_size,last_analyzed from dba_tables where table_name='T1' and wner='SYS';

 

  NUM_ROWS SAMPLE_SIZE LAST_ANALYZED

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

    100099      100099 2013-03-21 23:28:02

 

SQL> select * from col_usage$ where OBJ#=132928;

 

      OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

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

    132928          1              1              0                 0           0          0          0 2013-03-21 23:28:02

    132928          2              1              0                 0           0          0          0 2013-03-21 23:24:54

 

SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM from dba_tab_columns where table_name='T1' and column_name in('ID','NAME');

 

COLUM NUM_DISTINCT LOW_VALUE                                HIGH_VALUE                                  DENSITY NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE HISTOGRAM

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

NAME             1 2020202020202020202020202020202020202049 2020202020202020202020202020202020202049 4.9842E-06           1 2013-03-21 23:28:02        5512 FREQUENCY

ID             101 C102                                     C20A64                                   4.9842E-06           3 2013-03-21 23:28:02        5512 FREQUENCY

SQL> select COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE from dba_tab_histograms where TABLE_NAME='T1' and wner='SYS' order by 1;

 

COLUM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU

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

ID                  1             15

ID                  2             31

ID               5512            999

NAME             5512     1.6681E+35

 

id列也收集了直方图,bucket3个;

结论:

column出现在where子句时,其会被col_usage$记录并在下次收集统计信息时尝试收集其直方图;

 

 

3

Col_usage$的作用

修改一下案例2

执行select count(*) from t1 where id=999;

收集统计信息

删除col_usage$中的记录

重新收集统计信息

SQL>  select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM from dba_tab_columns where table_name='T1' and column_name in('ID','NAME');

 

COLUMN_NAM NUM_DISTINCT LOW_VALUE                                HIGH_VALUE                                  DENSITY NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE HISTOGRAM

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

NAME                  1 2020202020202020202020202020202020202049 2020202020202020202020202020202020202049          1           1 2013-03-22 02:34:17      100099 NONE

ID                  101 C102                                     C20A64                                    .00990099           1 2013-03-22 02:34:17      100099 NONE

此时id列没有直方图,因为col_usage$没有相应记录的缘故

 

注:不要手工更改col_usage$,否则很容易产生不可预知的问题

 

 

4

收集直方图类型

重建该表,其id列有101distinct值,在不执行任何sql的情况下收集统计信息

SQL> select * from col_usage$ where OBJ#=(select object_id from dba_objects where object_name ='T1' and wner='SYS');

 

no rows selected

 

exec dbms_stats.gather_table_stats('SYS','T1',method_opt => 'for all columns size 100');

其显示如下

 

SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM from dba_tab_columns where table_name='T1' and column_name in('ID','NAME');

 

COLUMN_NAM NUM_DISTINCT LOW_VALUE                                HIGH_VALUE                                  DENSITY NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE HISTOGRAM

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

NAME                  1 2020202020202020202020202020202020202049 2020202020202020202020202020202020202049 4.9969E-06           1 2013-03-22 03:05:44        5498 FREQUENCY

ID                  101 C102                                     C20A64                                   9.9938E-06         100 2013-03-22 03:05:44        5498 HEIGHT BAL

                                                                                                                                                                 ANCED

 

bucket调大至101

exec dbms_stats.gather_table_stats('SYS','T1',method_opt => 'for all columns size 101');

反复执行多次,idbucket数量会波动

SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM from dba_tab_columns where table_name='T1' and column_name in('ID','NAME');

 

COLUMN_NAM NUM_DISTINCT LOW_VALUE                                HIGH_VALUE                                  DENSITY NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE HISTOGRAM

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

NAME                  1 2020202020202020202020202020202020202049 2020202020202020202020202020202020202049 4.9208E-06           1 2013-03-22 03:06:13        5583 FREQUENCY

ID                  101 C102                                     C20A64                                   4.9208E-06           9 2013-03-22 03:06:13        5583 FREQUENCY

SQL> select COLUMN_NAME,NUM_DISTINCT,LOW_VALUE,HIGH_VALUE,DENSITY,NUM_BUCKETS, LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM from dba_tab_columns where table_name='T1' and column_name in('ID','NAME');

 

COLUMN_NAM NUM_DISTINCT LOW_VALUE                                HIGH_VALUE                                  DENSITY NUM_BUCKETS LAST_ANALYZED       SAMPLE_SIZE HISTOGRAM

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

NAME                  1 2020202020202020202020202020202020202049 2020202020202020202020202020202020202049 4.9923E-06           1 2013-03-22 03:05:21        5503 FREQUENCY

ID                  101 C102                                     C20A64                                   4.9923E-06           7 2013-03-22 03:05:21        5503 FREQUENCY

 

 

当列的distinct数量小于或等于指定的bucket数目时,会采用等频直方图,否则采用使用高度直方图

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

转载于:http://blog.itpub.net/15480802/viewspace-763737/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值