Oracle SQLチューニング Season2（第11回）第2章　SQLチューニング対象選定方法（8/8）

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；

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
– ホットブロック