电脑故障

位置:IT落伍者 >> 电脑故障 >> 浏览文章

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


发布日期:2023/5/26
 

参考了一下别人的代码又补充了一下写了一个存储过程

分析某一用户下面的表及索引

运行完毕后然后设置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;

上一篇:如何准备OCP考试?

下一篇:设置不使用spfile