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

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

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

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

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

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

disp_201511_1_img_1_概要.png

図1:検証イメージ 

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

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

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

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

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

検証内容

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

検証詳細

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

検証環境

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

マシン OS 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のデータ生成機能を使って準備します。(第1回参照)

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

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

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

データベース開発に関するお役立ち資料

disp_201511_1_img_2_実行計画1.png

 

disp_201511_1_img_3_実行計画2.png

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

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

disp_201511_1_img_4_実行計画3.png

 

disp_201511_1_img_5_実行計画4.png

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 1回目 2回目 3回目 4回目 5回目 平均
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

※単位:秒

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

disp_201511_1_img_6_検証結果1棒グラフ.png

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

追加検証

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

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

【検証結果・平均値】

  from句の2つ目に指定するテーブル
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

※単位:秒

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

disp_201511_1_img_7_検証結果2折れ線グラフ.png

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

結論

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

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

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

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

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

新規CTA

RECENT POST「DBlab」の最新記事


この記事が気に入ったらいいねしよう!
OBトライアル
ブログ購読のお申込み

RANKING人気資料ランキング

RANKING人気記事ランキング

RECENT POST 最新記事