ablog

不器用で落着きのない技術者のメモ

シンプルでシステマチックな Oracle Database 性能分析 with AWR & OS Watcher

JPOUG Advent Calendar 2016 の5日目のエントリーです。
昨日は id:knopp さんの Oracle RAC on Docker - KNOPP’s blog でした。


2012年から始った JPOUG Advent Calendar も今年で 6 年目になりました。
今年は私が AWRレポート*1と OS Watcher を使って Oracle Database の性能分析をどのようにしているかを紹介します。
このエントリが抽象的で分かりにくいと感じる方は 運用ヘルスチェックでトラブルを予防しよう！ を見てみてください。

追記(2017/03/05): スライド作ったのでリンクしました

まとめ

  • パフォーマンス分析の3軸
    • ベースライン
    • 時間ベース
    • 3-Circle Analysis
  • パフォーマンスチューニングの3原則*2
    • 仕事量を減らす
    • 並列化
    • 高速化

3つのポイント

ベースラインと比較する
  • 健康診断の血圧や心拍数に個人差があるように、データベースのパフォーマンスはシステム特性によって差があるためベースライン(平常時の基礎値)をとります。

P.12

時間単価ベース分析
  • 「単価 = DB Time / 仕事量(SQL実行回数など)」の式でベースラインと比較します。
  • DB Time はプロジェクトでいう総工数、ベースラインと比較して増えた場合はシンプルに仕事量が増えたか、単価が高くなったかのどちらかになります。
    • 業務量が増えた場合は、CPUなどのリソース使用量を確認し、必要に応じてリソース増強を検討します。
      • 例)日銀の黒田砲でオンライン証券システムの取引量が急増した。
    • 単価が高くなっている場合は何らかの異常がある可能性があるので、深堀調査します。
      • 例)db file sequential/scattered read などの平均が 1ms 程度から 10ms 超に増加、iostat の svctm が 1ms から 10ms 超に増加、原因はストレージキャッシュのバッテリー切れだった。

Oracle Performance Firefighting P.30

3-Circle Analysis
  • OS、DBインスタンス、高負荷SQLの3つの観点で分析することで、誤った分析を防ぎます*3。
    • 例えば、OSレベルでCPUなどのリソース使用量はどれだけか、DBはそのうち何割を使用しているか*4、さらに上位のSQLが何割を占めているか。

P.5

分析手順

定常的な情報収集

分析レポート目次

3-Circle Analysis をそのまま目次構成に使います。

  1. はじめに
    1. 本文書の目的
    2. 分析アプローチ
    3. 分析範囲
      1. 分析対象データベース … 対象DBのハードウェア、ソフトウェア構成、バージョンなど
      2. 分析情報 … AWRレポート、OS Watcher などと対象期間、取得頻度など
    4. その他
      1. 参考情報 … My Oracle Support など
  2. 分析結果サマリ
    1. 全体サマリ
      1. 分析結果
        1. OS分析結果
        2. DBインスタンス分析結果
        3. SQL分析結果
      2. 要改善項目
      3. 要経過観察項目
  3. 分析結果詳細
    1. OS分析結果
    2. DBインスタンス分析結果
    3. SQL分析結果

分析項目例

"チェック内容"はあくまで一例です。監視閾値などに合わせて変更します。

OS*5
  • CPU
項目 チェック内容 分析対象データ 分析対象項目
CPU使用率 100%に達している時間帯がないか
sys が 40% を超えていないか		 OSWatcher [vmstat]-[usr + sys + st]
ランキュー CPU数*2を超えていないか OSWatcher [vmstat]-[r]
CPU別使用率 特定のCPUで100%で張り付いている時間帯がないか
sys が 40% を超えていないか		 OSWatcher [mpstat]-[user + system + steal + intr + soft]
  • メモリ
