Oracle 重建索引脚本

      索引是提高数据库查询性能的有力武器。没有索引,就好比图书馆没有图书标签一样,找一本书自己想要的书比登天还难。然而索引在使用的过程中,尤其是在批量的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、如果同一台服务器上有多个实例,且每个实例有相同的schema,此脚本会轮巡所有实例并根据analyze结果来rebuild。  a、大家应根据需要作相应调整,如脚本的路径信息等。 b、需要修改相应的schema name。 d、可根据系统环境调整相应的并行度。

5、相关参考

  • Oracle 聚簇因子(Clustering factor)
  • Oracle 索引监控(monitor index)
  • Oracle 索引监控与外键索引
  • 收集统计信息导致索引被监控
  • Oracle 监控索引的使用率
  • NULL 值与索引(一)
  • NULL 值与索引(二)
  • 函数使得索引列失效
  • Oracle 索引质量分析
  • Oracle 重建索引的必要性

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Oracle 绑定变量窥探

    Bind Peeking是Oracle 9i中引入的新特性,一直持续到Oracle 10g R2。它的作用就是在SQL语句硬分析的时候,查看一下当前...

35730
来自专栏Jerry的SAP技术分享

使用BAPISDORDER_GETDETAILEDLIST创建S/4HANA的Outbound Delivery

要在S/4HANA里创建Outbound Delivery,首先要具有一个销售订单,ID为376,通过事务码VA03查看。

13310
来自专栏杨建荣的学习笔记

通过shell解析dump生成parfile(r2笔记76天)

当我们得到一个dump文件的时候,总是有些不太确定dump文件中是否含有一些我们原本不希望出现的表,如果在未知的情况下对dump文件进行操作时很危险的,比如我们...

22630
来自专栏杨建荣的学习笔记

MySQL和Oracle行值表达式对比(r11笔记第74天)

行值表达式也叫作行值构造器,在很多SQL使用场景中会看到它的身影,一般是通过in的方式出现,但是在MySQL和Oracle有什么不同之处呢。我们做几个简单的测试...

34980
来自专栏沃趣科技

应用示例荟萃 | performance_schema全方位介绍(下)

使用performance_schema中的语句当前事件记录表和语句事件历史记录表可以查询数据库中最近执行的一些SQL语句,以及语句相关的信息,这里我们以eve...

28430
来自专栏数据和云

书接上文:薛定谔的猫是如何诞生的?

编辑手记:注重细节,是DBA必要的基本素质要求。 书接上文(参考:空与非空 - 数据库中也有薛定谔的猫?),其实CBO的判断本身是没有问题的,问题在于,为什...

327100
来自专栏乐沙弥的世界

Oracle 索引监控(monitor index)

      合理的为数据库表上创建战略性索引,可以极大程度的提高了查询性能。但事实上日常中我们所创建的索引并非战略性索引,恰恰是大量冗余或是根本没有用到的索引耗...

12610
来自专栏杨建荣的学习笔记

浅谈exp/imp(上) (r5笔记第81天)

作为DBA,经常需要在不同数据库环境间做数据的导入导出,exp/imp就是这样的轻便快捷的客户端工具,可以很方便的在不同数据库之间转移数据对象,即使数据库位于不...

31280
来自专栏乐沙弥的世界

函数使得索引列失效

      在索引列上使用函数使得索引失效的是常见的索引失效原因之一,因此尽可能的避免在索引列上使用函数。尽管可以使用基于函数的索引来 解决索引失效的问题,但...

15430
来自专栏乐沙弥的世界

批量生成sqlldr文件,高速卸载数据

      SQL*Loader 是用于将外部数据进行批量高速加载的数据库的最高效工具,可用于将多种平面格式文件加载到Oracle数据库。SQL*Loader支...

23020

扫码关注云+社区

领取腾讯云代金券