データベース・パフォーマンス・チューニング（初級編）

• はじめに
• 索引について
• 表の結合操作
• SQL文の診断
• SQL文の改良

はじめに

オラクルのパフォーマンスチューニングには、設計段階で行なうものと、アプリケーション開発時に行なうものがあります。
ここでは、アプリケーション開発時に効率的なＳＱＬ文を書くためのポイントをまとめます。

一般的なチューニング作業の流れ

1. 適切なデータベース設計
2. ＳＱＬ文のチューニング
3. アプリケーションのチューニング
4. メモリのチューニング
5. Ｉ／Ｏのチューニング
6. 競合のチューニング

索引について

テーブル（表）内のデータは、物理入力順に格納されている。
そのため、表内の特定の列に「索引」を作成して、よりよい検索パフォーマンスを引き出すことができる。

●索引には、

1. 単一型索引−−表内の単一の列から構成された索引
2. 連結型索引−−複数の列から構成された索引。最大３２列まで指定できる。

※連結型索引でも、先頭列だけは単一型索引と同様に使用される。

索引１	地域コード	部署コード	←地域コードをキーに検索すると、索引１が使用される
索引２	地域コード	−

●索引列選択のガイドライン

1. WHERE句の条件として頻繁に使用する列で、かつ検索対象となる行数が表全体に占める割合が低い場合
2. 列の値が比較的一意な列
3. 表の結合で使用する列

※オラクルでは、表に対して作成できる索引の数に制限はない。
しかし、更新頻度が高い表では、同時に索引も更新されるためオーバヘッドが大きくなるので、考慮が必要である。

表の結合操作

Oracleでは、複数の表を結合する際に以下の結合方法の１つを選択し、実行する。

1. ネストループ結合
2. ソートマージ結合
3. ハッシュ結合

ソート種別	内部動作
ネストループ結合	オプティマイザが、どちらかひとつの表を「外部表」として選択し、もう一方を「内部表」とする。 外部表の各行について、結合条件を満たす内部表の行を見つける 結合条件を満たす各行のペアでデータを結合し、結果を返す ※ルールベースの場合、各表に対応するアクセス・パスのランクが低いほうが外部表になる
ソートマージ結合	結合条件で使用される列をソートキーとして、各表をソートする ２つの表をマージして、結果を戻す。 ※ソート処理がメモリで行なえる程度のデータ量だと速い
ハッシュ結合	一方の表をハッシュ関数を用いてメモリ上にロードし、ハッシュテーブルを作成する ハッシュテーブルを利用して、ディスク上のもう一方の表の各行に対応する行を結合し、結果を返す。 ※ハッシュ結合は、コストベースの時で、各表が等価結合のときのみ使用される

SQL文の診断

１．オプティマイザ

SQL文の実行時、表への最適なアクセス方法（実行計画）を選択する機能。
DML文(SELECT、INSERT、UPDATE、DELETE)を発行する際にもっとも効率よく表にアクセスする方法を選択してくれる

オプティマイザの設定は、初期化パラメータ「OPTIMIZER_MODE」で指定する。

●ルールベース・アプローチ

OPTIMIZER_MODE=RULE

オプティマイザはSQL構文と表、索引定義を情報として、アクセス・パスの優先順位に基づいて実行計画を作成する。
WHERE文中の条件が複数ある場合、もっとも優先順位の高いアクセス・パスを採用する。

ルール・ベースによるアクセスパスの採用順位

ランク	アクセス・パス
1	ROWIDによる単一行
2	クラスタ結合による単一行
3	一意キーまたは主キーを持つハッシュ・クラスタ・キーによる単一行
4	一意キーまたは主キーによる単一行
5	クラスタ結合
6	ハッシュ・クラスタ・キー
7	索引付きクラスタ・キー
8	複合索引
9	単一列索引
10	索引列の境界付きの範囲検索（＜＞、BETWEEN）
11	索引列の境界無しの範囲検索（＜、または＞のみ）
12	ソート／マージ結合
13	索引付き列のMAXまたはMIN
14	索引付き列のORDER BY
15	全表走査

●コストベース・アプローチ

OPTIMIZER_MODE=CHOOSE

オプティマイザはANALYZE文を使用して収集した統計情報に基づいて、最適なアクセス・パスを採用する。
初期化パラメータでコストベースが選択されていても、統計情報が無ければルールベースで実行計画が立てられる。

計算（全表走査）による統計情報の取得

推定による統計情報の取得

統計情報の削除

２．診断ツールの利用

ANALYZE TABLE

コストベースの場合、まず、オプティマイザが使用する統計情報をテーブルに格納する。
適切な実行計画判断のために、統計情報は常に最新状態を保つことが望ましい。

●統計情報を取得する

SQL> ANALYZE TABLE emp COMPUTE STATISTICS;

表が分析されました。

SQL> ANALYZE TABLE dept COMPUTE STATISTICS;

表が分析されました。

