索引是提高数据库查询性能的有力武器。没有索引,就好比图书馆没有图书标签一样,找一本书自己想要的书比登天还难。然而索引在使用的过程中,尤其是在批量的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 |
- # + Blog : http://blog.csdn.net/leshami |
- # +-------------------------------------------------------+
- #!/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脚本
- 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_INPUT_DATE...
................
4、后记
a、如果同一台服务器上有多个实例,且每个实例有相同的schema,此脚本会轮巡所有实例并根据analyze结果来rebuild。
a、大家应根据需要作相应调整,如脚本的路径信息等。
b、需要修改相应的schema name。
d、可根据系统环境调整相应的并行度。
5、相关参考
Oracle 聚簇因子(Clustering factor)
Oracle 索引监控(monitor index)
Oracle 索引监控与外键索引
收集统计信息导致索引被监控
Oracle 监控索引的使用率
NULL 值与索引(一)
NULL 值与索引(二)
函数使得索引列失效