Oracle オプティマイザによるアクセス順はパフォーマンスにどの程度影響があるか

2015.11.01 株式会社システムインテグレータ

テーブルのアクセス順はパフォーマンスに影響するのか

テーブルを結合するようなSQLを実行する際、パフォーマンスを考慮しfrom句で指定するテーブルの順番を考え直すといった場面があるかと思います。
今回はデータ件数の異なるテーブルを結合条件に指定した場合に、from句で指定するテーブルの順番によって、パフォーマンスにどれくらいの違いが出るのか検証します。
また、対象のデータベースは「Oracle」とします。

と、ここまで説明してご存知の方からはすぐに、「Oracleのバージョンやオプティマイザの種類によって結果が異なるのではないか。」とご指摘をいただきます。
後述しますが、Oracle10g以降の場合は「ルールベース・オプティマイザ（RBO）」がサポートされなくなり、「コストベース・オプティマイザ(CBO)」が主流となっています。
そうなると、SQL分のチューニングが自動的に行われ、from句の順番を考慮する機会はほとんど無くなりつつあります。

では今回の検証は取りやめに……しません！！
コストベース・オプティマイザ採用のOracle11gでオプティマイザ・ヒントを用いて、「from句の順番=Oracleが検索するテーブルの順番」によって、パフォーマンスにどれくらいの違いが出るのかを検証します。

Oracle オプティマイザによるアクセス順はパフォーマンスにどの程度影響があるか 1

図１：検証イメージ

Oracleのオプティマイザの種類に関して

Oracleのオプティマイザは、データベースへの問い合わせ時にデータに対して最適なアクセス方法を考えてくれる機能です。その種類として、前述しました「ルールベース・オプティマイザ」と「コストベース・オプティマイザ」についても少し触れておきます。

	ルールベース・オプティマイザ	コストベース・オプティマイザ
ﾁｭｰﾆﾝｸﾞ	SQL 文を分解した情報と所定ルールに従う。（格納データの内容は考慮しない）	データの件数・偏り・分布などを考慮する。
ﾒﾘｯﾄ	・ルールに従うため、チューニングが容易・新機能が追加されないため安定して利用可能・実行計画の変更が少ないなど・・・	・データの変動に対応できる・オプティマイザの新機能を利用できる・アクセスパスを柔軟に最適化できるなど・・・
ﾃﾞﾒﾘｯﾄ	・データの変動に対応できない・オプティマイザの新機能が利用できない・実行SQLのメンテナンスが困難など・・・	・統計情報を収集する必要がある･無駄な結合処理により性能劣化する可能性があるなど・・・

Oracle10g以降はサポートされていないとはいえ、初期化パラメータ次第ではまだRBOを利用できるので、利用しているところは多いかもしれません。
ルールに従うという所が見えやすくて分かりやすいからですかね。

※以降の表記は下記の通り省略します。
「ルールベース・オプティマイザ」　→　「RBO」
「コストベース・オプティマイザ」　→　「CBO」

検証内容

今回は格納されているデータ件数の異なるテーブルを用意し、そのテーブルを結合するSQL文の実行時間を、from句の指定順番を変えながら計測します。

検証詳細

・サンプルテーブルにそれぞれ100件、100万件のデータを作成
・テーブル結合を含むSELECT文の実行時間を計測
・from句の指定順を変えて実行時間を計測
・それぞれ5回実施し、平均時間を採用する
・一回の検証ごとにテーブルデータを削除し、DBバッファキャッシュをクリア
・データ生成には OBのデータ生成機能を使用

検証環境

今回の検証環境は以下の通りです。

マシン

Windows7

CPU

Intel Core i5-2430M 2.40GHz

メモリ

4GB

その他

特に無し

DB
ｹｰｽ1

対象RDBMS

Oracle11.0.2.0.1.0

テーブル名

TEST_FROM_100(データ件数:100件)
TEST_FROM_1000000(データ件数:100万件)

テーブル項目(共通)

COL1	NUMBER(10)
COL2	VARCHAR2(500)

※主キー設定：COL1

検証準備

①データ生成(100件、100万件を用意)
まずは生成データを下記の通り設定しました。
いつものようにSI Object Browserのデータ生成機能を使って準備します。(第１回参照)

カラム名	データ生成方法
COL1	連番。1から開始し1ずつカウントアップ
COL2	乱数値　全桁埋め

②実行SQLを準備
実行SQLの準備ですが、前述したようにCBOでは自動的に最適なアクセスパスを判断するため、from句の順番を変えても実行計画が変わらず、パフォーマンスに影響し辛くなっています。

