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