パフォーマンスセラピー / SQLチューニングの基本とは

Oracleチューニング講座

第2章　SQLチューニングの基本とは

Oracleシステムにおけるパフォーマンス問題の80％は、不適切なSQLに原因があるといわれています。
SQLを適切にチューニングせずに、システムパフォーマンスを解決することは不可能です。
今回は、SQLチューニングの基本として、SQLのアクセス・パスを理解するための知識を整理してみましょう。

SQLを実行した際に内部的にデータにアクセスする方法は、大きく分けて全表スキャンと索引スキャンに分類できます。

全表スキャン(フル・テーブル・スキャン)

全表スキャンとは、索引が存在しない場合（あるいは使用できない場合）に、データに対してアクセスする方法です。

全表スキャンには、以下の特徴があります。

■ 表を構成しているデータ・ブロックを最高水位標（ハイウォーターマーク、HWM）まで読込んで、表の各行が
　 WHERE句の条件を満たすかどうか判断する。

■ マルチ・ブロック読込みによって複数ブロックをまとめて読込む。
　 ※1回のI/O操作で読込むブロック数はDB_FILE_MULTIBLOCK_READ_COUNTパラメータで決定されま
　　　す(デフォルト8)。

NOTE

：

最高水位標(HWM)とはセグメントに割り当てられたブロックの中で、今までデータが挿入されたことがある最後尾のブロックのことです。

全表スキャンが選択された場合、ＳＱＬの実行計画には「TABLE ACCESS (FULL) 」と表示されます。

例）ENAME列がJONESのデータを検索する（全表スキャンの例）　※SQL*PlusのAutotrace機能

SQL> SELECT * FROM emp WHERE ename = 'JONES';

実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=37)
1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=37)

また、上記のＳＱＬでの全表スキャンの動きを表したのが、図１となります。

図１：全表スキャンの仕組み

①		ユーザーからSQLが発行される。
②		サーバープロセスによって、ディスクからマルチブロックの読込みが行われる。
③		HWMまで、EMP表を構成するすべてのデータ・ブロックが読込まれる。

索引スキャン

索引スキャンとは、索引を利用してデータに対してアクセスする方法です。
索引は、SQLのパフォーマンスを向上させるためのオブジェクトであり、列に対して作成します。
索引には指定された列の値と、その値が含まれる行のROWIDがセットになりソートされて含まれています。索引が利用されるには、索引を作成した列がWHERE句に記述される必要があります。

※ROWID：各行に一意に割振られている行のアドレスです。

索引を使用したアクセスには以下のような特徴があります。

■ 条件式に索引が作成されている列が指定されると、索引ブロックを読込んでROWID情報を取得し、その　 ROWIDを使用して表にROWIDアクセスする。

■		シングル・ブロック読込みを使用する。

索引スキャンが選択された場合、以下のように、ＳＱＬの実行計画には「INDEX (RANGE SCAN)」と表示されます。

例）ENAME列がJONESのデータを検索する（索引スキャン）。
　　※事前にEMP表のENAME列に索引が作成されている。

SQL> SELECT * FROM emp WHERE ename = 'JONES';

実行計画
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=37)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Card=1 Bytes=37)
2 1 INDEX (RANGE SCAN) OF 'ENAME_IND' (INDEX) (Cost=1 Card=1)

また、上記のＳＱＬでの索引スキャンの動きを表したのが、図2となります。

図2：索引スキャンの仕組み

①		ユーザーからSQLが発行される。WHERE句で索引のあるENAME列を指定。
②		ENAME列に作成されている索引にアクセスする。
③		索引には列の値とROWIDがセットになっているので（この例の場合は、'JONES'のデータとROWID）ROWIDを使用して表に対してROWIDアクセスする。 ※JONESのデータが複数ある場合は、複数回ROWIDアクセスを繰返す。

適切なスキャン方法の選択

SQLチューニングのポイントは、全表スキャン（フル・テーブル・スキャン）と索引スキャンを上手く使い分けることにあります。
全表スキャン（フル・テーブル・スキャン）と索引スキャンには、それぞれに適した処理があるので、それに適した使い分けを意識してください。

	１）全表スキャンが高速な場合
	表の中の大部分のブロックにアクセスする場合は、索引スキャンよりも全表スキャンが高速になります。それは以下の理由によります。 ■ 大量ブロックにアクセスするには、少数の大きいI/Oコールのほうがコストが低い。　全表スキャンは、マルチ・ブロック読込みにより1回のI/Oコールでまとめて複数のブロックを読込みます。 ■ 索引スキャンと比較して、結果的に読込むブロック数が少ない。　索引スキャンでは、「索引ブロックの読込み＋表ブロックの読込み」がアクセスしたブロック数となります。　その合計が、表を構成している総ブロックを上回るような場合は全表スキャンがより高速です。
	２）索引スキャンが高速な場合
	表の中の少数のブロックにアクセスする場合は、全表スキャンより索引スキャンが高速になります。それは以下の理由によります。 ■ 少数ブロックにアクセスするには、小さいI/Oコールのほうがコストが低い。　必要なブロックのみを読込む、シングル・ブロック読込みを行います。 ■ アクセスするブロック数が少数ですむ。　索引を使用すると、不要なブロックに対するアクセスを減少させることができます。

スキャン方法の比較

	全表スキャン	索引スキャン
ブロック読み込み	マルチブ・ロック読み込み	シングル・ブロック読み込み
適した処理	表の多くのブロックにアクセスする	表の少数のブロックにアクセスする

NOTE

：

適切なスキャン方法が使用されていない場合、大量の余分なブロックが読込まれ、その結果メモリーの使用効率低下、I/Oの問題等を引起します。パフォーマンス問題があるシステムでは、まずはSQLをチューニングすべきであるということを忘れないでください。

第3章　パフォーマンス改善のアプローチ方法を学ぶ