STATSPACK レポートの見方

自分用のメモです。ablog 全般に言えることですが、間違いだらけなので参考にしないで下さいw

[STATSPACK report for -> Sessions]

[概要]

スナップショット取得時のセッション数を見ることができる。
平常時のスナップショットを取得しておき、負荷が高いときやレスポンス低下時などに比較する。
想定と大きく違わないか確認する。

[Load Profile]

[概要]

システムの特性・傾向を見ることができる。
平常時のスナップショットを取得しておき、負荷が高いときやレスポンス低下時などに比較する。

[見方]

Redo size: 測定期間中に生成されたREDOログ量(byte)。
Hard parses: Parses に対して割合が高い場合はバインド変数が使われていないSQLが原因の可能性がある。
Logons: ログオン数。接続ユーザ数と比べて多すぎると問題ある可能性がある。
Rollback per transaction %: 同じ処理を同じ時間実施したレポートを比較して、大きく異なる場合は障害が発生している可能性ある。

[Instance Efficiency Percentages]

[概要]

インスタンス効率の指標
100% に近いほど良い。
目標値(参考)
- 80%以上: %Non-Parse CPU
- 90%以上: Buffer Hit%, In-memory Sort%, Soft Parse%
- 95%以上: Library Hit%, Redo Nowait%, Buffer Nowait%
- 98%以上: Latch Hit%

[見方]

Buffer Hit%: 必要なデータがデータベース・バッファ・キャッシュ上にあった割合。
- 必要なデータがデータベース・バッファ・キャッシュになかった場合、割合が小さくなる。
- データベース・バッファ・キャッシュが小さい場合は大きくする。
- インデックスを使わない非効率なSQLが全表走査を行ったことが原因でデータベース・バッファ・キャッシュからデータがはじきとばされた可能性がある。その場合はインデックスを追加作成するなどする。
Library Hit %: 必要なSQL、PL/SQLがライブラリ・キャッシュにあった割合。
- 割合が小さい場合、SQL実行計画が再利用されていないのが原因。
- バインド変数を使ったSQL文に書き換えることが有効。
Soft Parse %: 全ての解析のうち再利用可能なものの割合。
- 割合が小さい場合、SQL実行計画が再利用されていないのが原因。
- バインド変数を使ったSQL文に書き換えることが有効。
In-Memory Sort%: ソートがメモリ内で行われた割合。
Latch Hit%: 全てのラッチのヒット率。
Parse CPU to Parse Elapsed %: 解析CPU時間/ 解析の合計時間。
- 割合が小さい場合、SQL実行計画が再利用されていないのが原因。
- バインド変数を使ったSQL文に書き換えることが有効。
Execute to Parse%: SQL実行に対し解析が行われなかった割合。
- 割合が小さい場合、SQL実行計画が再利用されていないのが原因。
- バインド変数を使ったSQL文に書き換えることが有効。
%non-parse CPU: 解析以外で使用されたＣＰＵ時間の割合。
- 割合が小さい場合、SQL実行計画が再利用されていないのが原因。
- バインド変数を使ったSQL文に書き換えることが有効。
Buffer Nowait%: バッファに要求を出したときに、即座に使用可能だった割合。
Redo Nowait%: redo logに要求を出したときに、即座に使用可能だった割合。

[Top 5 Timed Events]

[概要]

総待ち時間(Wait Time)の長い待機イベントトップ5が表示される。
サーバ・プロセスとバックグラウンド・プロセスの両方が表示される。

[見方]

%Total Ela Time: イベントの待機時間 / 合計処理時間
CPU time が上位なほどリソースを有効活用できている。
ただし、CPU time が上位でも下記のような効率の悪いSQLがCPUを過剰に使用している可能性があるので注意。
- 全表走査している。
- 最適なインデックスを選択していない。
- SQL文の再解析が発生している。

[イベントの種類]

