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、可根据系统环境调整相应的并行度。