SQL> explain plan set statement_id = '2'
  2  for
  3  select emp.ename, dept.deptno, dept.dname
  4  from emp, dept
  5  where emp.deptno = dept.deptno;

Explainに成功しました.

SQL> @showplan
ID: 2

Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT   Cost=3
  HASH JOIN
    TABLE ACCESS FULL DEPT
    TABLE ACCESS FULL EMP
	    

EXPLAIN PLAN文

オプティマイザが選択したSQL文の実行計画をPLAN_TABLEに格納する。
（PLAN_TABLE表は、オラクル標準提供の「UTLXPLAN.SQL」スクリプトを実行して作成する。

●EXPLAIN PLANの実行例

SQL> explain plan set statement_id = '1'
  2  for
  3  select emp.ename, dept.deptno, dept.dname
  4  from emp, dept
  5  where emp.deptno = dept.deptno;

Explainに成功しました.

SQL> select id, parent_id, position, operation, options, object_name, cost
  2  from plan_table 
  3  order by id;

        ID  PARENT_ID   POSITION OPERATION                      OPTIONS                        OBJECT_NAME                     COST
---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------- ----------
         0                       SELECT STATEMENT
         1          0          1 NESTED LOOPS
         2          1          1 TABLE ACCESS                   FULL                           EMP
         3          1          2 TABLE ACCESS                   BY INDEX ROWID                 DEPT
         4          3          1 INDEX                          UNIQUE SCAN                    DEPT_PRIMARY_KEY
	    

OPERATION列とOPTIONS列の関係(抜粋)

OPERATION	OPTIONS	内容
INDEX	UNIQUE SCAN	索引からの単一のROWIDの検索
	RANGE SCAN	索引からの一つ以上のROWIDの検索
MERGE JOIN	−	２つの行セットを受け取り、それぞれを特定のキーでソートし、 一方のセットの各行を他方の行と突き合わせて結合し、その結果を戻す処理 （ソートマージ結合）
NESTED LOOPS	−	外部表の各行と内部表の各行を比較し、条件を満足する行を結合し、 その結果を戻す処理（ネストループ結合）
HASH JOIN	−	２つのセットの行を結合し、結果を戻す処理（ハッシュ結合）
SORT	UNIQUE	行のセットをソートし、重複をなくす
	GROUP BY	行のセットをグループにソートする
	JOIN	マージ結合処理の前に、行のセットをソートする
	ORDER BY	ORDER BY句に基づいて、行のセットをソートする
TABLE ACCESS	FULL	表から全ての行の検索
	BY ROWID	ROWIDに基づいた、表からの検索

●EXPLAIN PLAN文の整形用SQL(showplan.sql)

SET VERIFY OFF
ACCEPT id CHAR PROMPT 'ID: '
SELECT LPAD(' ', 2*(level - 1)) || operation || ' ' || options
    || ' ' || object_name || ' ' || DECODE(id, 0, 'Cost=' || position)
    "Query Plan"
  FROM plan_table
START WITH id = 0
  AND statement_id = &id
CONNECT BY PRIOR id = parent_id
  AND statement_ID = &id

rem --- plan_tableの削除 ---
DELETE FROM plan_table WHERE statement_id = &id;
COMMIT;
/
	    

　→実行

SQL> @showplan
ID: 1

Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT   Cost=
  NESTED LOOPS
    TABLE ACCESS FULL EMP
    TABLE ACCESS BY INDEX ROWID DEPT
      INDEX UNIQUE SCAN DEPT_PRIMARY_KEY
	    

AUTO TRACE

オプティマイザが選択したSQL文の実行計画を、SQL*Plus画面上に表示する。
PLAN_TABLE表、及びPLUSTRACEロールが必要。

●オートトレース機能を利用してみる

SQL> set autotrace traceonly
SQL> select emp.ename, dept.deptno, dept.dname
  2  from emp, dept
  3  where emp.deptno = dept.deptno;

16行が選択されました。


実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=16 Bytes=688)
   1    0   HASH JOIN (Cost=3 Card=16 Bytes=688)
   2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=4 Bytes=92)
   3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=320)




統計表示
----------------------------------------------------------
          0  recursive calls
          6  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1230  bytes sent via SQL*Net to client
        827  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         16  rows processed
	    

SET AUTOTRACEの構文

SET AUTOTRACE { OFF | ON | TRACEONLY } [EXPLAIN] [STATISTICS]

• OFF　オートトレースの停止
• ON　オートトレースの開始
• TRACEONLY　オートトレースを開始し、問い合わせ結果は表示しない
• EXPLAIN　実行計画のみの表示
• STATISTICS　パフォーマンス統計のみの表示

SQL文の改良

１．索引を使用するSQL文を記述する

検索で実際に使用される索引は、Explain Planを用いて検証する。実行計画を見て、 索引を効率的に使用していない場合は、以下の指標に沿ってＳＱＬ文を改善するとよい。

