「ティパック」性能問題をトラブルシューティングする２つのフレーム

クイズであります。

1. オラクルで特定のセッション（あるいはシステム）の現在の状態をすぐにわかる一番良い方法はなんでしょうか。 2. オラクルで特定のセッション（あるいはシステム）が何をしているのかを追跡できる一番良い方法は何でしょうか。

性能問題を分析する2つのフレームで上の質問に対する私の答えを代わります。私はオラクル性能問題をトラブルシューティングする全てのツールと技法を次の２つのフレームに分けます。

• スナップショット（Snapshot） - 特定の時点の作業の現在の状態を検索する方法
  
• プロファイル（Profile） - 特定の作業を時間の流れで追跡する方法
  

例をあげていましょうか。

• 10053診断イベント - オプティマイザの作業を時間の順序で追跡するプロファイル機能
  
• V$SESSION_WAIT - セッションの待機状態を検索するスナップショット機能
  
• Heap Dump - 特定のセッションやシステムの現在のメモリー使用程度を検索するスナップショット機能
  
• System State Dump - システムの現在の状態を検索するスナップショット機能
  
• Call Stack(oradebug dump callstack) - 特定のセッションの現在呼び出されているファンクションのコールツリーなのでスナップショットとプロファイルの中間性格
  
• Active Session History - アクティブセッションのリストを秒あたり一度づつサンプリングしたことなのでスナップショットとプロファイルの中間性格
  

スナップショットデータを時間の流れによって全体あるいは一部をサンプリングしたらそれがプロファイルになります。スナップショットは特定の時点の状態をいみするので一番基本的なデータだと言えます。

スナップショットは特定の時点の状態を示しているから、ほとんど大部分差異（Delta）と比較（Diff）を通じてだけ意味を持ちます。AWRレポートをみればスナップショット間の値を差異（Delta）を計算してくれます。差異と比較を通じて直感的にシステムの現在の状態がわかります。

プロファイルは時間の流れによってデータを追跡する方式だから、ほとんど大部分集計と要約を通じてだけ意味を持ちます。10046イベントにより生成されたプロファイルデータをTKPROFレポートというツールを利用して集計して見るのが代表的な例です。

ティパックは上のような簡単明瞭なフレームの上で状況によって適当なスナップショットとプロファイルだけ存在すれば比較と要約を通じて大部分の性能問題をトラブルシューティングできるという考えに基づいています。これからブログを通じてより具体的な事例たちとともにオラクルで使用できる多様な技法たちを論議するようにします。

「ティパック」を紹介します。

ティパックとはオラクル性能トラブルシューティング機能を提供するPL/SQLライブラリーであります。

ティパックウェブページ

これからブログを通じてティパック機能の使用方法を説明するつもりです。

性能改善の絶対的な原則！

オラクル性能問題の関する最高の専門家で平価されているCarry Milsapさんは性能を改善する絶対的な原則を言及したことがあります。

• ある作業の性能を改善する最高の方法はその作業自体をしないということだ。
  

本当に名言の中の名言です。

次に簡単な例があります。

1. これ以上チューニング不可能に見える完璧に最適化された文章です。ほぼ3.4秒がかかりました。

TPACK@ukja1106> declare
  2   v_value number;
  3  begin
  4   for idx in 1 .. 100000 loop
  5    select trunc(idx) into v_value from dual;
  6   end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.42

本当にそうでしょうか。上で話した絶対原則を適用したら？不必要なSELECT ... FROM DUAL分をなくしたら？

TPACK@ukja1106> declare
  2   v_value number;
  3  begin
  4   for idx in 1 .. 100000 loop
  5    v_value := trunc(idx);
  6   end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

0.07秒だけで作業が終わります。

2. DECODE関数はSQL文章内のみで修行可能です。従ってCASE...文に変換すれば同じ効果を享受できます。

TPACK@ukja1106> -- decode
TPACK@ukja1106> declare
  2   v_value varchar2(1);
  3  begin
  4   for idx in 1 .. 100000 loop
  5    select decode(mod(idx,2),0,'A','B') into v_value from dual;
  6   end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.59

