SQLチューニング対象選定方法 の第11回目は（Case別）SQLチューニング対象選定方法の5回目 です。
「Case5.ASHを活用した性能改善対象選定」について解説していきます。

それでは早速スタートしましょう！！

2.3.5 Case5.ASHを活用した性能改善対象選定

■　ターゲット抽出スクリプト

V$ACTIVE_SESSION_HISTORY （以下ASH）ビュー は Active Sessionのデータを秒単位でメモリ内に保存しています。
ASHビュー は最も最近発生したWait Event情報、各Active Sessionの活動情報、特定の区間で同じSQLがどれだけ
実行されたかなどの情報を提供します。
これを活用することができれば、最近の時点で発生した性能問題に対する正確な原因分析が可能です。

もし希望している過去時点のデータが存在しない場合には、DBA_HIST_ACTIVE_SESSION_HISTORYビュー に定期的に
保存されているものを照会すれば良いのです。
ASHビューで照会することができるデータは［ チューニング対象選択のための情報及び活用ツール ］を参照してください。

ASHビューを活用した色んな方法がありますが、簡単な活用スクリプトをいくつか紹介します。

●　最近最も多く実行されたSQLと実行シェア（実行回数）

SELECT sql_id 、
       COUNT( * ) 、
       COUNT( * ) *100/sum( COUNT( * ) ) over( ) ratio
FROM v$active_session_history
WHERE sample_time >= to_date(:from_time,'yyyymmdd hh24miss')
AND sample_time < to_date(:to_time,'yyyymmdd hh24miss')
GROUP BY sql_id
ORDER BY COUNT( * ) DESC ；

特定のSessionが最も多く実行したSQLと実行シェア（実行回数）

SELECT sql_id 、
       COUNT( * ) 、
       COUNT( * ) *100/sum( COUNT( * ) ) over( ) ratio
FROM v$active_session_history
WHERE sample_time >= to_date(:from_time,'yyyymmdd hh24miss')
AND sample_time < to_date(:to_time,'yyyymmdd hh24miss')
AND session_id = :b1
GROUP BY sql_id
ORDER BY COUNT( * ) DESC ；


特定区間イベント別待機時間

SELECT NVL(a.event, 'ON CPU') AS イベント、
       COUNT(*) AS total_wait_time
FROM v$active_session_history a
WHERE sample_time >= to_date(:from_time,'yyyymmdd hh24miss')
AND sample_time < to_date(:to_time,'yyyymmdd hh24miss')
GROUP BY a.event
ORDER BY total_wait_time DESC；


特定区間のCPU占有率順 - TOP SQL

SELECT ash.sql_id 、
       SUM( decode( ash.session_state , 'ON CPU' , 1 , 0 ) ) "CPU" 、
       SUM( decode( ash.session_state , 'WAITING' , 1 , 0 ) ) - SUM( decode( ash.session_state , 'WAITING' , decode( en.wait_class , 'User I/O' , 1 , 0 ) , 0 ) ) "WAIT" 、
       SUM( decode( ash.session_state , 'WAITING' , decode( en.wait_class , 'User I/O' , 1 , 0 ) , 0 ) ) "IO" 、
       SUM( decode( ash.session_state , 'ON CPU' , 1 , 1 ) ) "TOTAL"
FROM v$active_session_history ash 、
       v$event_name en
WHERE sql_id IS NOT NULL
AND en.event#=ash.event#。
AND ash.sample_time >= to_date(:from_time,'yyyymmdd hh24miss')
AND ash.sample_time < to_date(:to_time,'yyyymmdd hh24miss')
GROUP BY sql_id
ORDER BY SUM( decode( session_state , 'ON CPU' , 1 , 1 ) ) DESC；


特定区間CPU占有率順 - TOP Session のページです

SELECT ash.session_id 、
       ash.session_serial# 、
       ash.user_id 、
       ash.program 、
       SUM( decode( ash.session_state , 'ON CPU' , 1 , 0 ) ) "CPU" 、
       SUM( decode( ash.session_state , 'WAITING' , 1 , 0 ) ) - SUM( decode( ash.session_state , 'WAITING' , decode( en.wait_class , 'User I/O' , 1 , 0 ) , 0 ) ) "WAITING" 、
       SUM( decode( ash.session_state , 'WAITING' , decode( en.wait_class , 'User I/O' , 1 , 0 ) , 0 ) ) "IO" 、
       SUM( decode( session_state , 'ON CPU' , 1 , 1 ) ) "TOTAL"
FROM v$active_session_history ash 、
       v$event_name en
WHERE en.event# = ash.event# を指定します。
AND ash.sample_time >= to_date(:from_time,'yyyymmdd hh24miss')
AND ash.sample_time < to_date(:to_time,'yyyymmdd hh24miss')
GROUP BY session_id 、
       user_id 、
       session_serial# 、
       プログラム
