干 Oracle 运维这些年，我最怕的从来不是集群故障、高并发这种 “明枪”，而是统计信息这种 “暗箭”。

你肯定也遇过这种糟心事儿：

• 同一条 SQL，昨天还秒级返回，今天突然超时几十倍；
• 索引建了一堆，优化器偏偏放着不走，非要跑全表扫描；
• 刚迁移完的库，业务直接卡成 PPT，排查半天才发现，统计信息没跟过来；
• 自动收集任务半夜跑，抢光了业务资源，白天高峰直接崩。

这些问题，十有八九都和统计信息有关。它是 Oracle 优化器的 “眼睛”，数据分布、表大小、索引效率全靠它判断，一旦过期、缺失或失效，执行计划直接跑偏，再完美的 SQL 和索引都是白搭。

1) 分区表的统计信息实例

1、收集统计信息
BEGIN
dbms_stats.gather_table_stats(  ownname          => 'NC60',
                                tabname          => 'TEST',
                                estimate_percent => 100,  
                                ##--百分之百采样
                                block_sample     => FALSE,
                                method_opt       => 'FOR ALL COLUMNS SIZE 10',
                                ##--收集直方图
                                granularity      => 'ALL',                     
                                ##--所有分区
                                cascade          => TRUE                       
                                ##--收集索引
                             );
END;

2、表级统计信息
select table_name, num_rows, blocks, empty_blocks, avg_space，last_analyzed
  from user_tables
 where table_name = 'TEST';

3、表上列统计信息
select table_name,column_name,num_distinct,density 
 from user_tab_columns 
 where table_name = 'TEST';

4、表上列直方图信息(OBJECT_ID列)
col TABLE_NAME format a20
col COLUMN_NAME format a40
select table_name, column_name, endpoint_number, endpoint_value
  from user_tab_histograms
 where table_name = 'TEST'
   and column_name = 'OBJECT_ID';

5、分区的统计信息
select partition_name,num_rows,blocks,empty_blocks,avg_space, last_analyzed
 from user_tab_partitions
 where table_name = 'TEST';

6、分区上列的统计信息
select column_name,num_distinct,density,num_nulls
 from user_part_col_statistics
 where table_name = 'TEST'
 and partition_name = 'P1';

7、分区上列的直方图信息(OBJECT_ID列)
select column_name,bucket_number,endpoint_value
 from user_part_histograms
 where table_name = 'TEST'
 and partition_name = 'P1'
 and column_name = 'OBJECT_ID';

8、子分区的统计信息
select subpartition_name,num_rows,blocks,empty_blocks
 from user_tab_subpartitions
 where table_name = 'TEST'
 and partition_name = 'P1';

9、子分区上列的统计信息
select column_name,num_distinct,density
 from user_subpart_col_statistics
 where table_name = 'TEST'
 and subpartition_name = 'SYS_SUBP21';

10、子分区上的列的直方图信息
select column_name,bucket_number,endpoint_value
 from user_subpart_histograms
 where table_name = 'TEST'
 and subpartition_name = 'SYS_SUBP21'
 and column_name = 'OBJECT_ID';

2) ​​​​​​​分区索引相关信息及统计信息是否失效

select t2.table_name,
       t1.index_name,
       t1.partition_name,
       t1.last_analyzed,
       t1.blevel,
       t1.num_rows,
       t1.leaf_blocks,
       t1.status       
from user_ind_partitions t1, user_indexes t2
where t1.index_name = t2.index_name
and t2.table_name='RANGE_PART_TAB';

3) ​​​​​​​查找丢失或失效统计信息的表

select m.TABLE_OWNER,
       m.TABLE_NAME,
       m.INSERTS,
       m.UPDATES,
       m.DELETES,
       m.TRUNCATED,
       m.TIMESTAMP as LAST_MODIFIED,
       round((m.inserts + m.updates + m.deletes) * 100 /
             NULLIF(t.num_rows, 0),
             2) as EST_PCT_MODIFIED,
       t.num_rows as last_known_rows_number,
       t.last_analyzed
  From dba_tab_modifications m, dba_tables t
 where m.table_owner = t.owner
   and m.table_name = t.table_name
   and table_owner not in ('SYS', 'SYSTEM')
   and ((m.inserts + m.updates + m.deletes) * 100 / NULLIF(t.num_rows, 0) > 10 or
       t.last_analyzed is null)
 order by timestamp desc;

