Oracle收集统计信息的一些思考 _生活百科

一、问题

Oracle在收集统计信息时默认的采样比例是DBMS_STATS.AUTO_SAMPLE_SIZE ，那么AUTO_SAMPLE_SIZE的值具体是多少？
假设采样比例为10% ，那么在计算单个列的distinct时与实际的差别大吗？
有哪些采样算法？

二、实验准备三张实验表， t1/t2/t3 ，这三张表的数据内容完全一致，我们分别使用100%、10%、AUTO_SAMPLE_SIZE的比例去收集他们的统计信息。

SQL> begin2dbms_stats.gather_table_stats(3ownname => 'BAO',4tabname => 'T1',5estimate_percent => 1006);7end;8/PL/SQL procedure successfully completed.SQL> begin2dbms_stats.gather_table_stats(3ownname => 'BAO',4tabname => 'T2',5estimate_percent => 106);7end;8/PL/SQL procedure successfully completed.SQL> begin2dbms_stats.gather_table_stats(3ownname => 'BAO',4tabname => 'T3',5estimate_percent => dbms_stats.auto_sample_size6);7end;8/PL/SQL procedure successfully completed.

查看这三张表的统计信息，可以看到采用100%和AUTO_SAMPLE_SIZE这两种方式收集的统计信息的SAMPLE_SIZE相同，都是全量收集。

SQL> select table_name, num_rows, sample_size from user_tables where table_name in ('T1', 'T2', 'T3');TABLE_NAMENUM_ROWSSAMPLE_SIZE-----------------------------------T1145334145334T214619014619T3145334145334

官方文档并没有说明AUTO_SAMPLE_SIZE具体的值是多少，但是从实验结果来看，这个值就是100 。这就回答了文章的第一个问题。
Oracle为什么会默认采用100%的方式来收集统计信息呢，在ASKTOM有同行就提出过这个问题“DBMS_STATS.AUTO_SAMPLE_SIZE seems to always generate 100%” ，他们的回复是为了得到精确的distinct列值。接下来我们就来看下全量采集和部分采集列的distinct区别。

SQL> select a.column_name, a.num_distinct "t1.num_distinct", b.num_distinct "t2.num_distinct",2round((a.num_distinct - b.num_distinct) * 100 / a.num_distinct, 1) "diff",3a.sample_size "t1.sample_size", b.sample_size "t2.sample_size"4from (select table_name, column_name, num_distinct, sample_size from user_tab_col_statistics where table_name in ('T1')) a,5(select table_name, column_name, num_distinct, sample_size from user_tab_col_statistics where table_name in ('T2')) b6where a.column_name = b.column_name and a.num_distinct > 0 order by "diff" desc;COLUMN_NAMEt1.num_distinct t2.num_distinctdiff t1.sample_size t2.sample_size------------------------------ --------------- --------------- ---------- -------------- --------------OBJECT_NAME64552103008414533414619SUBOBJECT_NAME101538562.1682516856TIMESTAMP258512405214521214610LAST_DDL_TIME2490125749.514521214610CREATED2312120947.714533414619NAMESPACE211528.614521214610OBJECT_TYPE453913.314533414619OWNER807111.314533414619TEMPORARY22014533414619DUPLICATED11014533414619STATUS22014533414619SHARDED11014533414619GENERATED22014533414619SECONDARY11014533414619SHARING44014533414619EDITIONABLE220254332531ORACLE_MAINTAINED22014533414619APPLICATION11014533414619DEFAULT_COLLATION110168861705DATA_OBJECT_ID7778578100-.4778227813OBJECT_ID145212146100-.614521214610

T1表是全量收集， T2表是按10%的比例收集，从上面的结果可以看到，对于大部分字段通过部分采样的方式都能估算得很准确。但对于OBJECT_NAME这个列，估算出来的值和全量统计的差别很大，我们来看一下是什么原因导致的。

SQL> select count(*), object_name from t1 group by object_name order by count(*) desc;COUNT(*) OBJECT_NAME---------- -----------------690 S_AAA_CCD690 S_ABA_CED690 S_ACA_CCD690 S_ADA_CCD690 PK_AEA_CED...1 GV_$CON_SYSSTAT1 GV_$DATAFILE1 GV_$TABLESPACE1 GV_$ROLLSTAT1 GV_$PARAMETER

可以看到OBJECT_NAME这个列的数据分布极不均匀。因此对于分布不均匀的列，通过部分采样方式得到的distinct值与实际的distinct值差别就会比较大。这就回答了文章的第二个问题。