查看搜集统计信息的estimate_percent

estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 

常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.

自动估计要采样的一个segment的最佳百分比：

DBA_TABLES:
SAMPLE_SIZE	NUMBER	Sample size used in analyzing this table


SQL> select owner, table_name, tablespace_name, sample_size
  2    from all_tables
  3   where table_name = 'TEST'
  4     and owner = 'TEST';

OWNER      TABLE_NAME           TABLESPACE_NAME                SAMPLE_SIZE
---------- -------------------- ------------------------------ -----------
TEST       TEST                 TEST                                 72605


 BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'TEST',
                                tabname          => 'TEST',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size repeat',
                                no_invalidate    => FALSE,
                                degree           => 8,
                                cascade          => TRUE);
END;

查看采样率:
SQL> SELECT owner,
  2         table_name,
  3         num_rows,
  4         sample_size,
  5         trunc(sample_size / num_rows * 100) ectimate_percent
  6    FROM DBA_TAB_STATISTICS
  7   where table_name = 'TEST'
  8   and owner='TEST';

OWNER                          TABLE_NAME                       NUM_ROWS SAMPLE_SIZE ECTIMATE_PERCENT
------------------------------ ------------------------------ ---------- ----------- ----------------
TEST                           TEST                                72605       72605              100

改为20呢？


SQL> SELECT owner,
  2         table_name,
  3         num_rows,
  4         sample_size,
  5         trunc(sample_size / num_rows * 100) ectimate_percent
  6    FROM DBA_TAB_STATISTICS
  7   where table_name = 'TEST'
  8   and owner='TEST';

OWNER                          TABLE_NAME                       NUM_ROWS SAMPLE_SIZE ECTIMATE_PERCENT
------------------------------ ------------------------------ ---------- ----------- ----------------
TEST                           TEST                                71720       14344               20


改为自动呢？
 
 BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'TEST',
                                tabname          => 'TEST',
                                estimate_percent => dbms_stats.auto_sample_size,
                                method_opt       => 'for all columns size repeat',
                                no_invalidate    => FALSE,
                                degree           => 8,
                                cascade          => TRUE);
END;

SQL> SELECT owner,
  2         table_name,
  3         num_rows,
  4         sample_size,
  5         trunc(sample_size / num_rows * 100) ectimate_percent
  6    FROM DBA_TAB_STATISTICS
  7   where table_name = 'TEST'
  8   and owner='TEST';

OWNER                          TABLE_NAME                       NUM_ROWS SAMPLE_SIZE ECTIMATE_PERCENT
------------------------------ ------------------------------ ---------- ----------- ----------------
TEST                           TEST                                72605       72605              100

把表搞大呢？
SQL> select segment_name,bytes/1024/1024/1024 from dba_segments where segment_name='TEST' and owner='TEST';

SEGMENT_NAME                                                                      BYTES/1024/1024/1024
--------------------------------------------------------------------------------- --------------------
TEST                                                                                        1.89355469

还是100%
SQL> set linesize 200
SQL> SELECT owner,
  2         table_name,
  3         num_rows,
  4         sample_size,
  5         trunc(sample_size / num_rows * 100) ectimate_percent
  6    FROM DBA_TAB_STATISTICS
  7   where table_name='TEST'
  8   and 
  9    owner='TEST';

OWNER                          TABLE_NAME                       NUM_ROWS SAMPLE_SIZE ECTIMATE_PERCENT
------------------------------ ------------------------------ ---------- ----------- ----------------
TEST                           TEST                              9293440     9293440              100
回单位在研究