Oracle 運用術 ： インデックスと統計情報の自動再構築

先日コメントで oracle の質問を頂いたのですが、10g RAC のシステムに変更してから運用の難易度がグット上がってしまったので運用を外注してしまっています。そうすると不思議なことに（いや不思議じゃないんだが・・・）メキメキと DBA 運用術を忘れていきます。

と言うことで、僕が oracle 8i の時によく使っていた sql とかを書きためておこうと思います。公開する sql 等はネットで探したものもあれば自分で書いたものもあります。もう数年前に書きためた sql ばかりなのでどれがどれだか覚えてもいませんが・・・。

今回はインデックスと統計情報の自動再構築です。この２つを再構築しておくことでいつも最新のデータの統計情報に基づいたコストベースの実行計画で sql を実行してくれる等になります。もっとも実行計画が変わる場合も”ある”と言うことを認識しないと、実際にはなんだか急にレスポンスが悪くなったとかっていう場合もあり得ますのでご注意を。

僕の場合は、実行計画を変更したくない場合は、sql に hint 句をつけてアプリ側でどうこうしています。綺麗じゃないけど、oracle 任せで 100% 最速な実行計画を練ってくれる訳ではないと経験上思っています。

Oracle 運用術 ： インデックスと統計情報の自動再構築

まずは、あるユーザが保持する全てのインデックスを再構築し、テーブルとインデックスの統計情報を再構築する sql について。spool する場所とかのしては適宜変更して下さい。

※oracle EE 向けの sql です。再構築時に online 指定があります。oracle SE 向けの場合は online オプションを外して下さい。但し、インデックス再構築時にテーブルロックがかかるので oracle SE の方は実行するタイミングには十分ご注意を。

idxrebuild_and_analyze.sql

spool /tmp/idxrebuild_and_analyze.lst

DECLARE
BEGIN
    /* インデックスを再構築＆アナライズ */
    FOR CUR IN (SELECT INDEX_NAME FROM USER_INDEXES) LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX "' ||CUR.INDEX_NAME||'" REBUILD ONLINE NOLOGGING';
        EXECUTE IMMEDIATE 'ANALYZE INDEX "' ||CUR.INDEX_NAME||'" ESTIMATE STATISTICS SAMPLE 30 PERCENT';
    END LOOP;

    /* ユーザーテーブルのアナライズ */
    FOR CUR IN (SELECT TABLE_NAME FROM USER_TABLES) LOOP
        EXECUTE IMMEDIATE 'ANALYZE TABLE ' ||CUR.TABLE_NAME||' ESTIMATE STATISTICS SAMPLE 30 PERCENT';
    END LOOP;
END;
/

spool off

この sql を cron で定期的に実行します。月次での実行が経験上良いと考えています。※短い周期で実行してもデータがそれほど変化しているわけでもないし、月次実行なら実行計画が変わってしまっても比較的気がつきやすい。四半期に一回だと実行されたことを忘れてしまいがち。
shell スクリプトの初めの方の各種変数はご自分の環境に合わせて定義を変更下さい。

ora_idx_rebuild.sh

#!/bin/sh
## Please run by User oinstall.

## ora_idx_rebuild.sh ver 0.01 ================================================
source /etc/bashrc

mailaddress='user@your domain .jp'
MachineId=`hostname`
dbuser=test
dbpw=test123
tnsname=testdb
script=/tmp/idxrebuild_and_analyze.sql
##=============================================================================

## BEGIN BLOCK 1.2 : MailOnGood()----------------------------------------------
MailOnGood ()
{
    Today=`date '+%Y/%m/%d'`
    Time=`date '+%T'`
    MailTo=$mailaddress
    echo "===================
$0
$Today
$Time

HostName: $MachineId
Database: ${ORACLE_SID}
===================
$message
" | mail -s "ORACLE AUTO index-rebuild & Analyze" $MailTo
}
## ENDBLOCK 1.2----------------------------------------------------------------


## BEGIN BLOCK 1 : ORACLE Status SQL no1 --------------------------------------
CHK=`
sqlplus $dbuser/$dbpw@$tnsname << Eof 2>&1
    @ $script
    exit
Eof`

## END BLOCK 1 ----------------------------------------------------------------

MailOnGood
exit 0

ora_idx_rebuild/sh を実行すると自分宛に完了通知が届くと思います。チューニングのための定期運用は面倒くさくて忘れてしまいがちなので自動化してしまおうという術の１つでした。

ご利用に当たっては本スクリプトのメリットデメリットを理解の上どうぞ〜

当分はこの手のネタを公開していこうかと思ってます。