• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    Oracle重建索引Shell脚本、SQL脚本分享

    索引是提高数据库查询性能的有力武器。没有索引,就好比图书馆没有图书标签一样,找一本书自己想要的书比登天还难。然而索引在使用的过程中,尤其是在批量的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数据库密码重置、导入导出库命令示例应用
    • oracle sequence语句重置方介绍
    • oracle 重置sys密码的方法介绍
    • oracle 重置序列从指定数字开始的方法详解
    • PowerShell 自动备份oracle并上传到ftp
    • Shell实现的Oracle启动脚本分享
    • shell脚本操作oracle删除表空间、创建表空间、删除用户
    • oracle执行cmd的实现方法
    • Windows下编写批处理脚本来启动和重置Oracle数据库
    上一篇:Oracle关于重建索引争论的总结
    下一篇:Oracle索引质量介绍和分析脚本分享
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    Oracle重建索引Shell脚本、SQL脚本分享 Oracle,重建,索引,Shell,脚本,