频繁对索引字段进行delete、update操作,会对索引造成大量碎片,从而极大地影响索引的使用效率,并造成索引IO的增加。
索引碎片分析:
analyze index index_name validate structure online;
select name,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100 as "索引碎片率" from index_stats;
如果索引碎片率超过20%,则说明索引碎片已经非常严重。
可以编写一个检测所有索引碎片率的脚本,定期运行该脚本,保持对索引碎片率的监控。
索引碎片整理:
重建 alter index index_name rebuild;
alter index index_name rebuild online;
定期重建索引脚本:
CREATE OR REPLACE TYPE strsplit_type AS TABLE OF VARCHAR2(32676);
CREATE OR REPLACE FUNCTION strsplit(p_value VARCHAR2, p_split VARCHAR2 := ',')
--usage: select * from table(strsplit('1,2,3,4,5'))
RETURN strsplit_type
PIPELINED IS
v_idx INTEGER;
v_str VARCHAR2(500);
v_strs_last VARCHAR2(4000) := p_value;
BEGIN
LOOP
v_idx := instr(v_strs_last, p_split);
EXIT WHEN v_idx = 0;
v_str := substr(v_strs_last, 1, v_idx - 1);
v_strs_last := substr(v_strs_last, v_idx + 1);
PIPE ROW(v_str);
END LOOP;
PIPE ROW(v_strs_last);
RETURN;
END strsplit;
CREATE OR REPLACE PROCEDURE UP_CHECK_TO_REBUILD_INDEX
(
tbNames varchar
)
IS
sqlstr VARCHAR2(100);
idx_ratio INT;
BEGIN
--DECLARE sqlstr VARCHAR2(100);
-- idx_ratio INT;
BEGIN
FOR idx IN (SELECT t.index_name FROM user_indexes t
WHERE t.index_type = 'NORMAL' AND t.status = 'VALID' AND t.temporary = 'N' AND t.leaf_blocks > 100
AND t.table_name IN (SELECT UPPER(TRIM(COLUMN_VALUE)) from table(strsplit(tbNames)))
ORDER BY t.table_name, t.index_name
)
LOOP
DBMS_OUTPUT.put_LINE(idx.index_name || ' ANALYZE START ' || TO_CHAR(SYSDATE, 'yyyy-MM-dd hh24:mi:ss'));
sqlstr := 'ANALYZE INDEX ' || idx.Index_Name || ' VALIDATE STRUCTURE';
EXECUTE IMMEDIATE sqlstr;
SELECT TRUNC((t.del_lf_rows/t.lf_rows) * 100) INTO idx_ratio
FROM index_stats t WHERE t.name=idx.index_name AND ROWNUM=1;
IF (idx_ratio >= 20) THEN
DBMS_OUTPUT.put_line(' REINDEX ' || TO_CHAR(SYSDATE, 'yyyy-MM-dd hh24:mi:ss') || ' ratio: ' || idx_ratio);
sqlstr := 'ALTER INDEX ' || idx.index_name || ' REBUILD';
EXECUTE IMMEDIATE sqlstr;
END IF;
END LOOP;
END;
END UP_CHECK_TO_REBUILD_INDEX;
SQL>exec UP_CHECK_TO_REBUILD_INDEX('TKK29, muser');
begin
UP_CHECK_TO_REBUILD_INDEX('TKK29, muser');
end;
http://blog.itpub.net/41451/viewspace-1058441/