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

データベース技術

19年前に書いた記事です。製品やサービスの情報は公開当時のものです。

目次(4項目)
  1. 再構築の効果と注意点
  2. インデックスと統計情報を再構築する SQL
  3. cron で月次実行するシェルスクリプト
  4. まとめ

Oracle 運用術 : インデックスと統計情報の自動再構築のイメージ(挿絵)

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

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

再構築の効果と注意点

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

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

インデックスと統計情報を再構築する SQL

まずは、あるユーザが保持する全てのインデックスを再構築し、テーブルとインデックスの統計情報を再構築する 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

cron で月次実行するシェルスクリプト

この SQL を cron で定期的に実行します。月次での実行が経験上良いと考えています。理由はこんなところです。

  • 短い周期で実行してもデータがそれほど変化しているわけでもない
  • 月次実行なら実行計画が変わってしまっても比較的気がつきやすい
  • 四半期に一回だと実行されたことを忘れてしまいがち

シェルスクリプトの初めの方の各種変数はご自分の環境に合わせて定義を変更下さい。

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 を実行すると自分宛に完了通知が届くと思います。

まとめ

チューニングのための定期運用は面倒くさくて忘れてしまいがちなので自動化してしまおうという術の1つでした。

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

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

コメント 2件

  1. TOKK

    困って探していたところ、ベストな解答でした。
    今後ともよろしくお願いしたします。
  2. drk管理人

    TOKK さん>
    お役に立てて光栄です。Oracleネタはビジネスマンに人気が高いですね。僕が公開しているネタは全部自分が使っているものの中である程度枯れた(バグがとれた)ヤツを公開しているのである程度安心して使って頂けるかと思っています。

この記事へのコメントの受け付けは終了しています。