Oracle重建索引Shell腳本、SQL腳本分享
2024-08-29 13:58:13
供稿：網友
這篇文章主要介紹了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=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、可根據系統環境調整相應的并行度。
