導(dǎo)讀:1建站知識(shí)這篇文章主要介紹了Oracle重建索引Shell腳本、SQL腳本分享,索引是提高數(shù)據(jù)庫查詢性能的有力武器,定期重建索引是很有必要的事情,需要的朋友可以參考下 索網(wǎng)站建設(shè)教程網(wǎng)站seo優(yōu)化。

這篇文章主要介紹了Oracle重建索引Shell腳本、SQL腳本分享,索引是提高數(shù)據(jù)庫查詢性能的有力武器,定期重建索引是很有必要的事情,需要的朋友可以參考下 索引是提高數(shù)據(jù)庫查詢性能的有力武器。沒有索引，就好比圖書館沒有圖書標(biāo)簽一樣，找一本書自己想要的書比登天還難。然而索引在使用的過程中，尤其是在批量的DML的情形下會(huì)產(chǎn)生相應(yīng)的碎片，以及B樹高度會(huì)發(fā)生相應(yīng)變化，因此可以對(duì)這些變化較大的索引進(jìn)行重構(gòu)以提高性能。N久以前Oracle建議我們定期重建那些高度為4，已刪除的索引條目至少占有現(xiàn)有索引條目總數(shù)的20%的這些表上的索引。但Oracle現(xiàn)在強(qiáng)烈建議不要定期重建索引。具體可以參考文章：Oracle 重建索引的必要性。盡管如此重建索引還是有必要的，只是不建議定期。本文給出了重建索引的腳本供大家參考。 1、重建索引shell腳本 robin@SZDB:~/dba_scripts/custom/bin> more rebuild_unbalanced_indices.sh # +---------------------------------------------網(wǎng)站建設(shè)----------+ # +  Rebulid unblanced indices             | # +  Author : Leshami                  | # +  Parameter : No                   | # +-------------------------------------------------------+ #!/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/網(wǎng)站seo優(yōu)化課程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、重建索引調(diào)用的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_百度seo網(wǎng)站優(yōu)化INPUT_DATE...   ................ 4、后記 a、如果同一臺(tái)服務(wù)器上有多個(gè)實(shí)例，且每個(gè)實(shí)例有相同的schema，此腳本會(huì)輪巡所有實(shí)例并根據(jù)analyze結(jié)果來rebuild。 a、大家應(yīng)根據(jù)需要作相應(yīng)調(diào)整，如腳本的路徑信息等。 b、需要修改相應(yīng)的schema name。 d、可根據(jù)系統(tǒng)環(huán)境調(diào)整相應(yīng)的并行度。相關(guān)網(wǎng)站建設(shè)教程網(wǎng)站seo優(yōu)化。

關(guān)鍵詞標(biāo)簽: 索引 SQL 腳本