ORDER BY SUM( decode( session_state , 'ON CPU' , 1 , 1 ) ) DESC；


特定区間実行履歴

SELECT ash.sample_time TIME 、
       ash.session_id 、
       ash.session_serial# 、
       ash.user_id 、
       ash.program 、
       ash.module 、
       ash.client_id 、
       ash.machine 、
       ash.session_state 、
       ash.event 、
       ash.sql_id 、
       ash.blocking_session 、
       ash.current_obj# 、
       ash.current_file# 、
       ash.pga_allocated 、
       ash.temp_space_allocated
FROM v$active_session_history ash
WHERE ash.sample_time >= TO_DATE( :from_time , 'yyyymmdd hh24miss' )
AND ash.sample_time < TO_DATE( :to_time , 'yyyymmdd hh24miss' )
ORDER BY ash.sample_time DESC；

活用例

DB管理者が特定のDBサーバーに発生した性能問題をすぐに検出することができず、時間が経過してから検出したとします。
このケースで正確な原因分析をするためには、ASHビューを照会するスクリプトで該当時間帯の性能情報を簡単に把握することができます。

下記の［ 表2-7 ］の結果は、特定の状況を想定したスクリプトの実行結果です。

該当時間帯のActive Sessionの活動情報を見ると、同じSQLの実行とそのセッションで Latch: Cache Buffer Chains の
待機イベントを待機する現象が一番最初に目立ちます。
同じ時間帯にASHを活用してCPU使用率Top 3 SQLと同じ時間帯に発生した待ち時間が多く発生したTop 3 Wait Eventの
スクリプトを実行した結果をグラフで表示すると下記のようになります。

［ 表2-7 ］と［ 図2-8 ］を見ると、特定のSQLがLatch: Cache Buffers Chainsの待機が過度に発生し、CPU使用率が
その時点で急増したものと分析することができます。

Note. 　 バッファキャッシュを使うためにハッシュチェーンを探索したり変更しようとするプロセスは、必ずその チェーンを管理する cache buffers chains ラッチを取得しなければなりません。 cache buffers chains ラッチを取得する過程において競合が発生した場合には、latch: cache buffers chains イベントを待機することになります。 Oracle 9i以降では、読み取り専用の目的でチェーンを探索する場合には、cache buffers chainsラッチを Sharedモードで共有することができ、競合を減らすのに役立ちます。 Shared モードの cache buffers chains ラッチについて一つ注意点があります。 もし、cache buffers chainsラッチを共有することができれば、理論的には同時Selectによるcache buffers chains ラッチの競合は 全く発生しないはずです。 しかし、実際にテストしてみると、同時Selectの場合でもラッチ競合は発生します。 その理由はBuffer Lockに関係しています。 読み取り作業のためにSharedモードでラッチを獲得した場合、実際のバッファを読む過程でBuffer LockをShared モードで獲得しなければならないが、この過程においてバッファヘッダの情報を一部変更しなければなりません。 従って、Buffer Lockを獲得している間はラッチをExclusiveモードに変更しなければならず、Buffer Lockを解除している間も ラッチをExclusiveに獲得しなければなりません。 この過程で競合が発生し、このため、latch: cache buffers chainsイベントを待機することになります。 Latch: cache buffers chains 競合が発生する代表的なケースは以下の通りです。 – 非効率的なSQL – ホットブロック

今までSQLチューニング対象を選定する方法について説明しました。
もう一度強調しますが、性能問題を扱う上で、その原因を引き起こした対象を探す作業の存在感はかなり大きいです。
性能問題はいつ、どのように発生するか誰も予測することができず、どんなに鋭敏で徹底的に管理していたとしても、
この問題から自由であると言える人はいないでしょう。

しかし、性能改善対象選択の重要性と活用するツール、そして代表的に使用できる5つのケースに対するスクリプトや
活用事例などを理解すれば、同じ性能問題が頻繁に発生する様な最悪の状況は十分に回避することができるのです。

Databaseが担当する業務や性格別に発生する可能性がある性能問題は、本テーマで紹介した内容のカテゴリーより
もっと多様な形で現れることがあります。
しかし、分析ツールが持つデータの性質をよく理解していれば、各パフォーマンス問題に適した対象を抽出することが
できるので、正しい解決方法を見つける近道となるものと私たちは確信しています。

SQLチューニングブログ 2nd Season（第11回）　終

次回ブログテーマ

第3章　SQL実行情報の分析及び活用方法（1回目）

※　第3章は全3回シリーズです

■　データベース運用のことなら日本エクセムにお任せください　■

当社の最新情報はSNSでも配信中（フォローお願いいたします）

掲載内容についてのお問い合わせは

私たちは、日本のITインフラにおける
プロジェクト運営～システム運用 の安定化と効率化を推進します。

日本エクセム株式会社
営業推進部 まで
✉　sales@ex-em.co.jp