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