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

本文主要是介绍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           .358937442SQL> 

这篇关于Oracle为什么不按照estimate_percent来分析表？的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---