Oracle索引碎片检查及定期重建常用表的索引

最新推荐文章于 2024-05-29 10:18:43 发布

好记忆不如烂笔头abc

最新推荐文章于 2024-05-29 10:18:43 发布

阅读量1.2k

点赞数

分类专栏： oracle 文章标签：索引碎片数据库优化索引重建性能监控 SQL脚本

原文链接：https://www.cnblogs.com/zhaoguan_wang/p/5169821.html

版权

oracle 专栏收录该内容

433 篇文章 7 订阅

订阅专栏

频繁对索引字段进行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/

https://www.cnblogs.com/gdpuzxs/p/6928147.html

https://www.cnblogs.com/zhaoguan_wang/p/5169821.html