欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-29 13:20:53
索引是提高数据库查询性能的有力武器。没有索引,就好比图书馆没有图书标签一样,找一本书自己想要的书比登天还难。然而索引在使用的过程中,尤其是在批量的dml的情形下会产生相应的...

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