サンプリングvs.イベント

次のような質問があります。

特定セッションが実行したすべてのSQLの履歴を追跡できる方法はないか

返事は簡単なのです。

SQL Trace(10046 Event)でできます。

でもこの簡単な質問と返事の中に性能データ収集に対する深奥な心理があります。

直接性能データを収集して分析してみたことがある方なら自分も分からず必ず次の二つの方法を持って悩みするようになります。これらはオラクルに構わずにデータを収集するのにおいていつも適用される普遍的な二つの方法です。

• イベント(Event)方式: 10046イベントが代表的な方法です。すなわち、特定なイベントが発生する時それを取り込んで性能データを収集する方式であります。
  
• サンプリング(Sampling)方式: MaxgaugeやASHが代表的な方法です。秒あたり、または分あたり幾度ずつデータを直接キャプチャする方式であります。
  

イベント方式はオラクルが内部的にあらかじめ定義しておいたイベントたちに対してだけ使用可能です。例えば10046(SQLの実行)、10053(Optimizationの実行)、10032(Sortの実行)などオラクルが決めておいた範囲内でだけ使用可能です。イベント方式のメリットはサンプリング方式と違って収集の間違いはほとんどないということです。すなわちすべてのデータが収集できます。

サンプリング方式はどんなデータも願う周期で収集できるというメリットがあります。オラクルでならDynamic Performance Viewが主対象となります。ただし、収集周期によって収集の間違いかとても大きくなれるというデメリットがあります。

次の質問をイベント方式ではなくてサンプリング方式で具現できるでしょうか。

特定セッションが実行したすべてのSQLの履歴を追跡できる方法はないか

1. 次のようにセッションA(SID=159)で10,000個のSQL文章を連続的に修行します。

declare
 v_cursor sys_refcursor;
 v_value  number;
begin
 for r in 1 .. 10000 loop
  open v_cursor for 'select /*+ case_' || r || ' */ 1 from dual';
  fetch v_cursor into v_value;
  close v_cursor;
 end loop;
end;
/

2. セッションBで4,000,000度のサンプリングをCPUを100％活用して修行しながらX$KSUSE(V$SESSIONビューのベーステーブル)テーブルからSQLを追跡します。(ヒントを利用してサンプリングが可能な実行計画を制御するのに注意してください)

select * from 
(
 select
  sql_id,
  (select substr(t.sql_text,1,25) from v$sqlarea t 
     where t.sql_id = a.sql_id) as sql_text,
  hitcnt
 from (
  select /*+ no_merge */
   a.sql_id,
   count(*) as hitcnt
  from (
   select /*+ ordered use_nl(x s) */
     s.ksusepsi as sql_id
   from
    (select /*+ no_merge */ level as r from dual connect by level <= 4000000) x,
    sys.xm$ksuse s
   where s.indx = 154
  ) a
  group by a.sql_id
 ) a
) where sql_text is not null
;

3. 次にその結果をあります。

...
7hbnjb7uhrs2n select /*+ case_8766 */ 1                                 144
6150v08vhwyzs select /*+ case_8767 */ 1                                 169
7pxf03wwjqzf3 select /*+ case_8773 */ 1                                 349
ggq5ruaj6b9zf select /*+ case_8781 */ 1                                 343
abg6fj0kntu55 select /*+ case_8782 */ 1                                 304
6akym930shhgn select /*+ case_8786 */ 1                                 163
044rsut971xtb select /*+ case_8792 */ 1                                1802
8mjjurfg59y40 select /*+ case_8798 */ 1                                 142
4q3vgvnv2chn9 select /*+ case_8822 */ 1                                 172
34ddvz7nc4mrv select /*+ case_8836 */ 1                                1385
58zhgmbwa3r6z select /*+ case_8860 */ 1                                1410
g3xjqmh2fmp18 select /*+ case_8919 */ 1                                 185
7fjm8yu32sfyv select /*+ case_8929 */ 1                                 233
6wkawk6brmx41 select /*+ case_8283 */ 1                                 197
4zp1k8mr17t70 select /*+ case_8288 */ 1                                1040
5pha5zp0catjt select /*+ case_8289 */ 1                                 303
dswcqaxvy7pr9 select /*+ case_8307 */ 1                                 227
39fjqykryfvn8 select /*+ case_8315 */ 1                                 184
3xk1sjqdd49td select /*+ case_8321 */ 1                                 123
...

理論的には一つのSQLが4,000,000/10,000 = 400番ぐらいサンプリングがしてなるのが理想的です。でも実際のサンプリング回数はすごく偏差が大きくて間違いも持っています。

こんな誤差はサンプリング方式では仕方ないのです。例えばASH(Active Session History)は1秒で1番ずつActive Sessionを収集します。1秒という単位はセッションを収集するのには充分かも知れませんが、SQLの履歴を収集するには大きすぎる値です。ASHのサンプリングの正確性は_ash_sampling_interval, _ash_size, _ash_sample_allなどのパラメータで調整できますが本質的な誤差は避けられません。

Exemで提供しているMaxgaugeというツールは普通1秒あたり10度～100度ぐらいActive SessionたちのSQL文章をサンプリング方式で収集します。このぐらいならすごく驚くべき数値みたいですが、実際のシステムで運営してみたら考えより大きい誤差が生じます。上の例でCPUを100％占有しながらもサンプリングをしても誤差が大きいという事実を考え合わせば充分に予想できる結果ですが…

自分だけの方法で性能データを収集して分析しようとする欲を持っている方々なら「サンプリングvs。イベント」というこの命題にいつも注意しなければなりません。