Statspackを見ることがあり、毎回色々と調べていたので手順をまとめてみました。
これで、次回からの解析は楽になるはず！
目次
大まかな手順
① Statspack取得時のセッション数を確認する。
② Load Profile からアプリケーションの傾向を把握する。
③ Instance Efficiency Percentages からインスタンス効率をチェックする。
④ Report Summaryで待機イベントを確認する。
⑤ ④で問題がありそうな待機イベントの統計情報を確認する。
⑥ SQL ordered から遅いSQL、実行回数が多いSQLなどを確認する。
手順の詳細
① Statspack取得時のセッション数を確認する
目的
Statspack を取得した際の負荷状況（負荷が高いのか、低いのか）を確認します。
パラメータ
Begin Snap、End Snap からセッション数を確認します。
備考
セッション数からの負荷状況は参考程度とする。
② Load Profile からアプリケーションの傾向を把握する
目的
どんなトランザクションが多くて、どれだけ時間がかかっているかを確認する。
パラメータ
| パラメーター | 意味 |
|---|---|
| Logical reads | SGAから読み込んだブロック数 |
| Physical reads | ディスクから読み込んだブロック数 |
| Physical writes | ディスクに書き込んだブロック数 |
| Hard parses | SQLのハードパース回数 |
③ Instance Efficiency Percentages からインスタンス効率をチェックする
目的
バッファヒット率などから効率よく資源が使用されているか確認する。
理想とする指標
| 理想値 | パラメーター名 |
|---|---|
| 80%以上 | Non-Parse CPU |
| 90%以上 | Buffer Hit, In-memory Sort, Soft Parse |
| 95%以上 | Library Hit, Redo Nowait, Buffer Nowait |
| 98%以上 | Latch Hit |
各パラメーターの意味
| パラメーター名 | 意味 |
|---|---|
| Buffer Hit | 必要なデータがバッファ上にあった割合 |
| Library Hit | 必要なSQL、PL/SQLがライブラリ・キャッシュにあった割合（SGAにキャッシュされている） |
| Soft Parse | 全ての解析のうち再利用可能なものの割合 |
| In-Memory Sort | ソートがメモリ内で行われた割合 |
| Latch Hit | 全てのラッチのヒット率 |
| Parse CPU to Parse Elapsed | 解析CPU時間/ 解析の合計時間 |
| Execute to Parse | SQL実行に対し解析が行われなかった割合 |
| non-parse CPU | 解析以外で使用されたＣＰＵ時間の割合 |
| Buffer Nowait | バッファに要求を出したときに、即座に使用可能だった割合 |
| Redo Nowait | redo logに要求を出したときに、即座に使用可能だった割合 |
④ Report Summaryで待機イベントを確認する
目的
待機が多く発生しているイベントを特定する。
見方
「Top 5 Timed Events」に発生している待機イベントの割合が記載されています。
その中で、「CPU time」より多く、「Total Ela Time」 を使用しているイベントに注目します（パフォーマンス劣化の原因の可能性がある）。
ただし、「CPU time」が上位でも、効率の悪いSQL文が過剰にCPUを使用していて、「CPU time」が上位にある可能性もある。
その場合は、「SQL」セクションで「CPU Time(s)」の高いSQLを探して実行計画などを確認
します。
⑤ ④で問題がありそうな待機イベントの統計情報を確認する
各待機イベントの意味
・db file sequential read
索引スキャンのときに発生するI/O待機イベント。
このイベントが上位にきている場合は、「Instance Efficiency Percentages」のバッファヒット率（Buffer Hit %）を確認します。
もし、バッファヒット率が低い場合、物理I/Oが多くなり待機が発生します。
＜改善方法＞
・SQL ordered by GetsからBuffer Getsが多いSQL文を確認してチューニングできるか確認する。
・ キャッシュヒット率が上がると効果があるため、初期化パラメーター「DB_CACHE_SIZE」をチューニングする。
・db file scattered read
フルスキャンのときに発生するI/O待機イベント。
フルスキャン時は、大きいサイズのテーブルはデータベース・バッファ・キャッシュを使用しない。
＜改善方法＞
・SQL ordered by GetsからBuffer Getsが多いSQL文を確認してチューニングできるか確認する。
・索引スキャンを行うように、インデックスの追加やwhere句の条件を見直す。
・初期化パラメータ「DB_FILE_MULTIBLOCK_READ_COUNT」をチューニングする。
・library cache pin
ライブラリキャッシュの競合で発生する待機イベント。
ライブラリヒット率（Library Hit %）は、SQL・PL/SQLがメモリにキャッシュされている割合で、この値が低い場合、ハードパース（実行計画の再作成）が多く行われる。
＜改善方法＞
・初期化パラメータ「SHARED_POOL_SIZE」をチューニングして、ライブラリヒット率を上げる。
・buffer busy waits
同じブロックに対して同時アクセス要求があった場合、後から要求したプロセスは瞬間的に待ち状態になる。その際の待機イベント。
この待機イベントが発生する原因は、特定のセグメント・ブロックがホットになっている場合が多い。
＜改善方法＞
・「セグメント統計」の「Segments by Buffer Busy Waits」で競合が発生しているセグメント（表、索引）を確認して、対象セグメントのブロックサイズを小さくするか、もしくはハッシュ・パーティションなどを使用してブロック競合を発生しないようにチューニングする。
・ASSMや自動Undo管理を利用する。
・free buffer waits
サーバ・プロセスがバッファの空き領域を待つ待機イベント。
＜改善方法＞
・File IO Statsから書き込みが多いデータファイルを確認する。また、OS統計から書き込み時間が妥当かを確認して、遅い場合は、I/Oのチューニングが必要になる。
⑥ SQL ordered から遅いSQL、実行回数が多いSQLなどを確認する
目的
特徴的（遅い、実行回数が多い）なSQLがないか確認し、SQLの改善ができるか検討する。
パラメーター一覧
SQL ordered by Elapsed Time
経過時間が長いSQLが記載される。
項目の中の%CPUと%IOで、CPUとI/Oのどちらの割合が大きいか確認することができる。
経過時間が長いので、改善対象にはなるが問題の根本原因かまでは判断できない。
SQL ordered by CPU Time
サーバー・プロセスのCPU時間が多いSQL（経過時間に対する比率が％CPU）が記載される。
CPU Timeの割合が多い場合には、これと処理行数が上位のSQLの実行計画を確認する（処理行数を削減することで改善できるかを確認する）。
SQL ordered by User I/O Wait Time
サーバー・プロセスのI/O待機時間が長いSQL（経過時間に対する比率が％IO）が記載されます。
SQL ordered by Gets
論理I/O（DBバッファ・キャッシュ経由の論理I/O）が多いSQLが記載されます。
キャッシュ・ヒット率の問題で時間が長いのではないことを確認します。
SQL ordered by Reads
すべて（フラッシュ・キャッシュ＋非フラッシュ・キャッシュ）のDBブロックの読込みが多いSQLが記載されます。
DBブロックのI/Oが多い場合は、これとSQL ordered by User I/O Wait Timeが上位のSQLの実行計画を確認します。
I/O時間と読み込み数も確認。（読み込み数を削減することで改善できるか確認）
SQL ordered by Physical Reads(UnOptimized)
非フラッシュ・キャッシュからのDBブロックの読込みが多いSQL（Oracle Database 11gR2から）が記載されます。
フラッシュ・キャッシュがない場合はSQL ordered by Readsと同じになります。
SQL ordered by Executions
実行回数が多いSQL（全体と１回あたりの処理行数が分かる）が記載されます。
CPU TimeやダイレクトI/Oが多い場合には、これの処理行数が多いSQLも確認します。
SQL ordered by Parse Calls
解析コール数（ハード及びソフト・パース）が多いSQLが記載されます。
cursor: pin Sが多い場合には、ソフト・パースが多いという意味なので、上位のSQLを確認して、同時実行数の削減を検討します。
cursor: pin X/cursor: mutex X/library cache: mutex ..が多い場合は、ハード・パースが多いことなので、SQL ordered by Versoion Countが上位のSQLを確認して、ハード・パースの数を削減します（共有メモリを増やす、カーソルの共有化などを行う）。
SQL ordered by Sharable Memory
共有メモリ使用量が多いSQL（デフォルトは1Mバイト以上）が記載されます。
複雑なSQLや子カーソルが多く共有メモリが不足するような場合には削減を検討します。
SQL ordered by Version Count
子カーソル（同一SQLで実行計画が異なる）が多いSQLが記載されます。
cursor: mutex ../library cache: mutex ..が多い場合は、上位のSQLを確認して、子カーソルの削減を検討します。
SQL ordered by Cluster Wait Time
クラスタ待機時間が多いSQL（RACキャッシュ・フュージョン関係）が記載されます。
DBブロック競合のため、無駄なブロックにアクセスしない、パーティション化などを行います。
まとめ
Statspackの確認は大変ですが、上記の手順で分析することで少しでも解決の糸口が見えればいいなと思います。
個人的にまとめたメモなので、上記の手順が汎用的に使用できるかは・・・。
ただ、サポートに問い合わせる前のチェックポイントとしては役立つ（だったらいいな）と思います。
参考にさせていただいたサイト
https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsm03-1598233-ja.html
https://www.oracle.com/technetwork/jp/content/20100728statspack-tips-251843-ja.pdf
https://docs.oracle.com/cd/E16338_01/server.112/b56311/stats002.htm
http://otn.oracle.co.jp/skillup/stats_diag/2/
https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-32-2047371-ja.html