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

前端之家收集整理的这篇文章主要介绍了Oracle重建索引Shell脚本、SQL脚本分享前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

索引是提高数据库查询性能的有力武器。没有索引,就好比图书馆没有图书标签一样,找一本书自己想要的书比登天还难。然而索引在使用的过程中,尤其是在批量的DML的情形下会产生相应的碎片,以及B树高度会发生相应变化,因此可以对这些变化较大的索引进行重构以提高性能。N久以前Oracle建议我们定期重建那些高度为4,已删除的索引条目至少占有现有索引条目总数的20%的这些表上的索引。但Oracle现在强烈建议不要定期重建索引。具体可以参考文章。尽管如此重建索引还是有必要的,只是不建议定期。本文给出了重建索引的脚本供大家参考。

1、重建索引shell脚本

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_unbalancedindices${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脚本

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,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 ' || <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>ERRM);
     RAISE;
  END;
 END IF;

END IF;

CLOSE csrindexstats;
END LOOP;

DBMS_OUTPUT.PUT_LINE ('Local indices rebuilt: ' || TO_CHAR (vcount));
END;
/
exit;

3、输入日志样本

4、后记

a、如果同一台服务器上有多个实例,且每个实例有相同的schema,此脚本会轮巡所有实例并根据analyze结果来rebuild。
a、大家应根据需要作相应调整,如脚本的路径信息等。
b、需要修改相应的schema name。
d、可根据系统环境调整相应的并行度。

原文链接:https://www.f2er.com/oracle/65238.html

猜你在找的Oracle相关文章