索引是提高数据库查询性能的有力武器。没有索引，就好比图书馆没有图书标签一样，找一本书自己想要的书比登天还难。然而索引在使用的过程中，尤其是在批量的DML的情形下会产生相应的碎片，以及B树高度会发生相应变化，因此可以对这些变化较大的索引进行重构以提高性能。N久以前Oracle建议我们定期重建那些高度为4，已删除的索引条目至少占有现有索引条目总数的20%的这些表上的索引。但Oracle现在强烈建议不要定期重建索引。具体可以参考文章： Oracle 重建索引的必要性 。尽管如此重建索引还是有必要的，只是不建议定期。本文给出了重建索引的脚本供大家参考。
1、重建索引shell脚本
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
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/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脚本
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 |
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、输入日志样本
|
1 2 3 4 5 6 7 8 |
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、可根据系统环境调整相应的并行度。
查看更多关于Oracle重建索引Shell脚本、SQL脚本分享的详细内容...