判断oracle index 何时需要重建

oracle 索引何时需要重建

1.analyze index PUB_USER_FUNC_RESOURCE_INDEX3 validate structure;

2. 查询索引碎片：
select name,
del_lf_rows,
lf_rows,
round((del_lf_rows/(lf_rows+0.0000000001))*100) frag_pct
from index_stats
一般如果该索引的frag_pct>20%，那么就需要rebuild index

 

3.alter index PUB_USER_FUNC_RESOURCE_INDEX3 rebuild online;

 

 

--根据上面原理创建存储过程

CREATE OR REPLACE PROCEDURE rebuild_index IS
--收集索引的信息，决定是否需要重建
--analyze index PUB_USER_FUNC_RESOURCE_INDEX3 validate structure;
--select height,blocks,del_lf_rows,lf_rows, round(del_lf_rows/lf_rows*100) from index_stats;
--alter index PUB_USER_FUNC_RESOURCE_INDEX3 rebuild online;
CURSOR my_cursor IS SELECT OWNER,INDEX_NAME FROM dba_indexes WHERE owner  IN ('NEW_JK');
v_indexname VARCHAR2(30);
v_owner VARCHAR2(30);
v_rate NUMBER(10);
v_name VARCHAR2(30);
v_sql  VARCHAR2(100);
BEGIN
       FOR i IN my_cursor LOOP
       v_indexname:=i.index_name;
       v_owner:=i.owner;
      -- dbms_output.put_line('index_name is '||v_indexname);
       v_sql:= 'ANALYZE INDEX '||v_owner ||'.'|| v_indexname ||' VALIDATE STRUCTURE';


       dbms_output.put_line('analyze is '||v_sql);
       EXECUTE IMMEDIATE v_sql;
       BEGIN
       SELECT NAME,round((del_lf_rows/(lf_rows+0.0000000001))*100) INTO v_name, v_rate FROM index_stats;
            EXCEPTION
               when NO_DATA_FOUND then
               dbms_output.put_line('no data found');
             goto end_loop;
       END;
              IF (v_rate > 20 ) THEN
                dbms_output.put_line('index_name is '||v_owner||'.'||v_name || ' rate is '|| v_rate);
                dbms_output.put_line('************begin rebuild online*****************');
                dbms_output.put_line('ALTER INDEX '||v_owner||'.'||v_name ||' REBUILD ONLINE');
                EXECUTE IMMEDIATE('ALTER INDEX '||v_owner||'.'||v_name ||' REBUILD ONLINE');
                dbms_output.put_line('**************end rebuild online***************');
                --EXIT;
              END IF;
        <<end_loop>>
        null; 
       END LOOP;
END;

 

--执行存储过程

[oracle@server8 user_stat]$ cat index_rebuild.sh
#!/bin/bash
ORACLE_SID=ghsjdb
ORACLE_BASE=/home/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID ORACLE_BASE ORACLE_HOME TARGET_SID
PATH=$PATH:$ORACLE_HOME/bin:/usr/local/bin
export PATH
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_LANG

sqlplus / as sysdba <<EOF

exec ghsj_xm.user_stat;

exit
EOF

 

--设置定时服务

0 2 * * *   /home/oracle/user_stat/index_rebuild.sh > /home/oracle/user_stat/index_rebuild.log

 

本文乃原创文章，请勿转载。如须转载请详细标明转载出处