##下面这个脚本添加了表是否分区表信息
select m.TABLE_OWNER,
       'NO' as IS_PARTITION,
       m.TABLE_NAME as NAME,
       m.INSERTS,
       m.UPDATES,
       m.DELETES,
       m.TRUNCATED,
       m.TIMESTAMP as LAST_MODIFIED,
       round((m.inserts + m.updates + m.deletes) * 100 /
             NULLIF(t.num_rows, 0),
             2) as EST_PCT_MODIFIED,
       t.num_rows as last_known_rows_number,
       t.last_analyzed
  From dba_tab_modifications m, dba_tables t
 where m.table_owner = t.owner
   and m.table_name = t.table_name
   and m.table_owner not in ('SYS', 'SYSTEM')
   and ((m.inserts + m.updates + m.deletes) * 100 / NULLIF(t.num_rows, 0) > 10 or
       t.last_analyzed is null)
union
select m.TABLE_OWNER,
       'YES' as IS_PARTITION,
       m.PARTITION_NAME as NAME,
       m.INSERTS,
       m.UPDATES,
       m.DELETES,
       m.TRUNCATED,
       m.TIMESTAMP as LAST_MODIFIED,
       round((m.inserts + m.updates + m.deletes) * 100 /
             NULLIF(p.num_rows, 0),
             2) as EST_PCT_MODIFIED,
       p.num_rows as last_known_rows_number,
       p.last_analyzed
  From dba_tab_modifications m, dba_tab_partitions p
 where m.table_owner = p.table_owner
   and m.table_name = p.table_name
   and m.PARTITION_NAME = p.PARTITION_NAME
   and m.table_owner not in ('SYS', 'SYSTEM')
   and ((m.inserts + m.updates + m.deletes) * 100 / NULLIF(p.num_rows, 0) > 10 or
       p.last_analyzed is null)
 order by 8 desc;

4) ​​​​​​​获取SQL相关的表及索引、栏位等信息

set lines 200 pages 200
break on t_name
col t_name for a32
col i_name for a24
col c_name for a24     
col c_type for a8
col last_ana for a10  
col t_blk  for a7
col t_row  for a7
col C_NDV  for a7
col C_NUL  for a7
col I_BLK  for a7
col I_NDV  for a7
col pos    for 999
with table_list as
 (select /*+ materialize */
   OWNER table_owner, table_name
    from dba_tables
   where table_name = upper(trim('&tname'))
     and nvl('&owner', owner) = owner),
t as
 (select /*+ materialize */
   owner, t.table_name, num_rows, last_analyzed, blocks
    from dba_tables t, table_list p
   where t.owner = p.table_owner
     and t.table_name = p.table_name),
ic as
 (select /*+ materialize */
   ic.table_owner,
   ic.table_name,
   column_name,
   index_owner,
   index_name,
   column_position
    from dba_ind_columns ic, table_list p
   where ic.table_owner = p.table_owner
     and ic.table_name = p.table_name),
tc as
 (select /*+ materialize */
   table_owner,
   tc.table_name,
   column_name,
   num_distinct,
   num_nulls,
   substr(DATA_TYPE, 1, 8) data_type,
   tc.density
    from dba_tab_columns tc, table_list p
   where tc.owner = p.table_owner
     and tc.table_name = p.table_name
  union all
  select ic.table_owner,
         ic.table_name,
         ic.column_name,
         ic2.num_distinct,
         ic2.num_nulls,
         'FUNCTION',
         ic2.density
    from ic, dba_tab_col_statistics ic2
   where ic.column_name like 'SYS_NC%'
     and ic.table_name = ic2.table_name(+)
     and ic.table_owner = ic2.owner(+)
     and ic.column_name = ic2.column_name(+)),
i as
 (select /*+ materialize */
   i.table_owner,
   i.table_name,
   index_name,
   owner,
   distinct_keys,
   leaf_blocks
    from dba_indexes i, table_list p
   where i.table_owner = p.table_owner
     and i.table_name = p.table_name)
