Oracle重建索引Shell指令碼、SQL指令碼分享_oracle

索引是提高資料庫查詢效能的有力武器。沒有索引，就好比圖書館沒有圖書標籤一樣，找一本書自己想要的書比登天還難。然而索引在使用的過程中，尤其是在批量的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                   |# +-------------------------------------------------------+#!/bin/bash # --------------------# Define variable# --------------------if [ -f ~/.bash_profile ]; then. ~/.bash_profilefiDT=`date +%Y%m%d`;       export DTRETENTION=1LOG_DIR=/tmpLOG=${LOG_DIR}/rebuild_unbalanced_indices_${DT}.logDBA=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 sysdbaset 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、可根據系統內容調整相應的並行度。