关于表的统计信息收集和直方图

雪影舞剑

刘大:
关于表的统计信息收集和直方图有些疑惑
下面是我以前的测试过程
请教问题为: 使用"columns size 2 DEPTNO"仅收集指定字段的直方图与
"for all columns size 1 FOR COLUMNS SIZE 2 DEPTNO JOB"收集直方图以及其他字段的基本信息有何区别，应如何取舍

=======================================
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',cascade=>TRUE,no_invalidate=>FALSE);

PL/SQL procedure successfully completed

SQL> select t.column_name,t.num_buckets,t.last_analyzed from dba_tab_col_statistics t where t.table_name = 'EMP' and owner='SCOTT';

COLUMN_NAME                    NUM_BUCKETS LAST_ANALYZED
------------------------------ ----------- -------------
EMPNO                                    1 2012-11-2 2:5
ENAME                                    1 2012-11-2 2:5
JOB                                      5 2012-11-2 2:5
MGR                                      1 2012-11-2 2:5
HIREDATE                                 1 2012-11-2 2:5
SAL                                      1 2012-11-2 2:5
COMM                                     1 2012-11-2 2:5
DEPTNO                                   3 2012-11-2 2:5

8 rows selected

SQL> select t.NUM_ROWS,t.last_analyzed from dba_tables t where t.table_name = 'EMP' and owner='SCOTT';

  NUM_ROWS LAST_ANALYZED
---------- -------------
        14 2012-11-2 3:0
        
不带任何参数即为AUTO，由系统自动指定直方图的桶数目

SQL> exec dbms_stats.delete_table_stats(ownname=>'SCOTT',tabname=>'EMP')

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',cascade=>TRUE,no_invalidate=>FALSE,method_opt => 'for all columns size 1',estimate_percent=>20);

PL/SQL procedure successfully completed

SQL> select t.column_name,t.num_buckets,t.last_analyzed from dba_tab_col_statistics t where t.table_name = 'EMP' and owner='SCOTT';

COLUMN_NAME                    NUM_BUCKETS LAST_ANALYZED
------------------------------ ----------- -------------
EMPNO                                    1 2012-11-2 2:5
ENAME                                    1 2012-11-2 2:5
JOB                                      1 2012-11-2 2:5
MGR                                      1 2012-11-2 2:5
HIREDATE                                 1 2012-11-2 2:5
SAL                                      1 2012-11-2 2:5
COMM                                     1 2012-11-2 2:5
DEPTNO                                   1 2012-11-2 2:5

8 rows selected

SQL> select t.NUM_ROWS,t.last_analyzed from dba_tables t where t.table_name = 'EMP' and owner='SCOTT';

  NUM_ROWS LAST_ANALYZED
---------- -------------
        14 2012-11-2 2:5
        
以上指定的所有字段均不收集直方图，只收集表的统计信息

下面两种写法均只收集指定字段的直方图，其他字段不收集任何信息
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',cascade=>TRUE,no_invalidate=>FALSE,method_opt => 'for columns size 1 DEPTNO SIZE 2 ',estimate_percent=>20);

PL/SQL procedure successfully completed

SQL> select t.NUM_ROWS,t.last_analyzed from dba_tables t where t.table_name = 'EMP' and owner='SCOTT';

  NUM_ROWS LAST_ANALYZED
---------- -------------
        14 2012-11-2 3:0

SQL> select t.column_name,t.num_buckets,t.last_analyzed from dba_tab_col_statistics t where t.table_name = 'EMP' and owner='SCOTT';

COLUMN_NAME                    NUM_BUCKETS LAST_ANALYZED
------------------------------ ----------- -------------
DEPTNO                                   2 2012-11-2 3:0

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',cascade=>TRUE,no_invalidate=>FALSE,method_opt => 'for columns size 2 DEPTNO ',estimate_percent=>20);

PL/SQL procedure successfully completed

SQL> select t.column_name,t.num_buckets,t.last_analyzed from dba_tab_col_statistics t where t.table_name = 'EMP' and owner='SCOTT';

COLUMN_NAME                    NUM_BUCKETS LAST_ANALYZED
------------------------------ ----------- -------------
DEPTNO                                   2 2012-11-2 3:0

下面的写法才能正确实现需求:
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',cascade=>TRUE,no_invalidate=>FALSE,method_opt => 'for all columns size 1 FOR COLUMNS SIZE 2 DEPTNO JOB',estimate_percent=>20);

