Oracle重建索引Shell脚本、SQL脚本分享

数据库  /  管理员 发布于 6年前   165

索引是提高数据库查询性能的有力武器。没有索引，就好比图书馆没有图书标签一样，找一本书自己想要的书比登天还难。然而索引在使用的过程中，尤其是在批量的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}[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脚本

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');  trialPLS_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、可根据系统环境调整相应的并行度。