索引是提高数据库查询性能的有力武器。没有索引，就好比图书馆没有图书标签一样，找一本书自己想要的书比登天还难。然而索引在使用的过程中，尤其是在批量的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 |
# +-------------------------------------------------------+
#!/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、可根据系统环境调整相应的并行度。