3-6 SQLパフォーマンスチューニング −最適なSQL文の書き方−
実行計画の分析
Explain Planを使用する
18 March, 1998 Updated
PCDN OracleWG S.Yamazaki
オプティマイザが実行計画を作成する場合、コストベース・アプローチかルールベース・アプローチのいずれかを選択します。それぞれのアクセスパスの選択については「Oracleのオプティマイザ」に述べているとおりであり、
ここでは、いずれかのアプローチによって作成された実行計画の説明をしていきます。
なお、R7.3.2あたりからCBOでのみ動作する計画がありますので、CBO主体（analyzeコマンドで統計を取った状態）で説明したいと思います。
実行計画とは、DML文(SELECT,UPDATE,DELETEなど)を実行するために、いくつかのステップを踏むことになります。ステップでは、索引・行の読み込み、ソート、変換など、様々な動作が含まれています。これらの動作の組み合わせを実行計画と呼んでいます。
この実行計画を分析するために一般にはEXPLAIN PLANというコマンドを発行し、戻される結果により判断します。ここでは、EXPLAIN PLANコマンドの使用法の説明を交えて、主な実行計画の説明をしていきたいと思います。
このEXPLAIN PLANコマンドを使用するためには、以下の準備が必要です。
PLAN_TABLE表の作成
この表がないと、EXPLAIN PLANは使用できません。（SQL*Plusのautotrace onでも必要となります。）各ユーザーごとに作成するよりも、以下の手順を踏んだ方が良いかもしれません。
PLAN_TABLEの作成
1. ユーザーsysでログイン。
2. utlxplan.sqlを実行。（オラクルホーム\rdbmsxx\admin\の下にあります）
3. 次の権限を実行。
GRANT SELECT ON SYS.PLAN_TABLE TO PUBLIC;
GRANT INSERT ON SYS.PLAN_TABLE TO PUBLIC;
GRANT UPDATE ON SYS.PLAN_TABLE TO PUBLIC;
GRANT DELETE ON SYS.PLAN_TABLE TO PUBLIC;
4. パブリックシノニムの作成。
CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE;
EXPLAIN PLANの使用方法
では、実際に試してみましょう。（SCOTTユーザー使用）
SCOTT.EMP表とSCOTT.DEPT表を使った簡単なSQL文の実行計画を見てみます。
EXPLAIN PLAN SET STATEMENT_ID='PLANTEST' FOR -- コマンドの構文（文頭部）
SELECT e.empno, e.ename, d.dname FROM scott.emp e, scott.dept d WHERE e.deptno=d.deptno; -- ターゲットSQL文
これで、「ターゲットのSQL文」の実行計画がPLAN_TABLEに格納されます。
次に、その実行計画を見てみましょう。ちょっと普段では使用しない句がありますが、
これはツリー構造表示するためのものです。
SELECT DECODE(id,0,'', LPAD(' ',3*(level-1))||level||'.'||position)||' '||operation||' '||options||' '||object_name||' '||
object_type||' '||DECODE(id,0,'Cost = '||position) QUERY
FROM plan_table CONNECT BY PRIOR id = parent_id AND statement_id = 'PLANTEST'
START WITH id = 0 AND statement_id = 'PLANTEST';
RBOでの結果：
SELECT STATEMENT Cost =
2.1 NESTED LOOPS
3.1 TABLE ACCESS FULL EMP
3.2 TABLE ACCESS BY ROWID DEPT
4.1 INDEX UNIQUE SCAN PK_DEPT UNIQUE
CBOでの結果：
SELECT STATEMENT Cost = 3
2.1 HASH JOIN
3.1 TABLE ACCESS FULL EMP
3.2 TABLE ACCESS FULL DEPT
|
なぜ、RBOとCBOで結果が違うか簡単に説明しましょう。
RBOでは、表の統計情報がないため、スループット優先で、索引を使用しようとします。EMP表をすべて読み込み、EMP.DEPTNOと同じキーをDEPT表に索引を使用しループしながら結果行を取得します。
逆にCBOでは統計が採られているため、索引を使用せずとも、ともに全表操作しても早いと判断しています。（ちなみにヒント /*+ RULE */ を指定するとルールベースで実行する事も可能です。）
このように、同じSQL文でもCBOとRBOでは、違う実行計画が立てられます。常に念頭に置くようにしておいたほうが良いでしょう。（頭の片隅でも(^^;）
先の実行計画の中の２行目（2.1の階層）にNESTED LOOPSとかHASH JOINとかがあります。これは、複数表を結合（自己結合含む）する場合の方法です。Oracleの結合方法は、
・ソート・マージ結合
・ネステッド・ループ結合
・クラスタ結合（CBOのみ）
・ハッシュ結合
があります。また、２つ以上の表を結合する場合、「先に２つの表を結合し、その中間表と残りの表を結合する」というような順序も関係してきます。以降、かいつまんで説明していきましょう。
ソート・マージ結合
ごらんの通りです。
--------------------------------------------------------------------------------
SELECT STATEMENT
2.1 MERGE JOIN
3.1 SORT JOIN
4.1 TABLE ACCESS FULL DEPT
3.2 SORT JOIN
4.1 TABLE ACCESS FULL EMP
ネステッド・ループ結合
ごらんの通りです。
--------------------------------------------------------------------------------
SELECT STATEMENT
2.1 NESTED LOOPS
3.1 TABLE ACCESS FULL EMP
3.2 TABLE ACCESS BY ROWID DEPT
4.1 INDEX UNIQUE SCAN PK_DEPT UNIQUE
クラスタ結合
ごらんの通りです。
--------------------------------------------------------------------------------
SELECT STATEMENT
2.1 NESTED LOOPS
3.1 TABLE ACCESS FULL DEPT
3.2 TABLE ACCESS CLUSTER EMP
4.1 INDEX UNIQUE SCAN CLUS_IDX_DEPTNO CLUSTER
ハッシュ結合
ごらんの通りです。
--------------------------------------------------------------------------------
SELECT STATEMENT
2.1 HASH JOIN
3.1 TABLE ACCESS FULL EMP
3.2 TABLE ACCESS FULL DEPT
上の例では、省きましたが、CBOで実行計画が立てられると、コスト値が算出されます。これは、読み込んだブロック数とされており、ID列値が「0」のCOST列値を
「SELECT STATEMENT Cost = 29」として一般的に表示されています。
QUERY
------------------------------------------------
SELECT STATEMENT Cost = 29
2.1 TABLE ACCESS BY LOCAL INDEX ROWID MS_******
3.1 BITMAP CONVERSION TO ROWIDS
4.1 BITMAP AND
5.1 BITMAP MERGE
：
|
ID
----------
0
1
2
3
4
：
|
COST
----------
29
727
|
CBOのなかでも新しく取り入れられたのが、スター問合わせです。R7.2までは、直積型スター問合わせでしたが、R8から「スター変換スター問合わせ」ができるようになりました。
この問合わせを有効にするためには、初期化パラメーター「star_transformaion_enabled=True」にするか、明示的にヒント「/*+ STAR_TRANSFORMATION */」を指定します。ちなみに従来のスター問合わせのヒントは「/*+ STAR */」です。
では、どんな恩恵を受けることができるのでしょうか。
少し具体例を見てみます。次のような大規模な実表(ファクト表)と、複数の参照表(ディメンション表)を使用した検索を考えます。
SELECT FC.NAME, D1.DATA, D2.DATA, D3.DATA
FROM FACT_TBL FC, DM1 D1,DM2 D2,DM3 D3
WHERE
FC.D1_ID=D1.ID AND
FC.D2_ID=D2.ID AND
FC.D3_ID=D3.ID AND
D1.DATA = 'xxxq' AND
D2.TYPE IN ('xx1','xx2') AND
D3.CODE BETWEEN 60 AND 100;
スター変換スター問合わせ
この問い合わせは、必ずしも適用されるとは限りません。スター変換が適用されると、オプティマイザは内部副問合わせを使用します。オプティマイザが合理的でないと判断したら、ヒントを指定しても変換は行われません。
「スター変換スター問合わせ」は、「ビットマップ・スター変換スター問合わせ」と呼ばれます。名前のとおり、これはビットマップ索引との併用によって実現されるものです。
先の問い合せは以下のように内部で副問い合せに置き換えられます。
内部変換されたイメージ
SELECT BITMAP_MARGE
WHERE
FC.D1_ID IN (SELECT ID FROM DM1 WHERE DATA = 'xxxq') AND
FC.D2_ID IN (SELECT ID FROM DM2 WHERE TYPE IN ('xx1','xx2')) AND
FC.D3_ID IN (SELECT ID FROM DM3 WHERE CODE BETWEEN 60 AND 100)
スター変換されたEXPLAIN PLANです。
QUERY
--------------------------------------------------------------------------------
SELECT STATEMENT Cost = 4273
2.1 HASH JOIN
3.1 HASH JOIN
4.1 TABLE ACCESS FULL DM2
4.2 HASH JOIN
5.1 TABLE ACCESS FULL DM1
5.2 TABLE ACCESS BY INDEX ROWID FACT_TBL
6.1 BITMAP CONVERSION TO ROWIDS
7.1 BITMAP AND
8.1 BITMAP MERGE
9.1 BITMAP KEY ITERATION
10.1 TABLE ACCESS FULL DM1
10.2 BITMAP INDEX RANGE SCAN BIT_DM1
7.2 BITMAP AND
8.1 BITMAP MERGE
9.1 BITMAP KEY ITERATION
10.1 TABLE ACCESS FULL DM2
10.2 BITMAP INDEX RANGE SCAN BIT_DM2
7.3 BITMAP AND
8.1 BITMAP MERGE
9.1 BITMAP KEY ITERATION
10.1 TABLE ACCESS FULL DM3
10.2 BITMAP INDEX RANGE SCAN BIT_DM3
3.2 TABLE ACCESS FULL DM3
以下の図は、スター変換の実行計画を表したものです。
Oracleのオプティマイザ
正しい索引の使用方法
ヒントを使用する
統計情報
Oracle WorkGroup
int21 ホームページ |
PCDN ホームページ
Copyright (c) 1998 PCDN All Rights Reserved.
For questions or comments, please send mail to: pcdn@int21.co.jp
