津島博士のパフォーマンス講座 indexページ▶▶

津島博士のパフォーマンス講座 
第3回 Statspackから探る、パフォーマンス問題の原因特定方法

■津島博士による解説が動画でも! セミナー動画公開中です。
津島博士のパフォーマンス講座「パフォーマンス問題はなぜ起きるのか」  【WMV】 【MP4】 【PDF

これまでパフォーマンス問題の原因と改善方法についていくつか説明してきましたが、そもそも原因を特定することができない方も多いと思います。Oracle Directへの問合せにも原因が分からないパフォーマンス問題が多いようです。そこで、今回は「パフォーマンス原因の特定の方法」について事例を使用して説明しようと思います。すべてのパターンの問題を特定して解決することは難しいので、これだけは知っておいてほしいと思う内容を説明しますので、参考にして下さい。

■1.Statspackについて
Statspackは、Oracle8iから提供されたパフォーマンス診断ツールです。Oracle Database 10gからは、AWR(Automatic Workload Repository)が提供されていますが、活用にあたってOracle Diagnostic Packのライセンスが必要になるため、すべてのエディションで利用可能なStatspackを用いて説明を進めます。
ちなみに、AWRはStatspackを進化させたものです。Statspackより多くの種類のレポートを、より見やすく作成することが可能です。また、Oracle Enterprise Managerから操作、参照することができるため、初心者の方でも簡単に使うことができます。
Statspackは最初に「Report Summary」があり、その後に様々な「統計情報」があります。「Report Summary」で問題を切り分けて、その問題に対する「統計情報」で詳細に分析するような見方をします。「統計情報」には「待機イベント統計」、「SQL」、「IO統計」、「セグメント統計」、「アドバイザ統計」などがあります。

オラクルエンジニア通信:「Oracle性能監視 特集~あなたはどっち?DBA1.0/2.0

「Report Summary」の「Instance Efficiency Percentages」は、それぞれの処理の効率良さを示しています。100%に近いほど効率良く処理されているということになります。「Top 5 Timed Events」は、発生している待機イベントの割合を示しています。「% Total Ela Time」の割合が高いイベントを見ることで原因などを特定できます。ここに「CPU time」も表示されます。「CPU time」が高いと待機イベントが少ない効率良い状態で処理されていることになります。以下の左の出力例のように「CPU time」がトップに来て多くの割合を占める場合、リソースが有効に使われていると判断できます。右の出力例のように「CPU time」よりも待機イベントの時間が長い場合、割合を占める待機イベント「db file sequential read」などがパフォーマンス問題の原因になっていると言えます。

img_tsushima_110216_01.gif

 一般的には 「CPU time」が上位に来るほどチューニングは必要ないと考えられますが、逆に効率の悪いSQL文が過剰にCPUを使用している可能性もありますので、「SQL」セクションで「CPU Time(s)」の高いSQLを探して実行計画などの確認をして下さい。「SQL」セクションや実行計画については、別の機会で説明したいと思います(これを次回に説明するとしても良いかもしれませんが)。

■2.事例の分析
ここからは、事例(今回は電子カルテ/オーダリング(OLTP)システム)のstatspackを例にとって、パフォーマンス問題の解決方法を説明します。

(1)「Report Summary」を分析する
先ずは「Top 5 Timed Events」を参照して「CPU time」より上位の待機イベントを確認します。この例では「CPU time」が存在しないのですべての待機イベントが対象になりますが、今回は上位4つまでにしました(5つ目のラッチ関係の調整は簡単ではないので、ここでは省略します)。

