Oracle收集统计信息的一些思考
一、问题
- Oracle在收集统计信息时默认的采样比例是DBMS_STATS.AUTO_SAMPLE_SIZE，那么AUTO_SAMPLE_SIZE的值具体是多少？
- 假设采样比例为10%，那么在计算单个列的distinct时与实际的差别大吗？
- 有哪些采样算法？
二、实验
准备三张实验表，t1/t2/t3，这三张表的数据内容完全一致，我们分别使用100%、10%、AUTO_SAMPLE_SIZE的比例去收集他们的统计信息。
SQL> begin
2 dbms_stats.gather_table_stats(
3 ownname => 'BAO',
4 tabname => 'T1',
5 estimate_percent => 100
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_stats.gather_table_stats(
3 ownname => 'BAO',
4 tabname => 'T2',
5 estimate_percent => 10
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_stats.gather_table_stats(
3 ownname => 'BAO',
4 tabname => 'T3',
5 estimate_percent => dbms_stats.auto_sample_size
6 );
7 end;
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_NAME NUM_ROWS SAMPLE_SIZE
-------------- ---------- -----------
T1 145334 145334
T2 146190 14619
T3 145334 145334
官方文档并没有说明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",
2 round((a.num_distinct - b.num_distinct) * 100 / a.num_distinct, 1) "diff",
3 a.sample_size "t1.sample_size", b.sample_size "t2.sample_size"
4 from (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')) b
6 where a.column_name = b.column_name and a.num_distinct > 0 order by "diff" desc;
COLUMN_NAME t1.num_distinct t2.num_distinct diff t1.sample_size t2.sample_size
------------------------------ --------------- --------------- ---------- -------------- --------------
OBJECT_NAME 64552 10300 84 145334 14619
SUBOBJECT_NAME 1015 385 62.1 68251 6856
TIMESTAMP 2585 1240 52 145212 14610
LAST_DDL_TIME 2490 1257 49.5 145212 14610
CREATED 2312 1209 47.7 145334 14619
NAMESPACE 21 15 28.6 145212 14610
OBJECT_TYPE 45 39 13.3 145334 14619
OWNER 80 71 11.3 145334 14619
TEMPORARY 2 2 0 145334 14619
DUPLICATED 1 1 0 145334 14619
STATUS 2 2 0 145334 14619
SHARDED 1 1 0 145334 14619
GENERATED 2 2 0 145334 14619
SECONDARY 1 1 0 145334 14619
SHARING 4 4 0 145334 14619
EDITIONABLE 2 2 0 25433 2531
ORACLE_MAINTAINED 2 2 0 145334 14619
APPLICATION 1 1 0 145334 14619
DEFAULT_COLLATION 1 1 0 16886 1705
DATA_OBJECT_ID 77785 78100 -.4 77822 7813
OBJECT_ID 145212 146100 -.6 145212 14610
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_CCD
690 S_ABA_CED
690 S_ACA_CCD
690 S_ADA_CCD
690 PK_AEA_CED
...
1 GV_$CON_SYSSTAT
1 GV_$DATAFILE
1 GV_$TABLESPACE
1 GV_$ROLLSTAT
1 GV_$PARAMETER
可以看到OBJECT_NAME这个列的数据分布极不均匀。因此对于分布不均匀的列，通过部分采样方式得到的distinct值与实际的distinct值差别就会比较大。这就回答了文章的第二个问题。
三、采样算法
以下是个人的一些娱乐性思考
-
等比放大，即(采样得到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值就不准。这种算法有明显的缺陷。 -
按增长率估算，即将采样得到的前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代码。
import random
import cx_Oracle
def func(ins):
SAMPLE_PERCENT = 10 # 采样比例%
sample_size = int(len(ins) * SAMPLE_PERCENT / 100)
# 对数据进行采样
sample = random.sample(ins, sample_size)
head_half_sample = sample[0:int(len(sample)/2)] # 采样数据的前一半
head_half_sample_distinct = len(set(head_half_sample)) # 采样数据的前一半的distinct值
full_sample_distinct = len(set(sample)) # 采样数据的全量distinct值
tail_half_inc = full_sample_distinct - head_half_sample_distinct # 采样数据的distinct增量
estimate_distinct = round(head_half_sample_distinct + tail_half_inc * (100 - SAMPLE_PERCENT/2) / (SAMPLE_PERCENT/2))
return estimate_distinct
def test(colname):
DATABASE_URL = 'xxxxx'
conn = cx_Oracle.connect(DATABASE_URL)
curs = conn.cursor()
sql = 'select {} from t1'.format(colname)
curs.execute(sql)
tmpdata = []
for i in curs.fetchall():
tmpdata.append(i[0])
res = func(tmpdata)
curs.close()
conn.close()
return res
for i in ['OBJECT_NAME', 'SUBOBJECT_NAME', 'TIMESTAMP', 'LAST_DDL_TIME', 'CREATED', 'NAMESPACE', 'OBJECT_TYPE',
'OWNER', 'TEMPORARY', 'DUPLICATED', 'STATUS', 'SHARDED', 'GENERATED', 'SECONDARY', 'SHARING', 'EDITIONABLE',
'ORACLE_MAINTAINED', 'APPLICATION', 'DEFAULT_COLLATION', 'DATA_OBJECT_ID', 'OBJECT_ID']:
print(i, '估算的distinct->', test(i))
运行结果
再来跟之前的一个表格进行对比，按增长率的方式估算的distinct值看上去也能接受。
COLUMN_NAME 实际的distinct 数据库估算的distinct python估算的distinct
------------------------------ --------------- -------------------- --------------------
OBJECT_NAME 64552 10300 74865
SUBOBJECT_NAME 1015 385 1210
TIMESTAMP 2585 1240 5641
LAST_DDL_TIME 2490 1257 5536
CREATED 2312 1209 4983
NAMESPACE 21 15 89
OBJECT_TYPE 45 39 92
OWNER 80 71 176
TEMPORARY 2 2 2
DUPLICATED 1 1 1
STATUS 2 2 2
SHARDED 1 1 1
GENERATED 2 2 2
SECONDARY 1 1 1
SHARING 4 4 4
EDITIONABLE 2 2 3
ORACLE_MAINTAINED 2 2 2
APPLICATION 1 1 1
DEFAULT_COLLATION 1 1 2
DATA_OBJECT_ID 77785 78100 77752
OBJECT_ID 145212 146100 145178
限于时间，测试到此结束。后面有时间再学点统计相关的知识。
