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

来源：作者：出处：巧巧读书 2006-07-29 进入讨论组

关键词：
存储过程

　　参考了一下别人的代码又补充了一下写了一个存储过程。
　　
　　分析某一用户下面的表及索引。
　　
　　运行完毕后然后设置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;