全部博文（389）
分类： Oracle
2013-11-28 10:40:27
Oracle Optimizer 关于SAMPLE SIZE大小的计算
我们知道在收集statistics的时候，一般我们会使用oracle的default sample size来对收集的对像进行取样.
SQL> select dbms_stats.get_prefs('ESTIMATE_PERCENT') from dual;
DBMS_STATS.GET_PREFS('ESTIMATE
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE
default sample size的值由oracle根据hash算法(有相应的资料的朋友可以共享一下)来决定取样的大小，
我们来实验一下auto_sample_size,10%,55%,100%的几种取样的大小反映对像的精确性程度.
创建一个表，并t1,并且insert大量数据，为了创建更多的块，我把pctfree设置的比较高
create table T1
(
owner VARCHAR2(30),
object_name VARCHAR2(128),
subobject_name VARCHAR2(30),
object_id NUMBER,
data_object_id NUMBER,
object_type VARCHAR2(19),
created DATE,
last_ddl_time DATE,
timestamp VARCHAR2(19),
status VARCHAR2(7),
temporary VARCHAR2(1),
generated VARCHAR2(1),
secondary VARCHAR2(1),
namespace NUMBER,
edition_name VARCHAR2(30)
)
tablespace USERS
pctfree 80
SQL>insert into frank.t1 select * from dba_objects;
SQL> exec dbms_stats.gather_table_stats('FRANK','T1');
PL/SQL procedure successfully completed
SQL> select owner,table_name,blocks,num_rows from dba_tables where table_name='T1' and owner='FRANK';
OWNER TABLE_NAME BLOCKS NUM_ROWS
------------------------------ ------------------------------ ---------- ----------
FRANK T1 302855 4446592
oracle为我们计算出这个表当前的块是302855,行数是4446592.
查一下表真实的行和块是不是被统计出来了
SQL> select count(*) from t1;
COUNT(*)
----------
4446592
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from t1;
COUNT(DISTINCTDBMS_ROWID.ROWID
------------------------------
302117
可以看出数是相等，但是块数有一点点区别
接下来看看10%的sample size的准确性
SQL> exec dbms_stats.delete_table_stats('FRANK','T1');
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats(ownname=>'FRANK',tabname=>'T1',estimate_percent=>10);
PL/SQL procedure successfully completed
SQL> select owner,table_name,blocks,num_rows from dba_tables where table_name='T1' and owner='FRANK';
OWNER TABLE_NAME BLOCKS NUM_ROWS
------------------------------ ------------------------------ ---------- ----------
FRANK T1 302855 4441530
结果表明块数和行数都和真实的数据有些差别，在实验中我同时也观察到num_rows这个数值每次都是不一样.
最后我们来看一下55%的取样比例，在oracle的资料中表明，当sample size大于50%的时候，oracle就会使用100%的取样值,
我们来看一下是不是能准确的反映表的真实情况
SQL> exec dbms_stats.delete_table_stats('FRANK','T1');
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats(ownname=>'FRANK',tabname=>'T1',estimate_percent=>55);
PL/SQL procedure successfully completed
SQL> select owner,table_name,blocks,num_rows from dba_tables where table_name='T1' and owner='FRANK';
OWNER TABLE_NAME BLOCKS NUM_ROWS
------------------------------ ------------------------------ ---------- ----------
FRANK T1 302855 4445438
可以看到块数也是302855,行数更接近真实的行数
SQL> exec dbms_stats.delete_table_stats('FRANK','T1');
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats(ownname=>'FRANK',tabname=>'T1',estimate_percent=>100);
PL/SQL procedure successfully completed
SQL> select owner,table_name,blocks,num_rows from dba_tables where table_name='T1' and owner='FRANK';
OWNER TABLE_NAME BLOCKS NUM_ROWS
------------------------------ ------------------------------ ---------- ----------
FRANK T1 302855 4446592
当我们使用真正的100的sample size 大小的时候发现num_rows已经精确的反映了真实数据，blocks还是有一些区别
难道是我的理解的blocks的计算方式有误么(求探讨)?官方文档上对blocks是这么解答的 blocks ,Number of used data blocks in the table
也观察到随着取样值的增大，花费的时间更加多。当然这是很正常的，在不然都采用最大值就可以了.:)
一般oracle的default的sample size都能很好的工作了，对于一些特别敏感的表，可能需要多注意，oracle是否精确的计算到表数据的真实
情况，对于误差比较大的情况，可以使用set_table_prefs来为表单独设定sample size的大小，一般 10-15%是一个比较好的起点.
,在资源允许的情况，适当增大.
