1. 首頁 > 生活百科 > >

Oracle收集統計信息的一些思考

生活百科

一、問題

  1. Oracle在收集統計信息時默認的采樣比例是DBMS_STATS.AUTO_SAMPLE_SIZE，那么AUTO_SAMPLE_SIZE的值具體是多少？
  2. 假設采樣比例為10%，那么在計算單個列的distinct時與實際的差別大嗎？
  3. 有哪些采樣算法？
二、實驗準備三張實驗表，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-.614521214610T1表是全量收集，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【Oracle收集統計信息的一些思考】可以看到OBJECT_NAME這個列的數據分布極不均勻 。因此對于分布不均勻的列，通過部分采樣方式得到的distinct值與實際的distinct值差別就會比較大 。這就回答了文章的第二個問題 。
三、采樣算法以下是個人的一些娛樂性思考
  1. 等比放大，即(采樣得到distinct值 / 采樣行數) x 總行數 。舉個例子，假設表有1000行數據，只采樣100行，A列有95個不同的值，即count(distinct A) / count(A) = 95% ， 那么等比放大很容易推導出1000行數據，有950個A的不同值 。但是如果這100行中B列只有2個不同的值，即count(distinct B) / count(B) = 2% ， 那么對于1000行的表來講，B的不同值是不是等于2% * 1000呢？很有可能不是，說不定全表就這兩個不同值，例如性別 。所以通過等比放大得到的distinct值就不準 。這種算法有明顯的缺陷 。
  2. 按增長率估算，即將采樣得到的前5%作為一個基數 ， 采樣得到的后5%作為一個增長率 。（假設采樣比例是10%）還是舉個例子，假設表有1000行數據，只采樣100行 ， 采樣的前50行，C列有40個不同值 。采樣的后50行 ， C列又多了30個不同值，即總共有70個不同值 。那么后面的90%都會保持這個增長速度 。則總體的C列不同值為40 + 30 * ((100-5)/5) = 610 。再來看一種情況，假設采樣的前50行，D列有2個不同值 。采樣的后50行，D列多了0個不同值，即不同值總數保持不變 。那么后面的90%都會保持這個增長速度 。則總體的D列不同值仍為2 。這種方式似乎比等比采樣更加合乎實際情況一點 。接下來就用python去實現這個算法，看看與oracle的估算差別有多大 。以下為python代碼 。

    推薦閱讀