无为清净楼资源网 Design By www.qnjia.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.qnjia.com
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
无为清净楼资源网 Design By www.qnjia.com

稳了!魔兽国服回归的3条重磅消息!官宣时间再确认!

昨天有一位朋友在大神群里分享,自己亚服账号被封号之后居然弹出了国服的封号信息对话框。

这里面让他访问的是一个国服的战网网址,com.cn和后面的zh都非常明白地表明这就是国服战网。

而他在复制这个网址并且进行登录之后,确实是网易的网址,也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情,因为以前都没有出现这样的情况,现在突然提示跳转到国服战网的网址,是不是说明了简体中文客户端已经开始进行更新了呢?