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
回单位在研究
