analyze和dbms_stats相互作用和准确度

最新推荐文章于 2024-07-02 07:30:00 发布

逝鸿

最新推荐文章于 2024-07-02 07:30:00 发布

阅读量372

点赞数

文章标签： analyze dbms_stats 删除采样率

本文链接：https://blog.csdn.net/qq_21127313/article/details/79739810

版权

analyze和dbms_stats可以删除对方手机的统计信息吗？验证可以。

SQL> analyze table WORKER compute statistics for table;

Table analyzed.

SQL> select table_name, blocks, empty_blocks, num_rows
  2  from dba_tables
  3  where owner = 'TEST'
  4  and table_name = 'WORKER';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS
------------------------------ ---------- ------------ ----------
WORKER                              312           72      10000

SQL> exec dbms_stats.delete_table_stats (ownname => 'TEST',tabname => 'WORKER');

PL/SQL procedure successfully completed.

SQL> select table_name, blocks, empty_blocks, num_rows
  2  from dba_tables
  3  where owner = 'TEST'
  4  and table_name = 'WORKER';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS
------------------------------ ---------- ------------ ----------
WORKER

SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'WORKER',estimate_percent=>10,degree=>8,cascade=>true,granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select table_name, blocks, empty_blocks, num_rows
  2  from dba_tables
  3  where owner = 'TEST'
  4  and table_name = 'WORKER';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS
------------------------------ ---------- ------------ ----------
WORKER                              312            0      10021

SQL> analyze table WORKER delete statistics;

Table analyzed.

SQL> select table_name, blocks, empty_blocks, num_rows
  2  from dba_tables
  3  where owner = 'TEST'
  4  and table_name = 'WORKER';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS
------------------------------ ---------- ------------ ----------
WORKER

此时又发现了另一个问题，两种方式的收集结果不一样，都说分区表上dbms_stats表现好，来看看普通表上哪个比较准确

SQL> exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'WORKER',estimate_percent=>100,degree=>8,cascade=>true,granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select table_name, blocks, empty_blocks, num_rows
  2  from dba_tables
  3  where owner = 'TEST'
  4  and table_name = 'WORKER';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS
------------------------------ ---------- ------------ ----------
WORKER                              312            0      10000

SQL> select count(*) from WORKER;

  COUNT(*)
----------
     10000

可以推测dbms_stats因采样率不同准确度也不同，analyze此时准确率比较高