Oracle 重建索引指令碼

標籤：

      該指數是一個有力的武器，以提高資料庫的查詢效能。

沒有索引，喜歡同樣的標籤庫沒有書籍，找書，他們想預訂比登天還難。中，尤其是在批量的DML的情形下會產生對應的片段。以及B樹高度會發生對應變化。因此能夠對這些變化較大的索引進行重構以提高效能。N久曾經Oracle建議我們定期重建那些高度為4。已刪除的索引條目至少佔有現有索引條目總數的20%的這些表上的索引。但Oracle如今強烈建議不要定期重建索引。

詳細能夠參考文章：Oracle 重建索引的必要性。

雖然如此重建索引還是有必要的。僅僅是不建議定期。本文給出了重建索引的指令碼供大家參考。

 
1、重建索引shell指令碼

[email protected]:~/dba_scripts/custom/bin> more rebuild_unbalanced_indices.sh # +-------------------------------------------------------+# +    Rebulid unblanced indices                          |# +    Author : Leshami                                   | # +    Parameter : No                                     |# +    Blog : http://blog.csdn.net/leshami                | # +-------------------------------------------------------+#!/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}.log[email protected]# ------------------------------------# 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指令碼

[email protected]:~/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、假設同一台server上有多個執行個體，且每一個執行個體有同樣的schema。此指令碼會輪巡全部執行個體並依據analyze結果來rebuild。 
a、大家應依據須要作對應調整。如指令碼的路徑資訊等。

b、須要改動對應的schema name。

d、可依據系統內容調整對應的並行度。

 

5、相關參考
    Oracle 聚簇因子(Clustering factor) 
    Oracle 索引監控(monitor index)
    Oracle 索引監控與外鍵索引 
    收集統計資訊導致索引被監控 
    Oracle 監控索引的使用率
    NULL 值與索引(一)
    NULL 值與索引(二)
    函數使得索引列失效

    Oracle 索引品質分析

    Oracle 重建索引的必要性

        

著作權聲明：本文博主原創文章，部落格，未經同意不得轉載。

Oracle 重建索引指令碼