Oracle 重建索引脚本

索引是提高数据库查询性能的有力武器。没有索引，就好比图书馆没有图书标签一样，找一本书自己想要的书比登天还难。然而索引在使用的过程中，尤其是在批量的DML的情形下会产生相应的碎片，以及B树高度会发生相应变化，因此可以对这些变化较大的索引进行重构以提高性能。N久以前Oracle建议我们定期重建那些高度为4，已删除的索引条目至少占有现有索引条目总数的20%的这些表上的索引。但Oracle现在强烈建议不要定期重建索引。具体可以参考文章：Oracle 重建索引的必要性。尽管如此重建索引还是有必要的，只是不建议定期。本文给出了重建索引的脚本供大家参考。
 
1、重建索引shell脚本

[html] view plain copy

 print?

1. robin@SZDB:~/dba_scripts/custom/bin> more rebuild_unbalanced_indices.sh   
2. # +-------------------------------------------------------+  
3. # +    Rebulid unblanced indices                          |  
4. # +    Author : Leshami                                   |   
5. # +    Parameter : No                                     |  
6. # +    Blog : http://blog.csdn.net/leshami                |   
7. # +-------------------------------------------------------+  
8.   
9. #!/bin/bash   
10. # --------------------  
11. # Define variable  
12. # --------------------  
13.   
14. if [ -f ~/.bash_profile ]; then  
15. . ~/.bash_profile  
16. fi  
17.   
18. DT=`date +%Y%m%d`;             export DT  
19. RETENTION=1  
20. LOG_DIR=/tmp  
21. LOG=${LOG_DIR}/rebuild_unbalanced_indices_${DT}.log  
22. DBA=[e-mail]  
23.   
24. # ------------------------------------  
25. # Loop all instance in current server  
26. # -------------------------------------  
27. echo "Current date and time is : `/bin/date`">>${LOG}  
28.   
29. for db in `ps -ef | grep pmon | grep -v grep |grep -v asm |awk '{print $8}'|cut -c 10-`  
30. do  
31.     echo "$db"  
32.     export ORACLE_SID=$db  
33.     echo "Current DB is $db" >>${LOG}  
34.     echo "===============================================">>${LOG}  
35.     $ORACLE_HOME/bin/sqlplus -S /nolog @/users/robin/dba_scripts/custom/sql/rebuild_unbalanced_indices.sql>>${LOG}  
36. done;  
37.   
38. echo "End of rebuilding index for all instance at : `/bin/date`">>${LOG}  
39. # -------------------------------------  
40. # Check log file   
41. # -------------------------------------  
42. status=`grep "ORA-" ${LOG}`  
43. if [ -z $status ];then  
44.     mail -s "Succeeded rebuilding indices on `hostname`  !!!" ${DBA} <${LOG}  
45. else  
46.     mail -s "Failed rebuilding indices on `hostname`  !!!" ${DBA} <${LOG}  
47. fi  
48.   
49. # ------------------------------------------------  
50. # Removing files older than $RETENTION parameter   
51. # ------------------------------------------------  
52.   
53. find ${LOG_DIR} -name "rebuild_unb*" -mtime +$RETENTION -exec rm {} \;  
54.   
55. exit  

2、重建索引调用的SQL脚本

[sql] view plain copy

 print?

