Oracle为什么不按照estimate_percent来分析表？

之前在用户现场，被问道“estimate_percent有时候不按照我们指定值来收集统计信息”时，当时以为客户环境设置有问题，没多考虑。

今天小研究了一下，还真不按照设置来，在这里简单总结并说明了一下原因。

DB 11.2.0.3.0

redhat 5.7

创建一个测试表：

SQL> conn user1/oracle
Connected.
SQL> create table test1 as select * from dba_objects;

Table created.

SQL> col table_name for a10
SQL> select table_name, num_rows, sample_size, sample_size/num_rows from dba_tables where owner = 'USER1';

TABLE_NAME   NUM_ROWS SAMPLE_SIZE SAMPLE_SIZE/NUM_ROWS
---------- ---------- ----------- --------------------
T1             298240      298240                    1
SEND
TEST1

这是未收集前的状态，test1相关列为空。收集后：

SQL> exec dbms_stats.gather_table_stats(ownname => 'USER1', tabname => 'TEST1', estimate_percent => 10, method_opt => 'for all indexed columns size auto', cascade => true); 

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, sample_size, sample_size/num_rows from dba_tables where owner = 'USER1';

TABLE_NAME   NUM_ROWS SAMPLE_SIZE SAMPLE_SIZE/NUM_ROWS
---------- ---------- ----------- --------------------
T1             298240      298240                    1
SEND
TEST1           13814        4820           .348921384

收集后可以看到num_rows有值了，奇怪的是为什么采样比率是34.89%?而不是我设置的10%呢？

SQL> select count(*) from test1;

  COUNT(*)
----------
     13808          <<<< 实际数据量

SQL> exec dbms_stats.gather_table_stats(ownname => 'USER1', tabname => 'TEST1', estimate_percent => 10, method_opt => 'for all indexed columns size auto', cascade => true); 

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, sample_size, sample_size/num_rows from dba_tables where owner = 'USER1';

TABLE_NAME   NUM_ROWS SAMPLE_SIZE SAMPLE_SIZE/NUM_ROWS
---------- ---------- ----------- --------------------
T1             298240      298240                    1
SEND
TEST1           13590        4775           .351361295

重新用相同的采样比收集，采样比率改变为35.13%，那我设置的10%没起作用？

尝试一下10046：

SQL> alter session set tracefile_identifier = 'gather_table_stats';

Session altered.

SQL> alter session set events '10046 trace name context forever';

Session altered.

SQL> exec dbms_stats.gather_table_stats(ownname => 'USER1', tabname => 'TEST1', estimate_percent => 10, method_opt => 'for all indexed columns size auto', cascade => true); 

PL/SQL procedure successfully completed.

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL> select table_name, num_rows, sample_size, sample_size/num_rows from dba_tables where owner = 'USER1';

TABLE_NAME   NUM_ROWS SAMPLE_SIZE SAMPLE_SIZE/NUM_ROWS
---------- ---------- ----------- --------------------
T1             298240      298240                    1
SEND
TEST1           13891        4986           .358937442

SQL>