●索引列での計算の回避

×　SELECT EMPNAME,SAL FROM EMP WHERE SAL * 12 > 5000000;
○　　SELECT EMPNAME,SAL FROM EMP WHERE SAL > 5000000 / 12;

SAL列に索引がついている場合、この列を計算式や関数に含めてしまうと索引が使用されないので、 下のように記述する。

※暗黙の型変換に注意する
Oracleでは、SQLで明示的に指定しなくても数値型⇔文字型の変換を行なう。 索引列では関数が使用されたのと同じことになり、索引が使用されない。 暗黙の変換を避けるには、比較の対象となる値の書き方に注意すること
（CHAR型：'12345' NUMBER型：12345）

●NOTの回避

×　SELECT EMPNAME,SAL FROM EMP WHERE DEPTNO != 0;
○　　SELECT EMPNAME,SAL FROM EMP WHERE DEPTNO > 0;

否定演算子を使用した場合、全表走査が行なわれるので、下のように記述する。

●NULLの回避

×　SELECT EMPNAME,SAL FROM EMP WHERE DEPTNO IS NOT NULL;

○　　SELECT EMPNAME,SAL FROM EMP WHERE DEPTNO >= 0;

NULL列は索引に入らないので、索引を使用するように下のように記述する。

●UNIQUE索引の優先

EMPNOにUNIQUE索引、DEPTNOに非UNIQUE索引が作成されているEMP表を検索する場合

　SELECT * FROM EMP WHERE EMPNO = '000012' /* ←この条件に索引が使われる */
　　AND DEPTNO = '0001';

Oracleでは、表に利用可能な索引が２つ以上存在し、１つがUNIQUE索引、もう一つが非UNIQUE索引である場合、 UNIQUE索引のみを表検索に利用し、もう一方の索引は無視される。

●複合索引使用の注意

DEPTNO＋AREAに複合索引が作成されているEMP表を検索する場合

○　SELECT EMPNAME,SAL FROM EMP WHERE DEPTNO >= 0 AND AREA = 'TOKYO';
○　SELECT EMPNAME,SAL FROM EMP WHERE DEPTNO >= 0;
×　SELECT EMPNAME,SAL FROM EMP WHERE AREA = 'TOKYO';

複数の列を組み合わせた複合索引は、構成列全てを条件で指定している場合、 および複合索引の構成列の先頭の列を単独条件で使用している場合に利用される。

２．共有プールを使用するSQL文を記述する

●解析済みSQL文の再利用

1. SELECT * FROM EMP;
2. select * from emp;
3. SELECT * FROM EMP;

Oracleでは、一度解析し最適な実行計画を算出したSQL文をメモリ内の共有プールに保持しておき、 再度同じ文字列を持つSQL文が発行された場合にこの情報を使用して処理速度を向上させる。
ただし、この文字列（SQL文）が同一かどうかはテキストレベルで判断されるため、 大文字と小文字が異なるだけでも異なる文と判断される。 （上記の例では、１と３は同じだが、１と２は違う文と判断される）

アプリケーションが実行するSQL文を同一にするため、標準的なSQL文の記述方法を決定しておく、 ホスト変数を使用する、などの注意が必要である。

３．ヒント文の使用

コストベースの場合、オプティマイザが採用する実行計画にヒントを与えることで、 より効率的なＳＱＬ文の実行ができる。
ヒントは、指定が相反しないものであればブランクを空けて複数指定できる。

書式

ヒントの一覧(抜粋)

分類	ヒント	内容
目標に対するヒント	ALL_ROWS	最高のスループットを目標にしたコストベースオプティマイザのアプローチ
	FIRST_ROWS	最高の応答時間を目標にしたコストベースのアプローチ
	RULE	ルールベースのオプティマイザのアプローチ
アクセス方法に対するヒント	FULL(テーブル名)	指定された表の全表走査を行う
	ROWID(テーブル名)	ROWIDにより指定された表の走査を行う
	HASH(テーブル名)	指定された表のハッシュ走査を行う
	INDEX(テーブル名 インデックス名)	指定された表に対し指定された索引走査を行う
	INDEX_ASC(テーブル名 インデックス名)	指定された表に対し索引走査を昇順に行う
	INDEX_DESC(テーブル名 インデックス名)	指定された表に対し索引走査を降順に行う
	AND_EQUAL(テーブル名 インデックス名)	指定された表に対し索引の併合処理を使用する
結合処理に対するヒント	ORDERED	FROM句に指定された順序で結合する
	USE_NL(テーブル名 ・・・)	指定されたテーブルを内部表にしてネストループ結合を行う
	USE_MERGE(テーブル名 テーブル名 ・・・)	指定されたテーブルをマージしてソートマージ結合を行う
	USE_HASH(テーブル名 テーブル名 ・・・)	指定されたテーブルをハッシュテーブルに変換してハッシュ結合を行う

2003/02/18　担当：K.Y