set serverout on;
declare
v_sql varchar2(300);
v_Errmsg varchar2(300);
begin
delete from user_index_stats;
commit;
for i in (select table_name, index_name, num_rows from user_indexes where index_type like '%NORMAL%' and partitioned='NO'
and not (regexp_like(table_name,'^TMP[[:alnum:]]{27}$')) and temporary='N' and num_rows > 0 order by 3 desc) loop
begin
v_sql := 'analyze index '||i.index_name||' validate structure ';
execute immediate v_sql;
insert into user_index_stats(table_name, height, blocks, index_name, partition_name,
lf_rows, lf_blks, lf_rows_len, lf_blk_len, br_rows, br_blks,
br_rows_len, br_blk_len, del_lf_rows, del_lf_rows_len, distinct_keys,
most_repeated_key, btree_space, used_space, pct_used, rows_per_key, blks_gets_per_access,
pre_rows, pre_rows_len, opt_cmpr_count, opt_cmpr_pctsave)
select i.table_name, height, blocks, name, partition_name,
lf_rows, lf_blks, lf_rows_len, lf_blk_len, br_rows, br_blks,
br_rows_len, br_blk_len, del_lf_rows, del_lf_rows_len, distinct_keys,
most_repeated_key, btree_space, used_space, pct_used, rows_per_key, blks_gets_per_access,
pre_rows, pre_rows_len, opt_cmpr_count, opt_cmpr_pctsave from index_stats;
exception when others then
v_Errmsg :=substr(dbms_utility.format_error_backtrace,1,150);
v_Errmsg :=v_Errmsg||substr(dbms_utility.format_error_stack,1,150);
insert into user_index_stats(table_name, index_name, comments) values (i.table_name, i.index_name, v_Errmsg);
end;
end loop;
commit;
for i in (select table_name, index_name, round(del_lf_rows/lf_rows,2) del_pct, blocks, lf_rows, lf_rows_len,
del_lf_rows, del_lf_rows_len, (lf_rows - del_lf_rows) gap_num_rows
from user_index_stats where height >= 3 and (lf_rows > 0 and del_lf_rows/lf_rows >= 0.2)
order by height desc, del_lf_rows/lf_rows desc) loop
begin
v_sql := 'alter index '||i.index_name||' rebuild online parallel 4';
execute immediate v_sql;
v_sql := 'alter index '||i.index_name||' noparallel';
execute immediate v_sql;
--dbms_output.put_line(i.index_name);
update user_index_stats set rebuild_date = sysdate where index_name = i.index_name;
commit;
exception when others then
v_Errmsg :=substr(dbms_utility.format_error_backtrace,1,150);
v_Errmsg :=v_Errmsg||substr(dbms_utility.format_error_stack,1,150);
update user_index_stats set comments = v_Errmsg where index_name = i.index_name; end;
commit;
end loop;
end;
最新发布
本文介绍了一个PL/SQL命令，用于收集数据库中特定表的统计信息，这对于优化查询性能至关重要。
扫一扫
748
到【灌水乐园】发言
