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=[e-mail]

# ------------------------------------
# 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、可根据系统环境调整相应的并行度。