收集直方图信息的一个注意点

先查看一下deptno列的值的情况：

SQL>  select deptno,count(*) from test group by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30       6144
        20      40960
        10       3072
按照两种不同的estimate_percent进行收集直方图

SQL> exec dbms_stats.gather_table_stats(user,'TEST',cascade=>true,method_opt => 'for columns deptno size 10');

PL/SQL procedure successfully completed.

SQL> select table_name,column_name,ENDPOINT_NUMBER,ENDPOINT_ACTUAL_VALUE from user_histograms where table_name='TEST';

TABLE_NAME COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_ACTUAL_VALU
---------- -------------------- --------------- --------------------
TEST       DEPTNO                           307
TEST       DEPTNO                          4759
TEST       DEPTNO                          5403

SQL> exec dbms_stats.gather_table_stats(user,'TEST',cascade=>true,estimate_percent => null,method_opt => 'for columns deptno size 10');

PL/SQL procedure successfully completed.

SQL> select table_name,column_name,ENDPOINT_NUMBER,ENDPOINT_ACTUAL_VALUE from user_histograms where table_name='TEST';

TABLE_NAME COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_ACTUAL_VALU
---------- -------------------- --------------- --------------------
TEST       DEPTNO                          3072
TEST       DEPTNO                         44032
TEST       DEPTNO                         50176

SQL> exec dbms_stats.gather_table_stats(user,'TEST',cascade=>true,estimate_percent =>100,method_opt => 'for columns deptno size 10');

PL/SQL procedure successfully completed.

SQL> select table_name,column_name,ENDPOINT_NUMBER,ENDPOINT_ACTUAL_VALUE from user_histograms where table_name='TEST';

TABLE_NAME COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_ACTUAL_VALU
---------- -------------------- --------------- --------------------
TEST       DEPTNO                          3072
TEST       DEPTNO                         44032
TEST       DEPTNO                         50176

这两次收集的直方图统计信息之间的差别是由于estimate_percent参数的设置导致的，10g的相关默认参数设置如下：
procedure gather_table_stats
    (ownname varchar2,
     tabname varchar2,
     partname varchar2 default null,
     estimate_percent number default to_estimate_percent_type(get_param('ESTIMATE_PERCENT')),
     block_sample boolean default FALSE,
     method_opt varchar2 default get_param('METHOD_OPT'),
     degree number default to_degree_type(get_param('DEGREE')),
     granularity varchar2 default get_param('GRANULARITY'),
     cascade boolean default to_cascade_type(get_param('CASCADE')),
     stattab varchar2 default null, statid varchar2 default null,
     statown varchar2 default null,
     no_invalidate boolean default to_no_invalidate_type(get_param('NO_INVALIDATE')),
     stattype varchar2 default 'DATA',
     force boolean default FALSE);

根据产生的直方图统计信息来看，oracle自动采用的采样比例似乎为10%左右。

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

转载于:http://blog.itpub.net/10972173/viewspace-610903/