Oracle 19c统计信息

原创

wx62e28ac222a20 2022-08-04 09:59:16 ©著作权

文章标签 python java linux 算法大数据 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者wx62e28ac222a20的原创作品，请联系作者获取转载授权，否则将追究法律责任

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做统计信息收集

SQL > exec dbms_stats.gather_schema_stats(hbhe);

PL/SQL procedure successfully completed.

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

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 其收集所有系统模式的统计

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

Linux,oracle