Oracle重建索引Shell脚本、SQL脚本分享
时间:2015-02-22 23:15 来源:linux.it.net.cn 作者:IT
索引是提高数据库查询性能的有力武器。没有索引，就好比图书馆没有图书标签一样，找一本书自己想要的书比登天还难。然而索引在使用的过程中，尤其是在批量的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、可根据系统环境调整相应的并行度。
(责任编辑：IT)
索引是提高数据库查询性能的有力武器。没有索引，就好比图书馆没有图书标签一样，找一本书自己想要的书比登天还难。然而索引在使用的过程中，尤其是在批量的DML的情形下会产生相应的碎片，以及B树高度会发生相应变化，因此可以对这些变化较大的索引进行重构以提高性能。N久以前Oracle建议我们定期重建那些高度为4，已删除的索引条目至少占有现有索引条目总数的20%的这些表上的索引。但Oracle现在强烈建议不要定期重建索引。具体可以参考文章：Oracle 重建索引的必要性。尽管如此重建索引还是有必要的，只是不建议定期。本文给出了重建索引的脚本供大家参考。 1、重建索引shell脚本
2、重建索引调用的SQL脚本
3、输入日志样本
4、后记
a、如果同一台服务器上有多个实例，且每个实例有相同的schema，此脚本会轮巡所有实例并根据analyze结果来rebuild。 |