項目 チェック内容 分析対象データ 分析対象項目
メモリ使用率 実質使用量が80%を超えていないか OSWatcher [meminfo]-[(MemTotal-(MemFree+Active(file)+Inactive(file)))/MemTotal]
メモリ使用率内訳 ページテーブル、スラブキャッシュなどのサイズが想定外に大きくないか OSWatcher [meminfo]-[PageTables,Slab]
ページング発生状況 ページイン／ページアウトが発生していないか OSWatcher [vmstat]-[si, so]
スワップ領域使用率 スワップが発生していないか OSWatcher [vmstat]-[swpd]
  • ストレージ*6
項目 チェック内容 分析対象データ 分析対象項目
I/Oレスポンス 20ms を上回っていないか OSWatcher [iostat]-[await]
I/Oサービスタイム 10ms を上回っていないか OSWatcher [iostat]-[svctm]
ディスクビジー率 80%を上回っていないか OSWatcher [iostat]-[%util]
IOPS カタログスペックを超えていないか*7 OSWatcher [iostat]-[r/s. w/s]
DBインスタンス
  • 仕事量
項目 チェック内容 分析対象データ 分析対象項目
SQL実行回数 なし*8 AWR Report [Load Profile]-[Executes]
トランザクション数 なし AWR Report [Load Profile]-[Transactions]
ログオンユーザー数 なし AWR Report [Key Instance Activity Stats]-[logons cumulative]
REDO生成量 なし AWR Report [Load Profile]-[Redo size (bytes)]
ハードパース回数 なし AWR Report [Load Profile]-[Hard parses]
物理読込量 なし AWR Report [Load Profile]-[Physical read ]
論理読込量 なし AWR Report [Load Profile]-[Logical read]
インターコネクト通信量 なし AWR Report [Load Profile]-[Global Cache blocks received] / [Global Cache blocks served]
  • DB処理時間
項目 チェック内容 分析対象データ 分析対象項目
アクティブセッション数 アクティブセッション数がCPU_COUNTを超えていないか AWR Report [Wait Classes by Total Wait Time]-[Avg Active Sessions]
時間モデル統計 なし AWR Report [Time Model Statistics]
待機クラス なし AWR Report [Foreground Wait Class]
Top N 待機イベント enqueue、latch、mutex等の割合が高い場合は深堀調査を行う
平均待機時間が長い待機イベントがないか		 AWR Report [Top 10 Foreground Events by Total Wait Time]
I/Oレスポンス db file sequential read
db file scattered read
direct path read
log file sync
log file parallel write
などが 20ms を上回っていないか		 AWR Report [Foreground Wait Events]
[Wait Event Histogra]
[Wait Event Histogram Detai]
キャッシュフュージョン平均ブロック転送時間 10ms を上回っていないか AWR Report [Global Cache and Enqueue Services - Workload Characteristics]-[Avg global cache cr/current block receive time (ms)]
  • メモリ使用状況
項目 チェック内容 分析対象データ 分析対象項目
共有プール内訳 特定コンポーネントが大きくないか AWR Report [SGA Breakdown Difference]
ラージプール内訳 特定コンポーネントが大きくないか AWR Report [SGA Breakdown Difference]
共有プール・ラージプールの immediate 拡張有無 immediate 拡張が発生していないか AWR Report [Memory Dynamic Components]
バッファキャッシュヒット率 なし AWR Report [Instance Efficiency Percentages]
RACバッファキャッシュヒット率 なし AWR Report [Global Cache Efficiency Percentages (Target local+remote 100%) ]
ライブラリキャッシュヒット率 なし AWR Report [Instance Efficiency Percentages]
BufferPoolAdvisory バッファキャッシュ拡張によりI/O量削減の可能性が高いか AWR Report [Buffer Pool Advisory]
PGA使用量 PGA_AGGREGATE_TARGET を超えていないか AWR Report [PGA Aggr Target Stats]
PGA Advisory PGA拡張によるSQL実行時間短縮の可能性が高いか AWR Report [PGA Memory Advisory]
高負荷SQL
項目 チェック内容 分析対象データ 分析対象項目
実行回数の多いSQL(総計) ベースラインおよび他の期間との比較 AWR Report [SQL ordered by Executions]
物理読込量の多いSQL(総計/1回当り) 時系列で右肩上がりで増えているSQLがないか AWR Report [SQL ordered by Reads]
論理読込量の多いSQL(総計/1回当り) 時系列で右肩上がりで増えているSQLがないか AWR Report [SQL ordered by Gets]
CPU時間の長いSQL(総計/1回当り) 時系列で右肩上がりで増えているSQLがないか AWR Report [SQL ordered by CPU Time]
クラスタ待機時間の長いSQL(総計/1回当り) 時系列で右肩上がりで増えているSQLがないか AWR Report [SQL ordered by Cluster Wait Time]
共有メモリ使用量の多いSQL(総計) 共有メモリ使用量が 100MBを超えているSQLがないか AWR Report [SQL ordered by Sharable Memory]
Version Count の多いSQL(総計) 時系列で右肩上がりで増えているSQLがないか AWR Report [SQL ordered by Version Count]
実行時間の長いSQL(総計/1回当り) elapsed が undo_retention を超えてい るSQLがないか(1回当たり) AWR Report [SQL ordered by Elapsed Time]