TPACK@ukja1106> -- case
TPACK@ukja1106> declare
  2   v_value varchar2(1);
  3  begin
  4   for idx in 1 .. 100000 loop
  5    v_value := case mod(idx,2) when 0 then 'A' else 'B' end;
  6   end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15

3. Oracle 11gからはSequenceの値もSELECT ... FROM DUALを通じなくて直接えることがでいます。でも性能に与える影響はほとんどありませんね。

TPACK@ukja1106> -- SELECT... FROM DUAL
TPACK@ukja1106> declare
  2   v_value number;
  3  begin
  4   for idx in 1 .. 100000 loop
  5    select s1.nextval into v_value from dual;
  6   end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.73

TPACK@ukja1106> -- 直接
TPACK@ukja1106> declare
  2   v_value number;
  3  begin
  4   for idx in 1 .. 100000 loop
  5    v_value := s1.nextval;
  6   end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.64

うん。。。なぜでしょうか。それに対する応えはSQL*Traceを通じて得られます。SQL*Traceを実行してみたら{ v_value := s1.nextval }作業は{ Select S1.NEXTVAL from dual }

もう一度言いますが、性能改善の一番の原則は！

• ある作業の性能を改善する最高の方法はその作業自体をしないということだ。
  

_gby_hash_aggregation_enabledバグ

カスタムーからの問い合わせ：オラクルがどんな状況でもHASH GROUP BYを選択しないが、理由は何?

オラクルバージョンは10gR2（10.2.0.1）であります。

TPACK@ukja1021> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

次のクエリを見れば、一番簡単なGroup By文でもHASH GROUP BYではなくてSORT GROUP BYを使っています。

create table t1
as
select level as c1
from dual
connect by level <= 10;


explain plan for 
select count(*)
from tpack.t1
group by c1;

-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |
|   1 |  SORT GROUP BY     |      |    10 |
|   2 |   TABLE ACCESS FULL| T1   |    10 |
-------------------------------------------

隠しパラメーターである_gby_hash_aggregation_enabledの値がFalseである可能性があるでしょう。確かそうです。

TPACK@ukja1021> col value format a10
TPACK@ukja1021> @para gby_hash
TPACK@ukja1021> set echo off
old   9: and i.ksppinm like '%&1%'
new   9: and i.ksppinm like '%gby_hash%'

NAME                           VALUE                IS_DEFAUL SES_MODIFI
------------------------------ -------------------- --------- ----------
SYS_MODIFI
----------
DESCRIPTION
-------------------------------------------------------------------------

_gby_hash_aggregation_enabled  FALSE                FALSE     true
immediate
enable group-by and aggregation using hash scheme

しかし、_gby_hash_aggregation_enabledパラメーターをTrueに変え、USE_HASH_AGGREGATIONヒントを与えても、かわりなくSORT GROUP BYが選べられます。

alter session set "_gby_hash_aggregation_enabled" = true;

explain plan for 
select /*+ use_hash_aggregation */ count(*)
from t1
group by c1;

-------------------------------------------
| Id  | Operation          | Name | Rows  |
-------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |
|   1 |  SORT GROUP BY     |      |    10 |
|   2 |   TABLE ACCESS FULL| T1   |    10 |
-------------------------------------------

これは正常的な状況とは言えません。MOSで検索してみたら、完璧に一致するバグが見つかりました。

Bug 8631742: ALTER SESSION SET ... NOT HAVING EFFECT IN 10.2.0.4
...
RELEASE NOTES:
]] Setting _gby_hash_aggregation_enabled at the session level did not
]] always take effect
REDISCOVERY INFORMATION:
If you change the use of hash aggregation at the session level, but this does
not affect the choice of aggregation method in subsequebtly parsed SQL,
you are probably hitting this bug.

このバグはパラメーターファイルで_gby_hash_aggregation_enabledパラメーターの値を指定する時にのみ起こります。

*._gby_hash_aggregation_enabled=FALSE

初期のHASH GROUP BY機能が多くのバグを持っていたから、パラメーターファイルでこのパラメーターをFalseで指定しているシステムが多いと思います。でもUSE_HASH_AGGREGATIONヒントを使うにもかかわらずHASH GROUP BY機能が動作しないというのは予想しなかった副作用でしょう。

このバグは10.2.0.5でパッチされました。