size auto比size skewonly聪明的一个地方

  曾经我比较过dbms_stats.gather_table_stats里面method_opt参数的两个选项：size auto和size skewonly的区别，（详情请见 http://space.itpub.net/15415488/viewspace-591765）大致是讲size auto会多考虑column的使用情况来决定是否搜集柱状图，而size skewonly只需考虑这列是否倾斜即可。所以从这个层面上来看，如果我们确定希望搜集某个/些column(s)的柱状图时，size skewonly是更好的选择。但是，今天发现在某个特别的情况下，size auto能够识别一些size skewonly和size 都无法识别的一种情况。

  实验环境：10203 @ solaris 64 bit.

  建立一张实验用表：

create table testbyhao
(id varchar2(50) primary key,
name varchar2(100));

  插入的数据中的id这列，是由前缀40个'a'组成，后缀不一样的varchar2(50)组成。

insert into testbyhao
select rpad('a',40,'a')||to_char(rownum),
object_name from dba_objects;

  我先用size skewonly分析此表：

exec dbms_stats.gather_table_stats(user,'TESTBYHAO',METHOD_OPT=>'for all columns size skewonly');

  我们发现，分析后的id这列的distinct value为1，即Oracle并没有分辨出前缀前40个字符同样，但后缀不一样的varchar2(50)的数据。

SQL> select COLUMN_NAME,NUM_DISTINCT
  2  from user_tab_columns where TABLE_NAME='TESTBYHAO';

COLUM NUM_DISTINCT
----- ------------
ID               1
NAME          7899

  但我用size auto再分析此表：

exec dbms_stats.gather_table_stats(user,'TESTBYHAO',METHOD_OPT=>'for all columns size auto');

  我们发现，size auto模式成功识别出了所有id的不同来。

SQL> select COLUMN_NAME,NUM_DISTINCT
  2  from user_tab_columns where TABLE_NAME='TESTBYHAO';

COLUM NUM_DISTINCT
----- ------------
ID           11573
NAME          7899

   为了查明为什么，我做了10046 trace来比较两种分析的不同，对于size skewonly，我从trace文件里找到了这样一句SQL:

select substrb(dump(val,16,0,32),1,120) ep, cnt
from (select substrb("ID",1,32) val,count(*) cnt 
from "LONGRAW_USER"."TESTBYHAO" t 
where substrb("ID",1,32) is not null 
group by substrb("ID",1,32))
order by nlssort(val,'NLS_SORT = binary') ; 

    原来在size skewonly的模式下，Oracle是根据id前32个字节来判断的，并没有对整个varchar2(50)字段进行分析。

    但是对size auto，我并没有在trace文件中找到相关的SQL。

另外，不仅仅是size skewonly，就连我手工指定size 254也都无法让Oracle变聪明：

SQL> exec dbms_stats.gather_table_stats(user,'TESTBYHAO',ESTIMATE_PERCENT=>100,METHOD_OPT=>'for all columns size 254');

PL/SQL procedure successfully completed.

SQL> select COLUMN_NAME,NUM_DISTINCT from
  2  user_tab_columns where TABLE_NAME='TESTBYHAO';

COLUM NUM_DISTINCT
----- ------------
ID               1
NAME          7899

所以，对于这种需要对前缀很长（超过32个字节）的varchar2字段计算distinct value时，需要使用聪明的size auto模式。

    出此之外，我们谈到了柱状图，那么在这种非常迷惑Oracle的情况下，到底会不会为ID这列搜集柱状图呢？

于是我依旧比较了size auto，size skewonly和size 254这三种情况：

SQL> exec dbms_stats.gather_table_stats(user,'TESTBYHAO',METHOD_OPT=>'for all columns size skewonly');

PL/SQL procedure successfully completed.

SQL> select HISTOGRAM from user_tab_columns
  2  where TABLE_NAME='TESTBYHAO' and COLUMN_NAME='ID';

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

SQL> exec dbms_stats.gather_table_stats(user,'TESTBYHAO',METHOD_OPT=>'for all columns size auto');

PL/SQL procedure successfully completed.

SQL> select HISTOGRAM from user_tab_columns
  2  where TABLE_NAME='TESTBYHAO' and COLUMN_NAME='ID';

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

SQL> exec dbms_stats.gather_table_stats(user,'TESTBYHAO',METHOD_OPT=>'for all columns size 254');

PL/SQL procedure successfully completed.

SQL> select HISTOGRAM from user_tab_columns
  2  where TABLE_NAME='TESTBYHAO' and COLUMN_NAME='ID';

HISTOGRAM
---------------------------------------------
FREQUENCY

    以上结果表明，果然只有size 才会搜集柱状图，size auto和size skewonly在搜集柱状图的选择上面的决定是一样的，这是因为size skewonly认为只有一行，自然不会搜集；size auto认为每行都不一样，也不会搜集。

 

    接着我到了11GR2环境看这样的情况是否能重现。

    实验环境：11GR2 @ Solaris 64 bit。

SQL> exec dbms_stats.gather_table_stats(user,'TESTBYHAO',METHOD_OPT=>'for all columns size skewonly');

PL/SQL procedure successfully completed.

SQL> select COLUMN_NAME,NUM_DISTINCT from user_tab_columns where TABLE_NAME='TESTBYHAO';

COLUMN_NAM NUM_DISTINCT
---------- ------------
ID                12943
NAME               9785

SQL> exec dbms_stats.gather_table_stats(user,'TESTBYHAO',METHOD_OPT=>'for all columns size auto');

PL/SQL procedure successfully completed.

SQL> select COLUMN_NAME,NUM_DISTINCT from user_tab_columns where TABLE_NAME='TESTBYHAO';

COLUMN_NAM NUM_DISTINCT
---------- ------------
ID                12943
NAME               9785

   现在，在11GR2时，size skewonly已经变聪明了：）

 

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

转载于:http://blog.itpub.net/15415488/viewspace-622255/