熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

定時執行存儲過程對庫表及索引進行分析

2013-11-13 16:12:26  來源: Oracle 

  參考了一下別人的代碼又補充了一下寫了一個存儲過程
  
  分析某一用戶下面的表及索引
  
  運行完畢後然後設置job即可
  
  create or replace procedure DBA_ANAYZE_SCHEMA(v_USERNAME VARCHAR)
  AS
  v_per number() DEFAULT ;
  v_start number := dbms_utilityget_time;
  v_end  number;
  begin
  /*********************
  
  該存儲過程主要是對表及索引進行分析
  
  對於包含有子分區subpartition的表需要注意一下granularity參數具體參考
  
    granularity the granularity of statistics to collect (only pertinent
     if the table is partitioned)
     DEFAULT gather global and partitionlevel statistics
     SUBPARTITION gather subpartitionlevel statistics
     PARTITION gather partitionlevel statistics
     GLOBAL gather global statistics
     ALL gather all (subpartition partition and global) statistics
  *******************************/
  for rec in (select segment_namesegment_typeceil(sum(bytes)//) segment_size
  from user_segments where SEGMENT_NAME NOT LIKE TMP_%
  group by segment_namesegment_type)
  loop
  CASE WHEN recsegment_type = INDEX THEN
  case
  when recsegment_size <= then
  v_per := ;
  when recsegment_size <= then
  v_per := ;
  else
  v_per := ;
  end case;
  begin
  delete old schema index statistics;
  DBMS_STATSdelete_index_stats(ownname => upper(v_USERNAME)
  indname => recsegment_name);
  exception
  when others then
  null;
  end;
  begin
  analyze index compute statistics;
  dbms_statsgather_index_stats(ownname=>upper(v_USERNAME) 自己改一下
  INDNAME=>recsegment_name
  estimate_percent =>v_per
  degree =>      );
  exception
  when others then
  null;
  end;
  dbms_outputput_line(recsegment_name|| ||recsegment_size||m ||ceil((dbms_utilityget_time v_start)/)||s);
  v_start := dbms_utilityget_time;
  WHEN recsegment_type = TABLE then
  
  case when recsegment_size <= then
  v_per := ;
  when recsegment_size <= then
  v_per := ;
  else
  v_per := ;
  end case;
  
  begin
  delete table analyze statistics
  dbms_statsdelete_table_stats(ownname =>upper(v_USERNAME)
  tabname =>recsegment_name);
  exception
  when others then
  null;
  end;
  
  begin
  analyze table compute statistics;
  dbms_statsgather_table_stats(OWNNAME=>upper(v_USERNAME)
  TABNAME=>recsegment_name
  ESTIMATE_PERCENT=>v_per
  cascade => TRUE
  granularity => ALL
  degree =>
  METHOD_OPT=>FOR ALL INDEXED COLUMNS);
  exception
  when others then
  null;
  end;
   dbms_outputput_line(recsegment_name|| ||recsegment_size||m ||ceil((dbms_utilityget_time v_start)/)||s);
  v_start := dbms_utilityget_time;
  WHEN recsegment_type = TABLE PARTITION then
  case when recsegment_size <= then
  v_per := ;
  when recsegment_size <= then
  v_per := ;
  else
  v_per := ;
  end case;
  begin
  delete table analyze statistics
  dbms_statsdelete_table_stats(ownname =>upper(v_USERNAME)
  tabname =>recsegment_name);
  exception
  when others then
  null;
  end;
  begin
  analyze table compute statistics;
  dbms_statsgather_table_stats(OWNNAME=>upper(v_USERNAME)
  TABNAME=>recsegment_name
  ESTIMATE_PERCENT=>v_per
  cascade => TRUE
  granularity => ALL
  degree => DBMS_STATSDEFAULT_DEGREE
  METHOD_OPT=>FOR ALL INDEXED COLUMNS);
  exception
  when others then
  null;
  end;
  
  WHEN recsegment_type = INDEX PARTITION then
  case
  when recsegment_size <= then
  v_per := ;
  when recsegment_size <= then
  v_per := ;
  else
  v_per := ;
  end case;
  begin
  delete old schema index statistics;
  DBMS_STATSdelete_index_stats(ownname => upper(v_USERNAME)
  indname => recsegment_name);
  exception
  when others then
  null;
  end;
  
  begin
  analyze index compute statistics;
  dbms_statsgather_index_stats(ownname=>upper(v_USERNAME) 自己改一下
  INDNAME=>recsegment_name
  estimate_percent =>v_per
  degree =>dbms_statsDEFAULT_DEGREE
  );
  exception
  when others then
  null;
  end;
      dbms_outputput_line(recsegment_name|| ||recsegment_size||m ||ceil((dbms_utilityget_time v_start)/)||s);
  v_start := dbms_utilityget_time;
  /** WHEN recsegment_type = LOBINDEX then
  v_start := dbms_utilityget_time;
  WHEN recsegment_type = LOBSEGMENT then
  v_start := dbms_utilityget_time;**/
  END CASE;
  end loop;
  end;
From:http://tw.wingwit.com/Article/program/Oracle/201311/17964.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.