OWI(Oracle Wait Interface)の概要

Size: px

Start display at page:

Download "OWI(Oracle Wait Interface)の概要"

しおりつねざき
5 years ago
Views:

1 日本エクセム株式会社 1

2 目次 OWI とは? OWI ベース診断 / 分析 Oracle アーキテクチャーと OWI OWI の構成要素システム性能管理の必要性 2

3 QUIZ 以下はある期間の STATSPACK レポートの一部ですデータベース処理で遅延が発生しているでしょうか性能低下現象が発生している場合どのように診断 / 分析を行いどのような改善ポイントを提案すべきでしょうか 3

4 プロセスの状態と OWI のサイクルで処理する 1 CPU を必要としない状態 2 作業要求が入って CPU を使い始める 3 CPU を使って作業中 4 次の処理を進めるため必要なリソースを要求 5 CPU を使って処理を進めたいが何らかの理由でリソースの獲得待ち状態一部の待機はアイドル ( スリープ ) 状態になる 6 リソースが獲得できて次の処理を進めるため CPU を使い始める 7 アイドル ( スリープ ) 状態になる 4

5 プロセスの状態と OWI SQL> select event, p1, p2, p3, wait_time time from v$session where sid = 146; EVENT P1 P2 P3 WAIT_TIME db file scattered read SQL> select event#, name, parameter1, parameter2, parameter3, wait_class 2 from v$event_name where name = 'db file scattered read'; EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS db file scattered read file# block# blocks User I/O 5

6 OWI とは? Oracle Wait Interface = データベース内部処理の待機時間を基にしたパフォーマンスボトルネック分析のための新メソッド Oracle DB のパフォーマンスを Oracle が吐き出す待機イベントを中心に管理しよう!!! データベース内の各処理工程にセットされたタイマーを元に各ステップでのリソース獲得の待ち時間に着目しレスポンスタイムを定義処理時間 ( 応答時間 ) = サービス時間 (CPU 使用時間 )+ 待機時間レスポンスタイムの最小化 = 待ち時間の最小化 6

7 OWI の誕生 Oracle Wait Interface の歴史 Oracle7から導入 Oracleが時間ベースの性能管理を開始 Oracle 開発者がこつこつと待機イベント ( 時間計測用のイベント ) を増やす 2000 年 11 月のOracle magazine(us) にてWait Event-based チューニング手法として紹介される Oracle11gでは 961 個もの待機イベントがサポートされるなぜひろまらなかったのか? 当初はマニュアルにも載らなかった Oracleのコア開発者での内部で利用されていた Oracle7 Oracle8 ではイベント数が少なかった以前はハードウェアに負荷がかかりすぎ実用的ではなかった文献がほとんどなかった 7

8 比率ベース診断 / 分析 Ratio-Based: システムリソースの使用率などの統計比率を基にしたチューニング方法バッファキャッシュヒット率が90% 以上に保っているか? データディクショナリーのキャッシュヒットミスレートが 10% 以下になっているか? これらの指標を外れると? システムのどこかに問題があるかもしれないしかし個別セッションのパフォーマンスのンスの良し悪しを示すものではない人的リソースの消費時間の消費 Buffer Cache Hit Ratio は? とりあえずバッファキャッシュを管理コストの増大サービスレベルの低下トラブル増やしてみよう管理者ビジネスチャンスの損失しまったぜんぜん改善されないどうしよう!!! 8

9 OWI ベース診断 / 分析各処理の待機時間よりプロセスのボトルネック現象に焦点を合わせたチューニング Oracle 内部の処理待ち時間 ( 応答時間 ) に基づくチューニング効果が絶対的な数値として予測計測できるドリルダウンを行う形でボトルネックを発見できる待機情報ボトルネックとなるセッションボトルネックとなる SQL やリソースレスポンスタイムに注力しチューニングを行うことができる ResponseTime = ServiceTime + WaitTime = CPU used by this session + ΣTIME_WAITED = CPU work time + parse CPU time + recursive CPU time + ΣTIME_WAITED = CPU 実働時間 + 構文解析 CPU 利用 + 処理制御 CPU 時間 + 実行待ち時間チューニングポイント!! 9

