定时执行存储过程对库表及索引进行分析

2/9/2008来源:Oracle教程人气:6810


  参考了一下别人的代码又补充了一下写了一个存储过程
  
  分析某一用户下面的表及索引。
  
  运行完毕后然后设置job即可。
  
  create or replace PRocedure DBA_ANAYZE_SCHEMA(v_USERNAME VARCHAR2)
  AS
  v_per number(3) DEFAULT 100;
  v_start number := dbms_utility.get_time;
  --v_end  number;
  begin
  /*********************
  
  该存储过程主要是对表及索引进行分析,
  
  对于包含有子分区sub-partition的表需要注重一下granularity参数。具体参考:
  
  --  granularity - the granularity of statistics to collect (only pertinent
  --   if the table is partitioned)
  --   'DEFAULT' - gather global- and partition-level statistics
  --   'SUBPARTITION' - gather subpartition-level statistics
  --   'PARTITION' - gather partition-level statistics
  --   'GLOBAL' - gather global statistics
  --   'ALL' - gather all (subpartition, partition, and global) statistics
  *******************************/
  for rec in (select segment_name,segment_type,ceil(sum(bytes)/1024/1024) segment_size
  from user_segments where SEGMENT_NAME NOT LIKE 'TMP_%'
  group by segment_name,segment_type)
  loop
  CASE WHEN rec.segment_type = 'INDEX' THEN
  case
  when rec.segment_size <=100 then
  v_per := 100;
  when rec.segment_size <=300 then
  v_per := 50;
  else
  v_per := 20;
  end case;
  begin
  --delete old schema index statistics;
  DBMS_STATS.delete_index_stats(ownname => upper(v_USERNAME),
  indname => rec.segment_name);
  exception
  when others then
  null;
  end;
  begin
  --analyze index compute statistics;
  dbms_stats.gather_index_stats(ownname=>upper(v_USERNAME), --自己改一下
  INDNAME=>rec.segment_name,
  estimate_percent =>v_per,
  degree => 2     );
  exception
  when others then
  null;
  end;
  --dbms_output.put_line(rec.segment_name' 'rec.segment_size'm 'ceil((dbms_utility.get_time - v_start)/100)'s');
  v_start := dbms_utility.get_time;
  WHEN rec.segment_type = 'TABLE' then
  --
  case when rec.segment_size <=150 then
  v_per := 100;
  when rec.segment_size <=500 then
  v_per := 50;
  else
  v_per := 20;
  end case;
  
  begin
  --delete table analyze statistics
  dbms_stats.delete_table_stats(ownname =>upper(v_USERNAME),
  tabname =>rec.segment_name);
  exception
  when others then
  null;
  end;
  
  begin
  --analyze table compute statistics;
  dbms_stats.gather_table_stats(OWNNAME=>upper(v_USERNAME),
  TABNAME=>rec.segment_name,
  ESTIMATE_PERCENT=>v_per,
  cascade => TRUE,
  granularity => 'ALL',
  degree => 2,
  METHOD_OPT=>'FOR ALL INDEXED COLUMNS');

  exception
  when others then
  null;
  end;
  -- dbms_output.put_line(rec.segment_name' 'rec.segment_size'm 'ceil((dbms_utility.get_time - v_start)/100)'s');
  v_start := dbms_utility.get_time;
  WHEN rec.segment_type = 'TABLE PARTITION' then
  case when rec.segment_size <=150 then
  v_per := 100;
  when rec.segment_size <=500 then
  v_per := 50;
  else
  v_per := 20;
  end case;
  begin
  --delete table analyze statistics
  dbms_stats.delete_table_stats(ownname =>upper(v_USERNAME),
  tabname =>rec.segment_name);
  exception
  when others then
  null;
  end;
  begin
  --analyze table compute statistics;
  dbms_stats.gather_table_stats(OWNNAME=>upper(v_USERNAME),
  TABNAME=>rec.segment_name,
  ESTIMATE_PERCENT=>v_per,
  cascade => TRUE,
  granularity => 'ALL',
  degree => DBMS_STATS.DEFAULT_DEGREE,
  METHOD_OPT=>'FOR ALL INDEXED COLUMNS');
  exception
  when others then
  null;
  end;
  
  WHEN rec.segment_type = 'INDEX PARTITION' then
  case
  when rec.segment_size <=100 then
  v_per := 100;
  when rec.segment_size <=300 then
  v_per := 50;
  else
  v_per := 20;
  end case;
  begin
  --delete old schema index statistics;
  DBMS_STATS.delete_index_stats(ownname => upper(v_USERNAME),
  indname => rec.segment_name);
  exception
  when others then
  null;
  end;
  
  begin
  --analyze index compute statistics;
  dbms_stats.gather_index_stats(ownname=>upper(v_USERNAME), --自己改一下
  INDNAME=>rec.segment_name,
  estimate_percent =>v_per,
  degree =>dbms_stats.DEFAULT_DEGREE
  );
  exception
  when others then
  null;
  end;
  --    dbms_output.put_line(rec.segment_name' 'rec.segment_size'm 'ceil((dbms_utility.get_time - v_start)/100)'s');
  v_start := dbms_utility.get_time;
  /** WHEN rec.segment_type = 'LOBINDEX' then
  v_start := dbms_utility.get_time;
  WHEN rec.segment_type = 'LOBSEGMENT' then
  v_start := dbms_utility.get_time;**/
  END CASE;
  end loop;
  end;