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，如果遇到大的表，将导致分析时间过长，影响系统应用。