自宅から会社に着くまでの所要時間を分析してみました走行時間 : 15 分信号による待機 : 10 分道路幅の減少の渋滞待機 : 5 分混雑時間帯による渋滞待機 : 30

10 OWI ベース.vs. 比率ベースイメージ課題車で自宅から会社までの 1 時間所要出社時間を短縮する方法は? 比率ベース改善案現状の調査より A 地域での平均スピードは40km/hでした道路を拡充して平均スピードを60km/h 以上に上げることを推奨します OWI ベース改善案自宅から会社に着くまでの所要時間を分析してみました走行時間 : 15 分信号による待機 : 10 分道路幅の減少の渋滞待機 : 5 分混雑時間帯による渋滞待機 : 30 分上記分析から混雑時間帯による渋滞待機をもっとも改善すべきで信号による待機の回避方法も検討すべきですですので 1 出社時間帯の変更及び 2 信号の少ない他の道路へのルート変更を推奨します最大 40 分の出社時間を節約できます 10

11 OWI ベース.vs. 比率ベース区分比率ベース OWI ベースベースデータシステム全般の統計比率に基づく各セッション (SQL) で発生する待機イベント ( 処理の応答時間 ) に基づくチューニングポイント ( ボトルネック箇所 ) 初期化パラメータ初期化パラメータ表領域のパラメータテーブルのパラメータセグメントの再編成セグメントのパーティション化運用時間帯 AP(SQL) 索引の調整... 兆候検知 ( 予兆監視 ) 性能改善効果の定量化 ( 時間ベース性能管理 ) 不不可可セッション診断 / 分析不可 SQL 診断 / 分析不可時系列分析不適適 11

12 AP 処理の流れ : ボトルネック箇所 SGA client 接続 SERVER Process Shared Pool Buffer Cache バッファキャッシュ Log Buffer a b REDO PGA Memory ロック a b Session info Sort Area メモリ SQL 解析 8 9 Hash Area I/O DBWR LGWR コミット 12

13 Oracle アーキテクチャーと性能統計 13

14 SELECT 処理と待機イベント 14

15 UPDATE 処理と待機イベント 15

16 OWI の構成要素すべてのセッションは処理を行うためにはリソースが必要であり各セッションがCPUを使用していないときは何かしらの待ちが発生している状態となるデータファイルからのデータブロック読み書きでのタブロック読み書きでの I/O 待ちメモリの獲得待ち他処理との連携待ちデータベース処理にて発生した待機イベントがオラクルの動的ビューへ格納される V$EVENT_NAME V$SESSION_WAIT V$SESSION_EVENT V$SYSTEM_EVENTEVENT... V$SESSION_WAIT データベースでの様々な処理での待機イベントを格納 16

17 OWI の構成要素項目定義備考 V$EVENT_NAME インスタンスで定義している待機イベントの情報イベントの数正確な名称待機クラスの参照 V$SYSTEM_EVNET インスタンスの起動後全セッションで発生した待機イベントの累計統計値 ( インスタンス単位 ) インスタンスの全般的な安定度を判断デルタ情報を算出して特定時間帯の状態を診断 / 分析ができる Staspack 機能 V$SESSION_EVENT 現在接続されている全セッションについての各セッション別待機イベントの累計統計値接続中のセッションについて各イベント別統計情報の把握ができる V$SESSION_WAIT 各セッションが現在待機しているイベントリソースの詳細情報を参照時のリアルタイムで提供, 累積データではなくリアルタイムのデータであるため短い間隔のクエリーで繰り返し参照することで待機イベントの状況の把握に有効 V$SYSTEM_WAIT_CLASS 10g, インスタンスの起動後発生した待機クラスの累積情報待機イベントのクラス単位でインスタンスの安定度の把握に有効 V$SESSION_WAIT_CLASS 10g, 現在接続されている全セッションについてセッションレベルの待機クラスの累積情報待機イベントのクラス単位でセッションの待機状況の把握に有効 V$SESSION_WAIT_HISTORY 10g, 直近の 10 個の待機イベントの情報直近のセッションの履歴情報の把握に有効 V$EVENT_HISTOGRAM 10g, インスタンスの起動後の待機イベントのヒストグラム提供各バケット ( 待機時間の区間 ) 別の待機イベントの把握に適切 V$ACTIVE_SESSION_HISTORY 10g, アクティブセッションの履歴の情報 1 秒単位でのセッションのスナップショットを保存しているため各セッションの待機イベントなどの情報の追跡に適切 Trace Event SQL トレース待機イベントバインド変数などの履歴情報を含め途切れの無い情報の把握や SQL/ 待機イベント / バイン情報を提供ド変数の連携分析に適切 17

