圆月山庄资源网

Oracle重建索引Shell脚本、SQL脚本分享
数据库 2024/12/23 佚名
3 2
圆月山庄资源网 Design By www.vgjia.com

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

标签：
Oracle,重建索引,Shell脚本,SQL脚本
圆月山庄资源网 Design By www.vgjia.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
圆月山庄资源网 Design By www.vgjia.com
www.vgjia.com 圆月山庄资源网
139,976互联网资源
144,792高清电影
21,817无损音乐
631,128技术资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接