Oracle 重建索引脚本

32rewf

  索引是提高数据库查询性能的有力武器。没有索引，就好比图书馆没有图书标签一样，找一本书自己想要的书比登天还难。然而索引在使用的过程中，尤其是在批量的DML的情形下会产生相应的碎片，以及B树高度会发生相应变化，因此可以对这些变化较大的索引进行重构以提高性能。N久以前Oracle建议我们定期重建那些高度为4，已删除的索引条目至少占有现有索引条目总数的20%的这些表上的索引。但Oracle现在强烈建议不要定期重建索引。具体可以参考文章：Oracle 重建索引的必要性。尽管如此重建索引还是有必要的，只是不建议定期。本文给出了重建索引的脚本供大家参考。

1、重建索引shell脚本
robin@SZDB:~/dba_scripts/custom/bin> more rebuild_unbalanced_indices.sh   
# +-------------------------------------------------------+  
# +    Rebulid unblanced indices                          |  
# +    Author : Leshami                                   |   
# +    Parameter : No                                     |  
# +    Blog : http://blog.iyunv.com/leshami                |   
# +-------------------------------------------------------+  

#!/bin/bash   
# --------------------  
# Define variable  
# --------------------  

if [ -f ~/.bash_profile ]; then  
. ~/.bash_profile  
fi  

DT=`date +%Y%m%d`;             export DT  
RETENTION=1  
LOG_DIR=/tmp  
LOG=${LOG_DIR}/rebuild_unbalanced_indices_${DT}.log  
DBA=Leshami@12306.cn  

# ------------------------------------  
# Loop all instance in current server  
# -------------------------------------  
echo "Current date and time is : `/bin/date`">>${LOG}  

for db in `ps -ef | grep pmon | grep -v grep |grep -v asm |awk '{print $8}'|cut -c 10-`  
do  
    echo "$db"  
    export ORACLE_SID=$db  
    echo "Current DB is $db" >>${LOG}  
    echo "===============================================">>${LOG}  
    $ORACLE_HOME/bin/sqlplus -S /nolog @/users/robin/dba_scripts/custom/sql/rebuild_unbalanced_indices.sql>>${LOG}  
done;  

echo "End of rebuilding index for all instance at : `/bin/date`">>${LOG}  
# -------------------------------------  
# Check log file   
# -------------------------------------  
status=`grep "ORA-" ${LOG}`  
if [ -z $status ];then  
    mail -s "Succeeded rebuilding indices on `hostname`  !!!" ${DBA} <${LOG}  
else  
    mail -s "Failed rebuilding indices on `hostname`  !!!" ${DBA} <${LOG}  
fi  

# ------------------------------------------------  
# Removing files older than $RETENTION parameter   
# ------------------------------------------------  

find ${LOG_DIR} -name "rebuild_unb*" -mtime +$RETENTION -exec rm {} \;  

exit  

2、重建索引调用的SQL脚本
robin@SZDB:~/dba_scripts/custom/sql> more rebuild_unbalanced_indices.sql   
conn / as sysdba  
set serveroutput on;  
DECLARE  
   resource_busy               EXCEPTION;  
   PRAGMA EXCEPTION_INIT (resource_busy, -54);  
   c_max_trial        CONSTANT PLS_INTEGER := 10;  
   c_trial_interval   CONSTANT PLS_INTEGER := 1;  
   pmaxheight         CONSTANT INTEGER := 3;  
   pmaxleafsdeleted   CONSTANT INTEGER := 20;  

   CURSOR csrindexstats  
   IS  
      SELECT NAME,  
             height,  
             lf_rows AS leafrows,  
             del_lf_rows AS leafrowsdeleted  
        FROM index_stats;  

   vindexstats                 csrindexstats%ROWTYPE;  

   CURSOR csrglobalindexes  
   IS  
      SELECT owner,index_name, tablespace_name  
        FROM dba_indexes  
       WHERE partitioned = 'NO'  
        AND owner IN ('GX_ADMIN');  

   CURSOR csrlocalindexes  
   IS  
      SELECT index_owner,index_name, partition_name, tablespace_name  
        FROM dba_ind_partitions  
       WHERE status = 'USABLE'  
        AND index_owner IN ('GX_ADMIN');  

   trial                       PLS_INTEGER;  
   vcount                      INTEGER := 0;  
