导读:1建站知识这篇文章主要介绍了Oracle重建索引Shell脚本、SQL脚本分享,索引是提高数据库查询性能的有力武器,定期重建索引是很有必要的事情,需要的朋友可以参考下 索网站建设教程网站seo优化。

这篇文章主要介绍了Oracle重建索引Shell脚本、SQL脚本分享,索引是提高数据库查询性能的有力武器,定期重建索引是很有必要的事情,需要的朋友可以参考下 索引是提高数据库查询性能的有力武器。没有索引，就好比图书馆没有图书标签一样，找一本书自己想要的书比登天还难。然而索引在使用的过程中，尤其是在批量的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_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/网站seo优化课程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_百度seo网站优化INPUT_DATE...   ................ 4、后记 a、如果同一台服务器上有多个实例，且每个实例有相同的schema，此脚本会轮巡所有实例并根据analyze结果来rebuild。 a、大家应根据需要作相应调整，如脚本的路径信息等。 b、需要修改相应的schema name。 d、可根据系统环境调整相应的并行度。相关网站建设教程网站seo优化。

关键词标签: 索引 SQL 脚本