select /*+ordered use_hash(t tc)*/
 t.owner || '.' || t.table_name t_name,
 trim(to_char(t.blocks,
              case
                when t.blocks < 1e4 then
                 '9,999'
                else
                 '9.9EEEE'
              end)) t_blk,
 trim(to_char(t.num_rows,
              case
                when t.num_rows < 1e4 then
                 '9,999'
                else
                 '9.9EEEE'
              end)) t_row,
 i.index_name i_name,
 trim(to_char(i.leaf_blocks,
              case
                when i.leaf_blocks < 1e4 then
                 '9,999'
                else
                 '9.9EEEE'
              end)) I_BLK,
 trim(to_char(i.distinct_keys,
              case
                when i.distinct_keys < 1e4 then
                 '9,999'
                else
                 '9.9EEEE'
              end)) I_NDV,
 ic.column_position pos,
 tc.column_name c_name,
 tc.data_type c_type,
 trim(to_char(tc.num_distinct,
              case
                when tc.num_distinct < 1e4 then
                 '9,999'
                else
                 '9.9EEEE'
              end)) C_NDV,
 trim(to_char(tc.num_NULLS,
              case
                when tc.num_NULLS < 1e4 then
                 '9,999'
                else
                 '9.9EEEE'
              end)) C_NUL,
 trim(to_char(tc.density,
              case
                when tc.density > 1e-3 then
                 '0.999'
                else
                 '9.9EEEE'
              end)) C_SEL,
 trim(to_char(t.last_analyzed, 'YYYYMMDD')) as last_ANA
  from t, tc, ic, i
 where tc.table_owner = t.owner
   and tc.table_name = t.table_name
   and ic.table_owner(+) = tc.table_owner
   and ic.table_name(+) = tc.table_name
   and ic.column_name(+) = tc.column_name
   and i.table_owner(+) = ic.table_owner
   and i.table_name(+) = ic.table_name
   and i.index_name(+) = ic.index_name
 order by t.owner,
          t.table_name,
          i.index_name,
          column_position,
          num_distinct desc;

5) ​​​​​​​expdp/impdp导出导入统计信息

一、创建一个临时表
BEGIN
  DBMS_STATS.CREATE_STAT_TABLE ( 
    ownname => 'MLAN',   
    stattab => 'opt_stats'
);
END;
/

二、导出统计信息
BEGIN
  DBMS_STATS.EXPORT_SCHEMA_STATS ( ownname => 'MLAN',stattab => 'opt_stats');
  END;
/

三、将统计信息导出dmp文件
expdp \"/ as sysdba\" DIRECTORY=BKUP DUMPFILE=opt_stats.dmp TABLES=MLAN.opt_stats


四、将统计信息导入到目标端
impdp \"/ as sysdba\"  DIRECTORY=BKUP DUMPFILE=opt_stats.dmp TABLES=MLAN.opt_stats


五、在目标端将统计信息导入到数据字典中
BEGIN
  DBMS_STATS.IMPORT_SCHEMA_STATS( 
    ownname => 'MLAN',   
    stattab => 'opt_stats' 
);
END;
/

6) ​​​​​​​Oracle 查询每周统计信息收集频率和持续时间

set linesize 200
col REPEAT_INTERVAL for a60
col DURATION for a30
select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

关闭自动统计信息收集
BEGIN
  DBMS_SCHEDULER.DISABLE(
  name => '"SYS"."SATURDAY_WINDOW"',
  force => TRUE);
END;
/
 
 
修改自动统计信息持续时间
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
  name => '"SYS"."MONDAY_WINDOW"',
  attribute => 'DURATION',
  value => numtodsinterval(180,'minute'));
END;  
/
 
修改自动统计信息开始时间
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
  name => '"SYS"."MONDAY_WINDOW"',
  attribute => 'REPEAT_INTERVAL',
  value => 'freq=daily;byday=MON;byhour=21;byminute=0; bysecond=0 ');
END;
/
 
开启自动统计信息收集
BEGIN
  DBMS_SCHEDULER.ENABLE(
  name => '"SYS"."SATURDAY_WINDOW"');
END;
/