img_tsushima_110216_02.gif

  1. 「db file sequential read」待機イベント(70.04%)
    これは索引スキャンのときに発生するI/O待機イベントです。このようにI/O待機イベントが多い場合は、「IO統計」を参照してI/Oの状況を確認することになりますが、その前に「Instance Efficiency Percentages」のバッファヒット率(Buffer Hit %)を確認することが重要です(この値は、処理に必要なデータがメモリにキャッシュされている割合を示します。この値が低いと物理I/Oが多いことを示します)。キャッシュヒット率が低いためにI/O性能が悪化している場合があるからです。一般的には95%以上が理想といわれておりますが、この事例では75.87%と非常に低いことがわかります。そのため、キャッシュヒット率を上げるために、初期化パラメータ「DB_CACHE_SIZE」のチューニングをする必要があります。
  2. 「db file scattered read」待機イベント(20.61%)
    これはフルスキャンのときに発生するI/O待機イベントです。フルスキャンは索引スキャンと異なり、long tables(大きいサイズのテーブル)はデータベース・バッファ・キャッシュを使用しません(大きなテーブルはメモリにキープできないと判断するため)。そのため、キャッシュヒット率を改善しても影響しない場合がありますので、チューニングとしては
    • (ア) 索引スキャンするようにする
      (有効な索引を作成することですが、詳細な説明は別の機会とします)
    • (イ) I/O性能の改善(ストレージ構成の変更やディスク数を増やす)
    • (ウ) 初期化パラメータ「DB_FILE_MULTIBLOCK_READ_COUNT」の調整
      (この例では8ですので、最大I/Oサイズの128(1M / 8192:この事例のブロックサイズ)にします)
      (Oracle Database 10g R2からは自動設定されるので設定は不要)
    などが考えられます。ただし、1.のキャッシュヒット率を改善することでI/O性能が向上する場合もありますので、キャッシュヒット率の改善後に再度確認することで良いと思います。
  3. 「library cache pin」待機イベント(2.71%)
    3番目に来ていることから比率は低いですがライブラリキャッシュの競合が発生していることが分かります。そのためライブラリヒット率(Library Hit %)も低いです(この値は、必要なSQLやPL/SQLがメモリにキャッシュされている割合を示します。この値が低いとハードパース(実行計画の再作成)が多いことを示します)。この値も一般的には95%以上が理想といわれておりますが、この事例では89.76%と低いことがわかります。ライブラリヒット率を上げるために、初期化パラメータ「SHARED_POOL_SIZE」のチューニングをする必要があります。
  4. 「buffer busy waits」待機イベント(1.56%)
    4番目に来ているので影響は少ないと思いますが確認しておく必要があります。これはブロックの競合などが考えられます。「セグメント統計」の「Segments by Buffer Busy Waits」で競合が発生しているセグメント(表、索引)を確認して(セグメント統計はスナップショット・レベル=7で出力されますが、この事例では出力されていません)、対象セグメントのブロックサイズを小さくするかハッシュ・パーティションなどを使用してブロック競合を発生しないようにチューニングすることになります(詳細な説明は別の機会とします)。

 「Report Summary」の分析からデータベース・バッファ・キャッシュ、ライブラリ・キャッシュ、I/O性能についてチューニングが必要であることがわかりましたので、ここからはそれについて説明していきます。

(2)キャッシュヒット率を上げる
Oracle9iからは「Buffer Pool Advisory」にデータベース・バッファ・キャッシュのサイズに対するアドバイス情報がありますので、これを参照すれば設定が簡単に行えると思います(アドバイス情報は初期化パラメータ「STATISTICS_LEVEL」がTYPICAL(デフォルト)またはALLでないと出力されません)。この事例では出力されていませんでしたので、以下の出力例を使用して説明します。「Size Factr」の1.0が現行サイズです。「Est Physical Read Factor」が現行サイズの物理読込み回数を1.00とした時の、各サイズで見積もった物理読み込み回数の割合です(これが少なくなると物理I/Oが削減される)ので、この値があまり変化しなくなる値に設定します。これを参考にすることでサイズ設定を簡単に行うことができると思いますので、キャッシュヒット率が低いときには活用して下さい。

img_tsushima_110216_03.gif

(3)ライブラリヒット率を上げる
Oracle9iからは「Shared Pool Advisory」にライブラリ・キャッシュのサイズに対するアドバイス情報がありますので、これを参照すれば設定が簡単に行えると思います。この情報も出力されていませんので、以下の出力例を使用して説明します。「SP Size Factr」の1.0が現行サイズです。「Estd LC Time Saved Factr」が現行の共有プール・サイズの解析時間を1.00とした時の、各サイズで見積もった解析時間の割合です(これが少なくなるとハードパースが削減される)ので、この値があまり変化しなくなる値に設定します。これを参考にすることでサイズ設定を簡単に行うことができると思いますので、ライブラリヒット率が低いときには活用して下さい。この例ではサイズを変更してもあまり効果がありませんが、ライブラリヒット率が低い場合には効果が現れてきます。