実際に実行計画を確認してみましょう。
確認にはSI Object Browserの実行計画画面が便利です。
→SQL実行画面の「実行計画ボタン」をクリックすると確認できます。

Oracle オプティマイザによるアクセス順はパフォーマンスにどの程度影響があるか 2

Oracle オプティマイザによるアクセス順はパフォーマンスにどの程度影響があるか 3

図２：実行計画(オプティマイザ・ヒントなし)

上記の通りfrom句の順番を変えても変化はありませんので、今回の検証ではオプティマイザ・ヒントを付けてfrom句の順番でアクセスする順番が変わるようにしました。

Oracle オプティマイザによるアクセス順はパフォーマンスにどの程度影響があるか 4

Oracle オプティマイザによるアクセス順はパフォーマンスにどの程度影響があるか 5

from句の順番と今回の検証方法について触れたところで、遅くなりましたが今回実行するSQLは以下の通りです。

■SQL①(データ件数：少→多　の順)

select
/*+ ordered */
*
from
TEST_FROM_100 A,
TEST_FROM_1000000 B
where
A.COL1 = B.COL1
/

■SQL②(データ件数：多→少　の順)

select
/*+ ordered */
*
from
TEST_FROM_1000000 B,
TEST_FROM_100 A
where
A.COL1 = B.COL1
/

[RELATED_POSTS]

検証結果

検証結果は以下の通りとなりました。

実行SQL	１回目	２回目	３回目	４回目	５回目	平均
SQL①	00:00.346	00:00.221	00:00.347	00:00.224	00:00.346	00:00.297
SQL②	01:13.301	01:12.921	01:08.247	01:12.569	01:08.949	01:11.197

※単位：秒

平均値での実行時間の対比は以下の通りです。

Oracle オプティマイザによるアクセス順はパフォーマンスにどの程度影響があるか 6

データ件数の多いテーブルに、先にアクセスするSQLの方が、かなり時間がかかる結果となりました。その差は歴然ですね。

追加検証

今回は検証を追加し、データ件数にバリエーションを増やして、様々な組み合わせで実行時間を計測します。
【データ件数バリエーション】
・100件
・1000件
・10000件
・100000件
・1000000件

各データ件数の格納されたテーブルを、from句の１つ目と2つ目に指定してSQLを実行します。
追加検証でも5回実行して平均値を採用しますが、検証結果では平均値のみ公開します。

【検証結果・平均値】

		from句の２つ目に指定するテーブル
		100件	1000件	10000件	100000件	1000000件
from句の 1つ目に指定するテーブル	100件	00:00.359	00:00.375	00:00.437	00:00.312	00:00.297
	1000件	00:00.515	00:00.469	00:00.406	00:00.546	00:00.811
	10000件	00:00.500	00:00.452	00:00.624	00:00.936	00:00.796
	100000件	00:00.671	00:00.468	00:00.422	00:00.718	00:01.077
	100000件	01:11.197	00:58.674	00:51.498	01:04.004	00:52.308

※単位：秒

一つ目に指定したテーブルに着目して比較したグラフは以下の通りです。

Oracle オプティマイザによるアクセス順はパフォーマンスにどの程度影響があるか 7

特筆すべきは1つ目に1000000件を指定した場合ですね。2つ目に指定した件数に関わらず実行時間がかかってしまっています。

結論

今回の検証結果では、以下のようになりました。
・データ件数によって、アクセスする順番でパフォーマンスに違いが出る
・一つ目にアクセスするテーブルの影響が大きい
・CBOの場合はアクセス順が変わらない場合がある（オプティマイザの判断)

今回の検証では、オプティマイザ・ヒントを利用して「from句の順番=アクセスする順番」が変わることで、パフォーマンスに違いが出ることが確認できました。
データの内容、件数、処理内容などによっては結果が異なるかと思われますが、今回の検証結果ではデータ件数を考慮した実行SQLのメンテナンスが非常に重要であることがわかりました。

また、from句で指定する順番のうち、一つ目のテーブルに大きく影響を受けるということが確認できました。
今回の検証では単純なテーブルと、単純な結合方法でのSQL実行であったため、このようなパフォーマンスの差が顕著に出たものと考えますが、パフォーマンスの良い順番にアクセスするという考えは正しかったということになります。

ただ、RBOを採用しているDBでは自動的に行っている部分が大きく、なかなか意識しない部分になりますので、もしCBOを採用する場合には少しでも参考にしていただければ幸いです。

今回の検証結果は以上となります。