dbms_stats和analyze在sample size上的却别

在分析前，我们查询统计信息：

SQL(c3uat)>select table_name,num_rows,sample_size,last_analyzed from user_tables;

TABLE_NAME        NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
--------------- ---------- ----------- -----------------------------
BIG_TABLE           338209       44342 04-JUN-2008:11:21:23
JOB_ERR_LOG              2           2 04-JUN-2008:11:09:25
MY_LOCK                 26          26 04-JUN-2008:11:09:25
MY_SESSION              69          69 04-JUN-2008:11:09:26
MY_SQLTEXT             201         201 04-JUN-2008:11:09:26
PLAN_TABLE             228         228 04-JUN-2008:11:09:26
PRD_USERS               16          16 04-JUN-2008:11:09:27
TEST_TABLE               1           1 04-JUN-2008:11:09:27

8 rows selected.

 

 

我们使用dbms_stats的AUTO_SAMPLE_SIZE(来决定统计样本:

SQL(c3uat)>exec dbms_stats.gather_SCHEMA_STATS(OWNNAME=>'ATCDBA',-
                       OPTIONS=>'GATHER AUTO',-
                       ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,-
                       CASCADE=>TRUE,-
                       DEGREE=>2);

 

SQL(c3uat)>select table_name,num_rows,sample_size,last_analyzed from user_tables;

TABLE_NAME        NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
--------------- ---------- ----------- -----------------------------
BIG_TABLE           662167       57244 04-JUN-2008:13:07:22
JOB_ERR_LOG              2           2 04-JUN-2008:11:09:25
MY_LOCK                 26          26 04-JUN-2008:11:09:25
MY_SESSION              69          69 04-JUN-2008:11:09:26
MY_SQLTEXT             201         201 04-JUN-2008:11:09:26
PLAN_TABLE             228         228 04-JUN-2008:11:09:26
PRD_USERS               16          16 04-JUN-2008:11:09:27
TEST_TABLE               1           1 04-JUN-2008:11:09:27

8 rows selected.

可见系统选择了57244/662167=8.6%的数据进行分析，这是可以接受的。根据oracle资料显示，如设置为ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE，系统将选择5%~15%的数据进行分析。

 

用analyze的缺省sample size来分析表：

SQL(c3uat)>analyze table big_table estimate statistics;

Table analyzed.

SQL(c3uat)>select table_name,num_rows,sample_size,last_analyzed from user_tables;

TABLE_NAME        NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
--------------- ---------- ----------- -----------------------------
BIG_TABLE           665192        1014 04-JUN-2008:13:08:10
JOB_ERR_LOG              2           2 04-JUN-2008:11:09:25
MY_LOCK                 26          26 04-JUN-2008:11:09:25
MY_SESSION              69          69 04-JUN-2008:11:09:26
MY_SQLTEXT             201         201 04-JUN-2008:11:09:26
PLAN_TABLE             228         228 04-JUN-2008:11:09:26
PRD_USERS               16          16 04-JUN-2008:11:09:27
TEST_TABLE               1           1 04-JUN-2008:11:09:27

8 rows selected. 

可见oracle只选择了1000条数据来进行分析，相对于表的size实在是太小。

 

使用oralce的dbms_stats.gather_table_stats缺省sample size来进行分析：

SQL(c3uat)>exec dbms_stats.gather_table_stats(OWNNAME=>'ATCDBA',-
                   TABNAME=>'BIG_TABLE',-
                   -- ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,-
                   CASCADE=>TRUE,-
                   DEGREE=>2);  

SQL(c3uat)>select table_name,num_rows,sample_size,last_analyzed from user_tables;

TABLE_NAME        NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
--------------- ---------- ----------- -----------------------------
BIG_TABLE           663748      663748 04-JUN-2008:13:12:51
JOB_ERR_LOG              2           2 04-JUN-2008:11:09:25
MY_LOCK                 26          26 04-JUN-2008:11:09:25
MY_SESSION              69          69 04-JUN-2008:11:09:26
MY_SQLTEXT             201         201 04-JUN-2008:11:09:26
PLAN_TABLE             228         228 04-JUN-2008:11:09:26
PRD_USERS               16          16 04-JUN-2008:11:09:27
TEST_TABLE               1           1 04-JUN-2008:11:09:27 

系统选择了所有的数据来进行收集统计信息，相当于compute statistics，如果遇到大的表，将导致分析时间过长，影响系统应用。