db file scattered read
- キャッシュヒット率(buffer hit %)を確認し、メモリに余裕があればサイズを大きくすることを検討する。
- ディスクI/Oがボトルネックになっている可能性があるのでOS統計も確認する。
- v$sysstat の table scans(long tables) *1を確認する。
- SQL ordered by Gets から Buffer Gets が多いSQL文を確認し、SQL文をチューニングする。
db file sequentical read
- SQL ordered by Gets から Buffer Gets が多いSQL文を確認し、SQL文をチューニングする。
buffer busy waits
- 同じブロックに対して同時にアクセス要求があった場合、後から要求したプロセスは瞬間的に待ちになる。
- Segments by Buffer Busy Waits からホットになっているオブジェクトを特定する。
- セグメントヘッダの空きリスト競合が発生している場合は、ASSM(自動セグメント領域管理)を利用する。
- 昇順インデックスが原因の場合、逆キー索引やパーティションなどでホットブロックを分散させる。
- PCTFREE を調節して1ブロックに格納する行数を減らす。
- 手動UNDO管理を使用していて、UNDOヘッダ・UNDOブロックがホットな場合は自動UNDO管理に変更する。
free buffer waits
- サーバ・プロセスがバッファの空き領域を待つステータス。
- File IO Stats から書込みが多いデータファイルを確認する。
- OS統計から書込み時間が妥当かを確認する。遅い場合はI/Oのチューニングが必要。
- キャッシュヒット率(buffer hit %)が低い場合は、データベース・バッファ・キャッシュを大きくすることを検討する。
- チェックポイントの頻度を増やす。
- DBWRの数がデータベース・バッファ・キャッシュのサイズの割には少ない場合は増やす。
db file paralell write
- File IO Stats から書込みが多いデータファイルを確認する。
- OS統計から書込み時間が妥当かを確認する。遅い場合はI/Oのチューニングが必要。
log file sync / log file parallel write / log buffer space
- commit や rollback に伴う LGWR の書込み遅延が原因
- OS統計から書込み時間が妥当かを確認する。遅い場合はI/Oのチューニングが必要。
- バッチ処理などで commit のタイミングを変更できる場合は変更する。待機時間が短く回数が多い場合は、commit 頻度をさげる。
- log buffer space の場合は、log buffer サイズが適切でない可能性がある。
enq: ST - contention
- 動的な領域割り当てが主な原因。
- ローカル管理表領域を使用する。
enq: HW - contention
- セグメントの HWM を越える領域の割り当てが原因。
- エクステントの手動割り当て。
enq: SQ - contention
- 順序を生成する頻度が高いことが原因。
- 順序のキャッシュ数を増やす。
enq: TM - contention
- 制約される列が索引付けされない場合の外部キー制約に関係している傾向がある。
- 外部キー列を索引付けする。
enq: TX - row lock contention (mode: 6)
- 行レベルロック。
- アプリケーションの変更。
enq: TX - allocate ITL entry (mode: 4)
- ITL スロットの待機。
- INITTRANS、MAXTRANS を変更し ITL の個数を増やす。
enq: TX - index contention (mode: 4)
- 索引ブロック分割待ち。
- 逆キー索引やパーティションなどで分割の影響を受けるセッションを減らす。
enq: TX - row lock contention (mode: 4)
- ビットマップ索引の更新待ち。
- 更新頻度が高い表にビットマップ索引を使用しない。
Latch Free
- CPUリソースが不足していないか確認する。
- Latch Activity からどの Latch で待機が多いか確認する。
  - latch: cache buffer chains
    - ホットブロックが存在することが原因。
    - 採番表などの存在→順序を使用する。
    - 索引リーフブロックの競合→logical reads が多く索引のサイズが小さいものがあるか調べる。
  - latch: cache buffers lru chai
    - 多くの全表検索、誤った索引の使用、DBWRの遅延などが原因
- latch: library cache
  - 過度な解析が原因。
    - 共有されていないSQL。
    - バインド変数を使用していない。
    - 不必要な解析コールの発行。
  - 共有プールサイズが小さい。
  - Library Hit% など共有プール関連のインスタンス効率を確認。
  - 共有されるようにアプリケーションを変更または cusor_sharing を force に設定。
  - session_cached_cursor を増やし soft parse を減らす。

[SQL ordered by ...]

[概要]

上位のSQLについて、sprepsql.sql でSQL詳細レポートを生成する。
レベル6以上でスナップショットを取得している必要がある。

[参考]
http://www.oracle.co.jp/iSeminars/070123_1600/doc/Statspack_Tips.pdf
http://perfopy.ashisuto.co.jp/samples/atmarkit/atmarkit_part_shorttime5.html

*1:v$sysstat の table scans (long tables) と table scans (short tables) ってなんだ？ - ablog参照。