3-6 SQLパフォーマンスチューニング　－最適なSQL文の書き方－

Oracleのオプティマイザ

CBOとRBO

8 April, 1998 Updated　　
PCDN OracleWG S.Yamazaki　　

　普段、SQL文を書いているときは、期待した結果が得られた文が最適であると思いがちです。しかし、果たしてそれが最適かどうかは疑問が残ります。最適なSQL文を書くことはプログラマの責任であり、アプリケーションの応答時間の短縮や、利用する資源の競合の減少を図る上では避けて通れない問題です。
　ここで説明することが、SQL文のチューニングの最終到達点である「最適なアクセスパス」を選択できるように少しでもお役に立てれば幸いです。

最適なSQL文とは

　最適なSQL文とは何をもって「最適」とするのでしょう。オペレーターが期待するデータ集合をDBから抽出するとき、さまざまなSQLの書き方を想定すると思います。その時、オペレーターはある程度の応答時間を想定してSQLを書くと思います。では、試行錯誤して書いたSQL文が、期待した応答時間でデータを取得できたらそれで良いのでしょうか？私にはその時点では、まだ「適切なSQL文」でしかなく充分ではないと思っています。
　では、その適切なSQL文は最適ではないのか？という疑問が生じます。この時点で考えなければいけないことは、

無駄な条件、足りない条件はないか？あるいは、きちんとしたデータ型で条件指定しているだろうか？
適切な索引が使用されているのだろうか？
最終的なデータを抽出するのに、どのくらいの作業域を使用しているのだろう？

「最適なSQL文」

最も少ない資源を使用し、最も早く抽出できるようにしたSQL文です。

オプティマイザの機能

　では、最適なSQL文を記述するためにはどうすれば良いでしょう。それにはまず、オプティマイザの機能を理解しなければいけません。オプティマイザは、SQL文を受け取ると、次の流れで処理を行います。

１．構文チェック 記述された構文のチェック、テーブル・列名などを静的ディクショナリ表でチェックします。
また、Viewを使用していれば実表に展開し、Between述語など使用してる場合は、簡単な構文にも直したりします。

２．実行計画の作成 実データをアクセスするために、索引を使用するか、複数表を使用した結合時に、どんな結合方法を使用するか判断します。
（ソート･マージ結合、ネステッド･ループ結合、ハッシュ結合、スター結合などがあります。詳しくは後述します。）

　この実行計画を作成するとき、Oracleは２つの最適化アプローチのいずれかの方法を選択します。

コストベース・オプティマイザ（CBO）
　ANALYZEコマンドにより得られた表や索引の統計情報をもとに、コスト（読込みブロック数、I/OやCPU使用量、実行時間など）が最も小さい実行計画を選択します。
　コストベースでは、SQL文の構文と統計情報のデータ容量や分布率により、アクセスパスを決定しますので、同じSQL文でもアクセスパスは最も最適なパスに変更されることになります。
　なお、ハッシュ結合・スター問合わせなどは、CBOでしか使用できません。また、Oracle8からのCBOは格段と機能向上が図られており、今後のアプリケーションの新規開発にはコストベース・アプローチを使用するよう推奨されています。

ルールベース・オプティマイザ（RBO）
　OracleはSQL文のWhere句などの構文情報(Where句の順序には影響されません。)と索引情報からアクセスパスを1～15のランク付けを行い、一番高いランクのパスを選択することになります。
　ルールベースでは、コストベースと違い、データ容量や分布率が変動しようとも、同じSQL文では毎回同じアクセスパスを選択し、例え全表走査の方が高速な場合でも索引が使用できる状態であれば、索引を使ったアクセス・パスが選択されます。

アクセス・パス

ランク1：ROWIDを明示的にWhere句に指定した場合やCURRENT OF句を指定してコンパイルされてる場合。。
ランク2：クラスタ結合により、単一行を戻す場合。
ランク3：主キーまたはユニークキーが指定されたハッシュ・クラスタにより単一行が戻される場合。
ランク4：主キーまたはユニークキー列を全てを、Where句でイコール比較した場合。
ランク5：クラスタキー全てがWhere句でイコール比較された場合。
ランク6：ハッシュ・クラスタキー全てがWhere句でイコール比較された場合。
ランク7：索引クラスタキー全てをWhere句でイコール比較した場合。
ランク8：複合索引全てをWhere句でイコール比較した場合。
ランク9：単一索引をWhere句でイコール比較した場合、または、複数の単一索引をAND接続しイコール比較した場合。
ランク10：単一索引または、複合索引の先頭列をWhere句で有界(下限・上限がある)範囲検索した場合。
ランク11：単一索引または、複合索引の先頭列をWhere句で非有界(下限・上限どちらかが無い)範囲検索した場合。
ランク12：ソート／マージ結合される場合。
ランク13：単一索引または、複合索引の先頭列のみをMAX,MIN関数で選択した場合。
ランク14：単一索引または、複合索引の先頭列をORDER BY句に指定した場合。
ランク15：全表走査。（１から１４のランクに入らないSQL文。）
その他、ランク付けされていないコスト・ベースで使用されるアクセス・パス(ビットマップ索引走査など)があります。