BEGIN  
   trial := 0;  

   /* Global indexes */  
   FOR vindexrec IN csrglobalindexes  
   LOOP  
      EXECUTE IMMEDIATE  
         'analyze index ' || vindexrec.owner ||'.'|| vindexrec.index_name || ' validate structure';  

      OPEN csrindexstats;  

      FETCH csrindexstats INTO vindexstats;  

      IF csrindexstats%FOUND  
      THEN  
         IF    (vindexstats.height > pmaxheight)  
            OR (    vindexstats.leafrows > 0  
                AND vindexstats.leafrowsdeleted > 0  
                AND (vindexstats.leafrowsdeleted * 100 / vindexstats.leafrows) >  
                       pmaxleafsdeleted)  
         THEN  
            vcount := vcount + 1;  
            DBMS_OUTPUT.PUT_LINE (  
               'Rebuilding index ' || vindexrec.owner ||'.'|| vindexrec.index_name || '...');  

           <<alter_index>>  
            BEGIN  
               EXECUTE IMMEDIATE  
                     'alter index '  
                  || vindexrec.owner ||'.'  
                  || vindexrec.index_name  
                  || ' rebuild'  
                  || ' parallel nologging compute statistics'  
                  || ' tablespace '  
                  || vindexrec.tablespace_name;  
            EXCEPTION  
               WHEN resource_busy OR TIMEOUT_ON_RESOURCE  
               THEN  
                  DBMS_OUTPUT.PUT_LINE (  
                     'alter index - busy and wait for 1 sec');  
                  DBMS_LOCK.sleep (c_trial_interval);  

                  IF trial <= c_max_trial  
                  THEN  
                     GOTO alter_index;  
                  ELSE  
                     DBMS_OUTPUT.PUT_LINE (  
                           'alter index busy and waited - quit after '  
                        || TO_CHAR (c_max_trial)  
                        || ' trials');  
                     RAISE;  
                  END IF;  
               WHEN OTHERS  
               THEN  
                  DBMS_OUTPUT.PUT_LINE ('alter index err ' || SQLERRM);  
                  RAISE;  
            END;  
         END IF;  
      END IF;  

      CLOSE csrindexstats;  
   END LOOP;  

   DBMS_OUTPUT.PUT_LINE ('Global indices rebuilt: ' || TO_CHAR (vcount));  
   vcount := 0;  
   trial := 0;  

   /* Local indexes */  
   FOR vindexrec IN csrlocalindexes  
   LOOP  
      EXECUTE IMMEDIATE  
            'analyze index '  
         || vindexrec.index_owner||'.'  
         || vindexrec.index_name  
         || ' partition ('  
         || vindexrec.partition_name  
         || ') validate structure';  

      OPEN csrindexstats;  

      FETCH csrindexstats INTO vindexstats;  

      IF csrindexstats%FOUND  
      THEN  
         IF    (vindexstats.height > pmaxheight)  
            OR (    vindexstats.leafrows > 0  
                AND vindexstats.leafrowsdeleted > 0  
                AND (vindexstats.leafrowsdeleted * 100 / vindexstats.leafrows) >  
                       pmaxleafsdeleted)  
         THEN  
            vcount := vcount + 1;  
            DBMS_OUTPUT.PUT_LINE (  
               'Rebuilding index ' || vindexrec.index_owner||'.'|| vindexrec.index_name || '...');  

           <<alter_partitioned_index>>  
            BEGIN  
               EXECUTE IMMEDIATE  
                     'alter index '  
                  || vindexrec.index_owner||'.'  
                  || vindexrec.index_name  
                  || ' rebuild'  
                  || ' partition '  
                  || vindexrec.partition_name  
                  || ' parallel nologging compute statistics'  
                  || ' tablespace '  
                  || vindexrec.tablespace_name;  
            EXCEPTION  
               WHEN resource_busy OR TIMEOUT_ON_RESOURCE  
               THEN  
                  DBMS_OUTPUT.PUT_LINE (  
                     'alter partitioned index - busy and wait for 1 sec');  
                  DBMS_LOCK.sleep (c_trial_interval);  

                  IF trial <= c_max_trial  
                  THEN  
                     GOTO alter_partitioned_index;  
                  ELSE  
                     DBMS_OUTPUT.PUT_LINE (  
                           'alter partitioned index busy and waited - quit after '  
                        || TO_CHAR (c_max_trial)  
                        || ' trials');  
                     RAISE;  
                  END IF;  
               WHEN OTHERS  
               THEN  
                  DBMS_OUTPUT.PUT_LINE (  
                     'alter partitioned index err ' || SQLERRM);  
                  RAISE;  
            END;  
         END IF;  
      END IF;  

      CLOSE csrindexstats;  
   END LOOP;  

   DBMS_OUTPUT.PUT_LINE ('Local indices rebuilt: ' || TO_CHAR (vcount));  
END;  
/  
exit;  

3、输入日志样本
Current date and time is : Sun Apr 20 02:00:02 HKT 2014
Current DB is SYBO2 ===============================================
Rebuilding index GX_ADMIN.SYN_OUT_DATA_TBL_PK...
Rebuilding index GX_ADMIN.IDX_TDBK_SPLNK_PARENT_REF...
Rebuilding index GX_ADMIN.IDX_TDBK_SPLNK_CHILD_REF...
Rebuilding index GX_ADMIN.PK_TRADE_BROKER_TBL...
Rebuilding index GX_ADMIN.IDX_TDBK_INPUT_DATE...
    ................

4、后记
a、如果同一台服务器上有多个实例，且每个实例有相同的schema，此脚本会轮巡所有实例并根据analyze结果来rebuild。
a、大家应根据需要作相应调整，如脚本的路径信息等。
b、需要修改相应的schema name。
d、可根据系统环境调整相应的并行度。