1. robin@SZDB:~/dba_scripts/custom/sql> more rebuild_unbalanced_indices.sql   
2. conn / as sysdba  
3. set serveroutput on;  
4. DECLARE  
5.    resource_busy               EXCEPTION;  
6.    PRAGMA EXCEPTION_INIT (resource_busy, -54);  
7.    c_max_trial        CONSTANT PLS_INTEGER := 10;  
8.    c_trial_interval   CONSTANT PLS_INTEGER := 1;  
9.    pmaxheight         CONSTANT INTEGER := 3;  
10.    pmaxleafsdeleted   CONSTANT INTEGER := 20;  
11.   
12.    CURSOR csrindexstats  
13.    IS  
14.       SELECT NAME,  
15.              height,  
16.              lf_rows AS leafrows,  
17.              del_lf_rows AS leafrowsdeleted  
18.         FROM index_stats;  
19.   
20.    vindexstats                 csrindexstats%ROWTYPE;  
21.   
22.    CURSOR csrglobalindexes  
23.    IS  
24.       SELECT owner,index_name, tablespace_name  
25.         FROM dba_indexes  
26.        WHERE partitioned = 'NO'  
27.         AND owner IN ('GX_ADMIN');  
28.   
29.    CURSOR csrlocalindexes  
30.    IS  
31.       SELECT index_owner,index_name, partition_name, tablespace_name  
32.         FROM dba_ind_partitions  
33.        WHERE status = 'USABLE'  
34.         AND index_owner IN ('GX_ADMIN');  
35.   
36.    trial                       PLS_INTEGER;  
37.    vcount                      INTEGER := 0;  
38. BEGIN  
39.    trial := 0;  
40.   
41.    /* Global indexes */  
42.    FOR vindexrec IN csrglobalindexes  
43.    LOOP  
44.       EXECUTE IMMEDIATE  
45.          'analyze index ' || vindexrec.owner ||'.'|| vindexrec.index_name || ' validate structure';  
46.   
47.       OPEN csrindexstats;  
48.   
49.       FETCH csrindexstats INTO vindexstats;  
50.   
51.       IF csrindexstats%FOUND  
52.       THEN  
53.          IF    (vindexstats.height > pmaxheight)  
54.             OR (    vindexstats.leafrows > 0  
55.                 AND vindexstats.leafrowsdeleted > 0  
56.                 AND (vindexstats.leafrowsdeleted * 100 / vindexstats.leafrows) >  
57.                        pmaxleafsdeleted)  
58.          THEN  
59.             vcount := vcount + 1;  
60.             DBMS_OUTPUT.PUT_LINE (  
61.                'Rebuilding index ' || vindexrec.owner ||'.'|| vindexrec.index_name || '...');  
62.   
63.            <<alter_index>>  
64.             BEGIN  
65.                EXECUTE IMMEDIATE  
66.                      'alter index '  
67.                   || vindexrec.owner ||'.'  
68.                   || vindexrec.index_name  
69.                   || ' rebuild'  
70.                   || ' parallel nologging compute statistics'  
71.                   || ' tablespace '  
72.                   || vindexrec.tablespace_name;  
73.             EXCEPTION  
74.                WHEN resource_busy OR TIMEOUT_ON_RESOURCE  
75.                THEN  
76.                   DBMS_OUTPUT.PUT_LINE (  
77.                      'alter index - busy and wait for 1 sec');  
78.                   DBMS_LOCK.sleep (c_trial_interval);  
79.   
80.                   IF trial <= c_max_trial  
81.                   THEN  
82.                      GOTO alter_index;  
83.                   ELSE  
84.                      DBMS_OUTPUT.PUT_LINE (  
85.                            'alter index busy and waited - quit after '  
86.                         || TO_CHAR (c_max_trial)  
87.                         || ' trials');  
88.                      RAISE;  
89.                   END IF;  
90.                WHEN OTHERS  
91.                THEN  
92.                   DBMS_OUTPUT.PUT_LINE ('alter index err ' || SQLERRM);  
93.                   RAISE;  
94.             END;  
95.          END IF;  
96.       END IF;  
97.   
98.       CLOSE csrindexstats;  
99.    END LOOP;  
100.   
101.    DBMS_OUTPUT.PUT_LINE ('Global indices rebuilt: ' || TO_CHAR (vcount));  
102.    vcount := 0;  
103.    trial := 0;  
104.   
105.    /* Local indexes */  
106.    FOR vindexrec IN csrlocalindexes  
107.    LOOP  
108.       EXECUTE IMMEDIATE  
109.             'analyze index '  
110.          || vindexrec.index_owner||'.'  
111.          || vindexrec.index_name  
112.          || ' partition ('  
113.          || vindexrec.partition_name  
114.          || ') validate structure';  
115.   
116.       OPEN csrindexstats;  
117.   
118.       FETCH csrindexstats INTO vindexstats;  
119.   
120.       IF csrindexstats%FOUND  
121.       THEN  
122.          IF    (vindexstats.height > pmaxheight)  
123.             OR (    vindexstats.leafrows > 0  
124.                 AND vindexstats.leafrowsdeleted > 0  
125.                 AND (vindexstats.leafrowsdeleted * 100 / vindexstats.leafrows) >  
126.                        pmaxleafsdeleted)  
127.          THEN  
128.             vcount := vcount + 1;  
129.             DBMS_OUTPUT.PUT_LINE (  
130.                'Rebuilding index ' || vindexrec.index_owner||'.'|| vindexrec.index_name || '...');  
131.   
132.            <<alter_partitioned_index>>  
133.             BEGIN  
134.                EXECUTE IMMEDIATE  
135.                      'alter index '  
136.                   || vindexrec.index_owner||'.'  
137.                   || vindexrec.index_name  
138.                   || ' rebuild'  
139.                   || ' partition '  
140.                   || vindexrec.partition_name  
141.                   || ' parallel nologging compute statistics'  
142.                   || ' tablespace '  
143.                   || vindexrec.tablespace_name;  
144.             EXCEPTION  
145.                WHEN resource_busy OR TIMEOUT_ON_RESOURCE  
146.                THEN  
147.                   DBMS_OUTPUT.PUT_LINE (  
148.                      'alter partitioned index - busy and wait for 1 sec');  
149.                   DBMS_LOCK.sleep (c_trial_interval);  
150.   
151.                   IF trial <= c_max_trial  
152.                   THEN  
153.                      GOTO alter_partitioned_index;  
154.                   ELSE  
155.                      DBMS_OUTPUT.PUT_LINE (  
156.                            'alter partitioned index busy and waited - quit after '  
157.                         || TO_CHAR (c_max_trial)  
158.                         || ' trials');  
159.                      RAISE;  
160.                   END IF;  
161.                WHEN OTHERS  
162.                THEN  
163.                   DBMS_OUTPUT.PUT_LINE (  
164.                      'alter partitioned index err ' || SQLERRM);  
165.                   RAISE;  
166.             END;  
167.          END IF;  
168.       END IF;  
169.   
170.       CLOSE csrindexstats;  
171.    END LOOP;  
172.   
173.    DBMS_OUTPUT.PUT_LINE ('Local indices rebuilt: ' || TO_CHAR (vcount));  
174. END;  
175. /  
176. 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、可根据系统环境调整相应的并行度。

 

5、相关参考
    Oracle 聚簇因子(Clustering factor)  
    Oracle 索引监控(monitor index) 
    Oracle 索引监控与外键索引  
    收集统计信息导致索引被监控  
    Oracle 监控索引的使用率 
    NULL 值与索引(一) 
    NULL 值与索引(二) 
    函数使得索引列失效

    Oracle 索引质量分析

    Oracle 重建索引的必要性