img_tsushima_110216_04.gif

 今回のバージョンはOracle9i R2ですのでアドバイス機能を使用して設定しますが、図のようにOracle Database 10gからは自動共有メモリ管理(初期化パラメータ「SGA_TARGET」を指定するとデータベース・バッファ・キャッシュと共有プールなどを自動的に調整してくれる機能)、Oracle Database 11gからは自動メモリ管理(初期化パラメータ「MEMORY_TARGET」を指定するとPGAも含めて自動調整してくれる機能)が使用できますので、これを使用することをお勧めします。

img_tsushima_110216_05.gif

各バージョンによるメモリ設定の初期化パラメータ

(4)I/O性能を確認する
「Tablespace IO Stats」で読み取り回数「Reads」や平均読み取り時間「Av Rd(ms)」などからI/O状況を確認してください。「Reads」がどこかの表領域に集中している場合は、そこがボトルネックとなってパフォーマンス低下の原因になりますので、そのようになっている場合は配置を改善して下さい。この例では表領域DATACに集中していることが分かります(複数のデータファイルからなっている場合は「File IO Stats」も確認して集中しているデータファイルを特定して下さい)。表領域DATADなどの「Av Rd(ms)」が高い表領域もありますので、これも改善する必要があります。この例の「File IO Stats」を見ると"J:\ORD0\DATA"の性能が悪いようです(このディスクに集中していると思われます)ので、これを改善すると良いと思われます(どのように改善するかはストレージ構成を確認する必要がありますので、ここでは説明しません)。

img_tsushima_110216_06.gif

img_tsushima_110216_07.gif

 Oracle9iからストレージ構成はS.A.M.E.(Stripe And Mirror Everything)を推奨しています。これはすべてのディスクをストライプして、可用性のためにはミラーリングするという構成になります。これを使用すると偏りなどは基本的に無くなるので管理が簡単になります。Oracle Database 10g以降であれば自動ストレージ管理(ASM)機能を使用していただければS.A.M.Eに基づいたストレージ構成を自動的に行いますので、これを使用することをお勧めします。

img_tsushima_110216_08.gif

オラクルエンジニア通信:「Oracle ASMを1から学ぶ - マニュアル、インストール・構築、設定・管理

■3.おわりに
今回はStatspackからパフォーマンス原因を特定する方法について特にI/Oに関係する原因を説明しました。他にも様々な原因はありますが、RDBMSではほとんどがI/Oの部分が原因でパフォーマンス問題が発生しますので、皆様のシステムでも確認してみて下さい。
今後の内容の参考にもさせていただきたいと思いますので、ご意見ご質問、お待ちしております。
それでは、次回まで、ごきげんよう。

img_tsushima.gif ■津島博士より
長年に渡りデータベースの構築やパフォーマンスチューニングなどに従事し、最近では若手エンジニアの育成および大規模データベース案件などの支援に従事しております。今までの経験が少しでもお役に立てればと思い、この連載を始めることにいたしました。できるだけ長く続けたいと思いますのでよろしくお願いいたします。

Oracle Databaseは、技術の進化により非常に扱いやすくなったと思います。私自身も昔のバージョンを使用したころに比べると非常に楽になったと感じています。いろいろと進化したとはいえパフォーマンス問題が発生しなくなった訳ではありません。今でも多くの担当者が色々と苦労していると思います。その中でスキルや機能を知らずに苦労している場合もあるように思いますので、ここで紹介していけたらと考えています。

この連載では、このようなOracle技術者(データベース技術者)の方へのアドバイスとして様々なパフォーマンス問題を題材に解説していこうと考えています。既にデータベース運用を行っている管理者、これから管理者を目指す方までを対象に、様々な疑問に対して少しでも何かの手助けになればと願っています。できるだけ読者の皆様からの疑問に答えていきたいと思っておりますので(問合せなどの具体例を使用して説明した方が分かりやすいと思いますので)、パフォーマンス問題に関する様々な質問をお願いいたします。

津島博士の記事についてのご質問はこちらまでお願いいたします。

津島博士のパフォーマンス講座 indexページ▶▶