定时执行存储过程对库表及索引进行分析
2024-07-21 02:38:07
供稿：网友
参考了一下别人的代码又补充了一下写了一个存储过程。
分析某一用户下面的表及索引。
运行完毕后然后设置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;