RBOのルール

　現在、データベース全体の方向性がコストベースになってきています。しかし、コストベースをマスターするには、ルールベースのオプティマイザを充分理解していないといけません。
　ここでは、ルールベースにおいてのルールをいくつか説明していきたいと思います。このルールは「最適なSQL文を書くための基本である」と私は思っています。
　なお、コストベースの詳しい説明については、「実行計画の分析」で触れることになります。

Null値の検索は索引を使用しない。

SELECT * FROM tbl WHERE col_a IS NULL

!=, <> は索引を使用しない。

SELECT * FROM tbl WHERE col_a != 1300　全表走査
SELECT * FROM tbl WHERE col_a > 1300 OR col_a < 1300　索引使用

索引列を計算しない。

SELECT * FROM tbl WHERE col_a / 100 > 25　全表走査
SELECT * FROM tbl WHERE col_a > 25 * 100　索引使用

OR検索

SELECT * FROM tbl WHERE col_a = 20 OR col_b > 1000　全表走査

結合索引の順序。

SELECT * FROM tbl WHERE col_b = 20　全表走査
SELECT * FROM tbl WHERE col_a >= 0 AND col_b = 20　索引使用

複数の索引を使用する場合の優先順位。

SELECT * FROM tbl WHERE col_a = 1200 AND col_b = 20　索引使用

結合索引を含む索引が複数あると結合索引が優先されます。（[col_a],[col_b,col_c]に索引が付いてるとします。）

SELECT * FROM tbl WHERE col_a = 1200 AND col_b = 20 AND col_c=500　[col_b,col_c]索引使用

イコールと不等号の索引ではイコールが優先されます。（[col_a],[col_b]に索引が付いてるとします。）

SELECT * FROM tbl WHERE col_a > 1200 AND col_b = 20　[col_b]索引使用

全て不等号なら索引作成順で最後の索引が優先されます。（[col_a],[col_b],[col_c]の順で索引が作成されてるとします。）

SELECT * FROM tbl WHERE col_a > 1200 AND col_b > 20 AND col_c < 500　[col_c]索引使用

Like句の使用。

SELECT * FROM tbl WHERE col_e Like 'A%'　[col_c]索引使用
SELECT * FROM tbl WHERE col_e Like '%A%'　全表走査
SELECT * FROM tbl WHERE col_e Like '_A%'　全表走査
SELECT * FROM tbl WHERE col_e Not Like 'A%'　全表走査

Order By句の使用。

Group By句の使用。

テーブルの結合。

文字の区別

　一つ忘れてならないのが、SQL文を書くときの大文字小文字の扱いです。
　table_aとtable_Aは同じテーブルと解釈してくれますが、SELECT * FROM table_aとSELECT * FROM table_Aは別なSQL文として認識してしまいます。これは、共有SQL領域に同じSQL文がある場合、Oracleが構文チェックなどをする時間を省けますが、1字でも違うと、再度構文チェックされます。
　SQL文を書く場合、大文字小文字の使用方法についても独自のルールを設けると良いでしょう。

正しい索引の使用方法
ヒントを使用する
実行計画の分析
統計情報

[雑談]
　さて、少し私の個人的な考えも述べておきましょう。とてもくだらないことかも知れないので読み飛ばしても結構です。(^^;
　私は、最適なSQL文を書くことに対してとても魅力を感じています。現在、サーバーの管理からチューニングを行っていますが、主にクライアントからのアクセスのためにSQL文をチューニングすることがかなりあります。そこで私は考えます、「このままOracleに任せていいのか？」と。はたしてOracleのオプティマイザは私の思うとおりに動いているのだろうか．．．ここからOracleとの知恵比べが始まるのです。
　OracleのCBOはとてもよく出来ています。しかし、100%信じてはいけません、間違うこともあるのです。また、Oracleのオプティマイザをよく理解し、必要な知識を得ると出し抜くこともできるのです。例えば、10秒が1秒弱になったときとかは、「これがプログラマの醍醐味」だと、優越感に浸れることもあります。(^^;
　この優越感はぜひ、皆さんにも味わっていただきたいと思います。「優越感だけでは仕事はできない」「ただの自己満足じゃないか」と思われるかもしれません。けれど、きっと使用されたユーザーさんにも喜んでいただけると思います。

Oracle WorkGroup
int21 ホームページ | PCDN ホームページ

１．構文チェック	記述された構文のチェック、テーブル・列名などを静的ディクショナリ表でチェックします。また、Viewを使用していれば実表に展開し、Between述語など使用してる場合は、簡単な構文にも直したりします。
２．実行計画の作成	実データをアクセスするために、索引を使用するか、複数表を使用した結合時に、どんな結合方法を使用するか判断します。（ソート･マージ結合、ネステッド･ループ結合、ハッシュ結合、スター結合などがあります。詳しくは後述します。）