oracle统计信息



收集表的统计信息
begin
  dbms_stats.gather_table_stats(ownname          => 'TESTDATA',
                                tabname          => 'TEST_TABLE',
                                estimate_percent => 100,
                                block_sample     => CASE 'FALSE' WHEN 'TRUE' THEN TRUE ELSE FALSE END,
                                method_opt       => 'FOR ALL COLUMNS SIZE REPEAT',
                                degree           => 1,
                                granularity      => 'ALL',
                                cascade          => TRUE,
                                no_invalidate    => CASE 'FALSE' WHEN 'TRUE' THEN TRUE ELSE FALSE END);
end;
/

如题统计信息为0，则删除统计信息：
declare
  v_num_rows number;
begin
  select num_rows
    into v_num_rows
    from dba_tables
   where owner = 'TESTDATA'
     and table_name = 'TEST_TABLE';
  if v_num_rows = 0 then
    dbms_stats.delete_table_stats('TESTDATA',
                                  'TEST_TABLE',
                                  no_invalidate => CASE WHEN 'Y' = 'Y' THEN FALSE ELSE TRUE END);
  end if;
end;
/

收集索引的统计信息
BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(OWNNAME          => 'TESTDATA',
                                INDNAME          => 'INDEX_TESTCOL',
                                ESTIMATE_PERCENT => 12,
                                DEGREE           => 6,
                                GRANULARITY      => 'ALL',
                                NO_INVALIDATE    => FALSE);
END;
/

创建中中间表
begin
  dbms_stats.create_stat_table(ownname => 'TESTMID',
                               stattab => 'CBO_STATS_BAK');
end;
/

导出索引统计信息保存至中间表
begin
  dbms_stats.export_index_stats(ownname => 'TESTDATA',
                                indname => 'INDEX_TESTCOL',
                                stattab => 'CBO_STATS_BAK',
                                statid  => 'INDEX_TESTCOL',
                                statown => 'TESTMID');
end;
/

将中间表报错的统计信息导入
begin
  dbms_stats.import_index_stats(ownname       => 'TESTDATA',
                                indname       => 'INDEX_TESTCOL',
                                stattab       => 'CBO_STATS_BAK',
                                statid        => 'INDEX_TESTCOL',
                                statown       => 'TESTMID',
                                no_invalidate => true);
end;
/

--###########################
--查询统计信息收集策略
select owner, table_name, num_rows, last_analyzed
  from dba_tab_pending_stats
 where table_name = 'TEST_TABLE';

查询统计信息收集后是否发布启select dbms_stats.get_prefs('publish', 'TESTDATA', 'TEST_TABLE') from dual;

DBMS_STATS.GET_PREFS('PUBLISH'
--------------------------------------------------------------------------------
FALSE

exec dbms_stats.set_table_prefs('TESTDATA','TEST_TABLE','publish','true');

PL/SQL procedure successfully completed

select dbms_stats.get_prefs('publish','TESTDATA','TEST_TABLE') from dual;

DBMS_STATS.GET_PREFS('PUBLISH'
--------------------------------------------------------------------------------
TRUE

select num_rows,last_analyzed from dba_tables where table_name='TEST_TABLE';

  NUM_ROWS LAST_ANALYZED
---------- -------------
     97516 2016/4/18 15:

select owner,table_name,num_rows,last_analyzed from dba_tab_pending_stats where table_name='TEST_TABLE';

OWNER                          TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------
ICSSIBNBDATA                   NB_SB_PAIC_ORG_SYN_INFO             97516 2016/4/18 15:

###################################################################
生产收集表的统计信息脚本：
select

 'BEGIN' || chr(10) ||
 
 'DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ' || '''' || owner || '''' || ',' ||
 chr(10) ||
 
 ' TABNAME => ' || '''' || table_name || '''' || ',' || chr(10) ||
 
 ' ESTIMATE_PERCENT => 100,' || chr(10) ||
 
 ' METHOD_OPT => ' || '''' || 'FOR ALL COLUMNS SIZE REPEAT' || '''' || ', ' ||
 chr(10) ||
 
 ' DEGREE => 8, ' || chr(10) ||
 
 ' GRANULARITY => ' || '''' || 'ALL' || '''' || ', ' || chr(10) ||
 
 ' CASCADE => TRUE, ' || chr(10) ||
 
 ' NO_INVALIDATE => FALSE); ' || chr(10) ||
 
 ' END;' || chr(10) || '/'
  from dba_tables
 where table_name = upper('&table_name');