Oracle 19c统计信息-CSDN博客

本文链接：https://blog.csdn.net/hbhe0316/article/details/122701432

declare
  i number;
  begin
  for i in 1..30000000 loop
   insert into t1 values(i,'name');
  end loop;
  commit;
  end;
  /

SQL> select count(*) from t1;

  COUNT(*)
----------
  30000000

SQL> SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN FROM USER_TAB_STATISTICS WHERE TABLE_NAME = 'T1';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------


SQL> CREATE INDEX index1 ON t1(id);

Index created.

SQL> SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN FROM USER_TAB_STATISTICS WHERE TABLE_NAME = 'T1';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------


SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'T1',estimate_percent=>10,method_opt=>'for all indexed columns');

PL/SQL procedure successfully completed.


 SQL> select table_name,num_rows,blocks,last_analyzed from user_tables where table_name='T1';

TABLE_NAME
--------------------------------------------------------------------------------
  NUM_ROWS     BLOCKS LAST_ANALYZED
---------- ---------- ------------------
T1
  30000000      65945 2021:10:1322:00:11

对hbhe schema下所有的table做统计信息收集

https://www.cndba.cn/hbhe0316/article/4926

SQL > exec dbms_stats.gather_schema_stats(hbhe);

PL/SQL procedure successfully completed.

对sys下的Index1索引做统计信息收集

https://www.cndba.cn/hbhe0316/article/4926

SQL>  exec dbms_stats.gather_index_stats(ownname => 'sys',indname => 'index1',estimate_percent => '10',degree => '4') ;

PL/SQL procedure successfully completed.

dbms_stats.gather_system_stats 收集系统统计信息
dbms_stats.GATHER_DICTIONARY_STATS：所有字典对象的统计；
DBMS_STATS.GATHER_DICTIONARY_STATS 其收集所有系统模式的统计https://www.cndba.cn/hbhe0316/article/4926https://www.cndba.cn/hbhe0316/article/4926

dbms_stats.delete_table_stats 删除表的统计信息
dbms_stats.delete_index_stats 删除索引的统计信息
dbms_stats.export_table_stats 输出表的统计信息
dbms_stats.create_state_table
dbms_stats.set_table_stats 设置表的统计
dbms_stats.auto_sample_size

https://www.cndba.cn/hbhe0316/article/4926

https://www.cndba.cn/hbhe0316/article/4926 https://www.cndba.cn/hbhe0316/article/4926

Linux,oracle