大家好,又见面了,我是全栈君,今天给大家准备了Idea注册码。
该指数是一个有力的武器,以提高数据库的查询性能。
没有索引,喜欢同样的标签库没有书籍,找书,他们想预订比登天还难。中,尤其是在批量的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、假设同一台server上有多个实例,且每一个实例有同样的schema。此脚本会轮巡全部实例并依据analyze结果来rebuild。
a、大家应依据须要作对应调整。如脚本的路径信息等。
b、须要改动对应的schema name。
d、可依据系统环境调整对应的并行度。
5、相关參考
Oracle 聚簇因子(Clustering factor)
Oracle 索引监控(monitor index)
Oracle 索引监控与外键索引
收集统计信息导致索引被监控
Oracle 监控索引的使用率
NULL 值与索引(一)
NULL 值与索引(二)
函数使得索引列失效
版权声明:本文博主原创文章,博客,未经同意不得转载。
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/116963.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...