# Oracle 運用術 : インデックスと統計情報の自動再構築 - URL: https://www.drk7.jp/MT/archives/001222.html - 公開: 2007.05.27 - カテゴリ: データベース技術 - 著者: drk ![Oracle 運用術 : インデックスと統計情報の自動再構築のイメージ(挿絵)](https://www.drk7.jp/media/opt/posts/001222/illust.jpg.w1600.webp) 先日コメントで 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](https://www.drk7.jp/pub/sql/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](https://www.drk7.jp/pub/sql/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つでした。 ご利用に当たっては本スクリプトのメリットデメリットを理解の上どうぞ〜 当分はこの手のネタを公開していこうかと思ってます。