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