私が見るポイントを一部紹介

DBが遅延しているか？
  • システムが遅延していても DB Time が短い場合は、DB は遅延していません。アプリケーションやネットワークなどDBより前のレイヤーで遅延しています。
DBが使った時間の内訳は？
  • DB Time のうち何の割合が高いか。Wait Time か DB CPU か、Wait Time のうちどの Wait Class/Event の割合が高いか。特定のSQLが占める割合が高いか。
  • 例えば、8CPUのマシンで、CPU使用率が50% 、AWRスナップショット間隔が1時間で、DB CPUが14400秒(4時間)、SQL ordered by CPU Time の1位が1000秒(3時間)、SQL ordered by Gets も1位で、チューニングでアクセスブロック数が大幅にに減り、CPU時間がほぼ0になった場合、CPU使用率は37%ほど削減できることになる。
DB Time と DB CPU + Wait Time の比較

補足

DB Time について
  • DB Time はスナップショット間隔より長くなることがあります。例えば、AWRのスナップショットを1時間間隔で取得していて、2セッションがずっとアクティブで実行されていた場合、1時間のスナップショットでも DB Time は2時間になります。
AWRについて
  • 可能な場合は DocID 1746171.1 の手順でリポジトリごと入手し、手元のDBにインポートし、深堀する際は DBA_HIST_* を抽出して分析できるようにします。
  • AWR の R は "Report" ではなく "Repository" の略です。AWRレポートを分析し、深堀調査を行う場合は、DBA_HIST_ASH、DBA_HIST_SQLSTAT、DBA_HIST_SQL_PLAN などのデータを分析します。
  • 12.1 の時点で SQL Monitor の元表(WRP$)はエクスポートしたダンプに含まれない点は要注意。
ベースラインなしで評価できる項目
  • ベースラインがなくても絶対的な評価ができるものは評価します。例えば、平均I/Oレイテンシが 20ms を超えているなど*9。

参考情報など

参考資料
関連
過去の JPOUG Advent Calendar 記事
過去の JPOUG Advent Calendar


明日は Oracle ACE の渡部 亮太さんです。

*1:もしくはStatspackレポート

*2:第四にあらかじめやっておくというのがある。バッチ処理でのデータマート作成、MVIEW や Golden Gate みたいなリアルタイム差分更新など。

*3:例えば、CPU使用率

*4:CPU使用率とCPU時間の換算が必要。スナップショット間隔 *

*5:Linuxを前提としています

*6:以下は HDD を想定しています

*7:RACの場合は、全ノードからの IOPS の合計値になります

*8:初回はベースラインとして使用、2回目以降はベースラインと比較

*9:20msは例で、ストレージの基本性能によって変わります。HDDでも 5〜10ms 程度なので、20ms までのびるとサチっていないか、ストレージに異常がないか確認したほうがよいです。ストレージによってはワークロードが