18 OWI の構成要素 18

19 OWI の構成要素 :V$SYSTEM_EVENT 19

20 OWI の構成要素 :V$SESSION_EVENT 20

21 OWI の構成要素 :V$SESSION_WAIT V$SESSION Oracle Database リファレンス 10g リリース 2(10.2) より 21

22 OWI の構成要素 :V$SYSTEM_WAIT_CLASS 22

23 OWI の構成要素 :V$SYSTEM_WAIT_CLASS 23

24 OWI の構成要素 :V$SESSION_WAIT_HISTORY 24

25 OWI の構成要素 :V$EVENT_HISTOGRAM 25

26 OWI の構成要素 : 性能統計 26

27 OWI の構成要素 : 性能統計 27

28 OWI の構成要素 :V$ACTIVE_SESSION_HISTORY プロセスの処理に関わるデータを記録し事後に確認できる手法を提供する一連のインタフェースとその診断 / 分析メソッドに発展 28

29 OWI のレファレンス MaxGauge イベントヘルプより 29

30 時間経過による性能低下 30

31 OWI 診断 / 分析プロセスシステム性能低下認識システムレベル分析 : トレンドアラート等診断 / 分析対象の時間帯を特定トップダウンアプローチ概要分析 : アクティブセッション / 滞留 /CPU 詳細領域分析 :I/O メモリーロック上位 SQL... セッション診断 / 分析 SQL 診断 / 分析必要時正常時との比較分析ログ外部情報確認 31

32 QUIZ 解説 enq: SQ - contention イベント概要 SQ ロックは低い値の CACHE 属性を持ったシーケンスを同時に大量に発行した時に発生しますシーケンスは低負荷でユニークな値を取得できることを目的にしているため一定区間の値をメモリにキャッシュしておきますただしディクししショナリの更新が行なわれる際複数のセッションで1 つのセッションだけがシーケンスプールを再度キャッシュできることが保証されているためその際はほかのセッションは enq: SQ - contention イベントで待機することになります診断 / 分析ポイント該当イベントによる待機が上位待機で上がっている場合又はインスタンスレベルで 1 秒 / 秒を超える場合シーケンス値の取得で相当のボトルネックが発生している状況です最初にどのシーケンスでのロックなのかを確認しましょう次に該当シーケンスの CACHE 値を確認しますデフォルトで作成されたシーケンスは 20 のCACHE 属性を持つため 20 回発行されると再度ディクショナリを更新し次の20 個のシーケンスをメモリにキャッシュしなければなりませんトランザクションが同時かつ大量に発生する表のキーとしてシーケンスを採用する場合は CACHE 属性の値が小さすぎるとボトルネックの原因になるので十分大きく設定する必要があります改善策最後に該当シーケンスの利用頻度を考慮し現状の CACHE より大きく調整します 32

33 QUIZ 解説該当シーケンスの CACHE 属性をに変更することで 1831 秒 ( ) (99% 短縮 ) の滞留がなくなりましたまたシーケンスでのボトルネックが解消されて他の待機が新たに上位にランクアップされたが CPU 使用時間に比べて気にするほどの現象ではないと考えられます 33

MaxGauge_診断分析プロセス

MaxGauge_診断分析プロセス Easy Use -1- MaxGauge 診断 / 分析プロセス Easy Use -2- システム性能低下認識システムレベル分析 : トレンドアラート等診断 / 分析対象の時間帯を特定トップダウンアプローチ概要分析 : アクティブセッション / 滞留 /CPU 詳細領域分析 :I/O メモリーロック上位ロック上位 SQL... セッション診断 / 分析 SQL 診断 / 分析