Oracle重建索引Shell脚本、SQL脚本分享
索引是提高数据库查询性能的有力武器。没有索引，就好比图书馆没有图书标签一样，找一本书自己想要的书比登天还难。然而索引在使用的过程中，尤其是在批量的DML的情形下会产生相应的碎片，以及B树高度会发生相应变化，因此可以对这些变化较大的索引进行重构以提高性能。N久以前Oracle建议我们定期重建那些高度为4，已删除的索引条目至少占有现有索引条目总数的20%的这些表上的索引。但Oracle现在强烈建议不要定期重建索引。具体可以参考文章：Oracle重建索引的必要性。尽管如此重建索引还是有必要的，只是不建议定期。本文给出了重建索引的脚本供大家参考。
1、重建索引shell脚本
robin@SZDB:~/dba_scripts/custom/bin>morerebuild_unbalanced_indices.sh
#+-------------------------------------------------------+
#+Rebulidunblancedindices|
#+Author:Leshami|
#+Parameter:No|
#+-------------------------------------------------------+
#!/bin/bash
#--------------------
#Definevariable
#--------------------
if[-f~/.bash_profile];then
.~/.bash_profile
fi
DT=`date+%Y%m%d`;exportDT
RETENTION=1
LOG_DIR=/tmp
LOG=${LOG_DIR}/rebuild_unbalanced_indices_${DT}.log
DBA=Leshami@12306.cn
#------------------------------------
#Loopallinstanceincurrentserver
#-------------------------------------
echo"Currentdateandtimeis:`/bin/date`">>${LOG}
fordbin`ps-ef|greppmon|grep-vgrep|grep-vasm|awk'{print$8}'|cut-c10-`
do
echo"$db"
exportORACLE_SID=$db
echo"CurrentDBis$db">>${LOG}
echo"===============================================">>${LOG}
$ORACLE_HOME/bin/sqlplus-S/nolog@/users/robin/dba_scripts/custom/sql/rebuild_unbalanced_indices.sql>>${LOG}
done;
echo"Endofrebuildingindexforallinstanceat:`/bin/date`">>${LOG}
#-------------------------------------
#Checklogfile
#-------------------------------------
status=`grep"ORA-"${LOG}`
if[-z$status];then
mail-s"Succeededrebuildingindiceson`hostname`!!!"${DBA}<${LOG}
else
mail-s"Failedrebuildingindiceson`hostname`!!!"${DBA}<${LOG}
fi
#------------------------------------------------
#Removingfilesolderthan$RETENTIONparameter
#------------------------------------------------
find${LOG_DIR}-name"rebuild_unb*"-mtime+$RETENTION-execrm{}\;
exit
2、重建索引调用的SQL脚本
robin@SZDB:~/dba_scripts/custom/sql>morerebuild_unbalanced_indices.sql
conn/assysdba
setserveroutputon;
DECLARE
resource_busyEXCEPTION;
PRAGMAEXCEPTION_INIT(resource_busy,-54);
c_max_trialCONSTANTPLS_INTEGER:=10;
c_trial_intervalCONSTANTPLS_INTEGER:=1;
pmaxheightCONSTANTINTEGER:=3;
pmaxleafsdeletedCONSTANTINTEGER:=20;
CURSORcsrindexstats
IS
SELECTNAME,
height,
lf_rowsASleafrows,
del_lf_rowsASleafrowsdeleted
FROMindex_stats;
vindexstatscsrindexstats%ROWTYPE;
CURSORcsrglobalindexes
IS
SELECTowner,index_name,tablespace_name
FROMdba_indexes
WHEREpartitioned='NO'
ANDownerIN('GX_ADMIN');
CURSORcsrlocalindexes
IS
SELECTindex_owner,index_name,partition_name,tablespace_name
FROMdba_ind_partitions
WHEREstatus='USABLE'
ANDindex_ownerIN('GX_ADMIN');
trialPLS_INTEGER;
vcountINTEGER:=0;
BEGIN
trial:=0;
/*Globalindexes*/
FORvindexrecINcsrglobalindexes
LOOP
EXECUTEIMMEDIATE
'analyzeindex'||vindexrec.owner||'.'||vindexrec.index_name||'validatestructure';
OPENcsrindexstats;
FETCHcsrindexstatsINTOvindexstats;
IFcsrindexstats%FOUND
THEN
IF(vindexstats.height>pmaxheight)
OR(vindexstats.leafrows>0
ANDvindexstats.leafrowsdeleted>0
AND(vindexstats.leafrowsdeleted*100/vindexstats.leafrows)>
pmaxleafsdeleted)
THEN
vcount:=vcount+1;
DBMS_OUTPUT.PUT_LINE(
'Rebuildingindex'||vindexrec.owner||'.'||vindexrec.index_name||'...');
<<alter_index>>
BEGIN
EXECUTEIMMEDIATE
'alterindex'
||vindexrec.owner||'.'
||vindexrec.index_name
||'rebuild'
||'parallelnologgingcomputestatistics'
||'tablespace'
||vindexrec.tablespace_name;
EXCEPTION
WHENresource_busyORTIMEOUT_ON_RESOURCE
THEN
DBMS_OUTPUT.PUT_LINE(
'alterindex-busyandwaitfor1sec');
DBMS_LOCK.sleep(c_trial_interval);
IFtrial<=c_max_trial
THEN
GOTOalter_index;
ELSE
DBMS_OUTPUT.PUT_LINE(
'alterindexbusyandwaited-quitafter'
||TO_CHAR(c_max_trial)
||'trials');
RAISE;
ENDIF;
WHENOTHERS
THEN
DBMS_OUTPUT.PUT_LINE('alterindexerr'||SQLERRM);
RAISE;
END;
ENDIF;
ENDIF;
CLOSEcsrindexstats;
ENDLOOP;
DBMS_OUTPUT.PUT_LINE('Globalindicesrebuilt:'||TO_CHAR(vcount));
vcount:=0;
trial:=0;
/*Localindexes*/
FORvindexrecINcsrlocalindexes
LOOP
EXECUTEIMMEDIATE
'analyzeindex'
||vindexrec.index_owner||'.'
||vindexrec.index_name
||'partition('
||vindexrec.partition_name
||')validatestructure';
OPENcsrindexstats;
FETCHcsrindexstatsINTOvindexstats;
IFcsrindexstats%FOUND
THEN
IF(vindexstats.height>pmaxheight)
OR(vindexstats.leafrows>0
ANDvindexstats.leafrowsdeleted>0
AND(vindexstats.leafrowsdeleted*100/vindexstats.leafrows)>
pmaxleafsdeleted)
THEN
vcount:=vcount+1;
DBMS_OUTPUT.PUT_LINE(
'Rebuildingindex'||vindexrec.index_owner||'.'||vindexrec.index_name||'...');
<<alter_partitioned_index>>
BEGIN
EXECUTEIMMEDIATE
'alterindex'
||vindexrec.index_owner||'.'
||vindexrec.index_name
||'rebuild'
||'partition'
||vindexrec.partition_name
||'parallelnologgingcomputestatistics'
||'tablespace'
||vindexrec.tablespace_name;
EXCEPTION
WHENresource_busyORTIMEOUT_ON_RESOURCE
THEN
DBMS_OUTPUT.PUT_LINE(
'alterpartitionedindex-busyandwaitfor1sec');
DBMS_LOCK.sleep(c_trial_interval);
IFtrial<=c_max_trial
THEN
GOTOalter_partitioned_index;
ELSE
DBMS_OUTPUT.PUT_LINE(
'alterpartitionedindexbusyandwaited-quitafter'
||TO_CHAR(c_max_trial)
||'trials');
RAISE;
ENDIF;
WHENOTHERS
THEN
DBMS_OUTPUT.PUT_LINE(
'alterpartitionedindexerr'||SQLERRM);
RAISE;
END;
ENDIF;
ENDIF;
CLOSEcsrindexstats;
ENDLOOP;
DBMS_OUTPUT.PUT_LINE('Localindicesrebuilt:'||TO_CHAR(vcount));
END;
/
exit;
3、输入日志样本
Currentdateandtimeis:SunApr2002:00:02HKT2014 CurrentDBisSYBO2=============================================== RebuildingindexGX_ADMIN.SYN_OUT_DATA_TBL_PK... RebuildingindexGX_ADMIN.IDX_TDBK_SPLNK_PARENT_REF... RebuildingindexGX_ADMIN.IDX_TDBK_SPLNK_CHILD_REF... RebuildingindexGX_ADMIN.PK_TRADE_BROKER_TBL... RebuildingindexGX_ADMIN.IDX_TDBK_INPUT_DATE... ................
4、后记
a、如果同一台服务器上有多个实例，且每个实例有相同的schema，此脚本会轮巡所有实例并根据analyze结果来rebuild。
a、大家应根据需要作相应调整，如脚本的路径信息等。
b、需要修改相应的schemaname。
d、可根据系统环境调整相应的并行度。