索引是提高数据库查询性能的有力武器。没有索引，就好比图书馆没有图书标签一样，找一本书自己想要的书比登天还难。然而索引在使用的过程中，尤其是在批量的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_profilefiDT=`date +%Y%m%d`; export DTRETENTION=1LOG_DIR=/tmpLOG=${LOG_DIR}/rebuild_unbalanced_indices_${DT}.logDBA=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 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、输入日志样本
|
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、可根据系统环境调整相应的并行度。