PL/SQL procedure successfully completed

SQL> select t.NUM_ROWS,t.last_analyzed from dba_tables t where t.table_name = 'EMP' and owner='SCOTT';

  NUM_ROWS LAST_ANALYZED
---------- -------------
        14 2012-11-2 3:0

SQL> select t.column_name,t.num_buckets,t.last_analyzed from dba_tab_col_statistics t where t.table_name = 'EMP' and owner='SCOTT';

COLUMN_NAME                    NUM_BUCKETS LAST_ANALYZED
------------------------------ ----------- ------------------------------
EMPNO                                    1 2012-11-2 3:06:58
ENAME                                    1 2012-11-2 3:06:58
JOB                                      2 2012-11-2 3:06:58
MGR                                      1 2012-11-2 3:06:58
HIREDATE                                 1 2012-11-2 3:06:58
SAL                                      1 2012-11-2 3:06:58
COMM                                     1 2012-11-2 3:06:58
DEPTNO                                   2 2012-11-2 3:06:58

8 rows selected

Maclean Liu(刘相兵

1、 提问必须说版本！！！！

Maclean Liu(刘相兵

FYI:

SQL> select banner from v$version where rownum=1;

BANNER
------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> drop table maclean ;

表已删除。

SQL> create table maclean as select rownum t1, rownum t2 from dual connect by level<=10000;

表已创建。

SQL> exec dbms_stats.gather_table_stats(user,'MACLEAN',method_opt=>'FOR ALL COLUMNS SIZE 254 FOR COLUMNS SIZE 100 T1 ')

PL/SQL 过程已成功完成。

SQL> select count(*) from dba_tab_histograms where table_name='MACLEAN' and column_name='T1';

  COUNT(*)
----------
       100

SQL> select count(*) from dba_tab_histograms where table_name='MACLEAN' and column_name='T2';

  COUNT(*)
----------
       254

Maclean Liu(刘相兵

你也可以参考下这篇文章 https://blogs.oracle.com/optimizer/entry/how_does_the_method_opt

我对method_opt的具体语法 研究不多

SKYLINE.LIU

for all columns size 1 FOR COLUMNS SIZE 2 DEPTNO JOB 表的所有字段除JOB字段外均不收集直方图，JOB收集直方图为2桶

dba_tab_col_statistics 显示结果中num_buckets为1桶表示没有直方图信息，你可以看一下dba_tab_col_statistics 中对应列 histogram 列的直方图类型，NONE为未收集直方图

雪影舞剑

但我觉得如果bucket=1，至少CBO能够知道这个字段的distinct值，只是不清楚分布
如果完全不收集该字段，那么就没有任何信息了

雪影舞剑

补充版本，以上的测试应该是在10204上的
我现在主要是对11203的统计信息收集策略的考虑

雪影舞剑

看了下ML提示的这篇文:
for all columns size 1 means collect base column statistics on all columns but no histogram
他的测试结果跟我的也类似
应该能印证我的上述观点

Stone

雪影舞剑 发表于 2013-8-18 19:25
看了下ML提示的这篇文:
for all columns size 1 means collect base column statistics on all columns but ...

Skyline的理解和表述应该是没有问题的，你的最终的理解也应该是正确的，不矛盾，不冲突 :)

这是因为当我们收集表的信息的时候，如果不考虑表的skewed column的话，一般情况下不需要加"OPT_METHOD"这个参数。那么不加的时候数据库默认的情况是采用"FOR ALL COLUMNS SIZE AUTO"，这意味着会AUTO收集列的各种basic相关信息，当然这个默认的行为也可以通过"DBMS_STATS.SET_PARAM"来修改的。

那么如果加了OPT_METHOD来收集histograms信息，就要细分到底需不需要再次收集各个列的信息，当然为了信息的准确的话可以考虑通过加上"FOR ALL COLUMNS SIZE 1"，一方面再次收集列basic信息，另一方面也确认不收集"histograms"信息，那么需要进一步指定"FOR COLUMNS SIZE ## Column_name'"来确认到底收集那个列的histogram信息。

另外我的理解对于不加"FOR ALL COLUMNS SIZE 1"这个部分的话，应该不会删除系统原来收集的列的基本信息，当然假定以前收集过，不是第一次收集(这个理解需要用下环境证明下)。当然如果是新的环境的话，还是有必要加上来收集下基本的统计信息。