DB:11202,两节点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列也收集了直方图,bucket为3个;
结论:
当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列有101个distinct值,在不执行任何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');
反复执行多次,id的bucket数量会波动
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/