Oracle ヒント句を利用したパフォーマンスチューニング

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

ヒント句はパフォーマンスチューニングに有効か

2015年11月に公開したテーマ「テーブルのアクセス順でパフォーマンスにどの程度影響があるか」の中で一部触れられていた「ヒント句」について、少し話を広げて検証をしてみました。

11月回では「ORDERED」と言うヒント句にてオプティマイザにFROM句に読み込ませるテーブル順を指定させていました。これによりデータ量の少ないテーブルを先に、データ量の多いテーブルを後に読み込ませることでパフォーマンスに違いが出てくることが実証されました。

今回の検証ではOracleのオプティマイザに用意された数多くのヒント句を利用して、パフォーマンスチューニングが出来るのか、どういった方法が有効であるかを検証していきたいと思います。

Oracle ヒント句を利用したパフォーマンスチューニング 1

図１：検証イメージ

検証内容

今回は格納されているデータ件数の異なるテーブルを用意し、各テーブルを結合するSQL文に対してヒント句を用いることで、SQL実行時の実行計画上のコストに変化が生じるかを検証します。

検証詳細

・サンプルテーブルを用意し、単一/複数表にヒント句を利用してSELECT文を実行
・SI Object BrowserからテーブルへのSELECT時の実行計画を参照しコストの値を測定
・ヒント句によりコストの軽減/増加を体感 [RELATED_POSTS]

検証環境

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

ｻｰﾊﾞ情報	OS	Windows7
	CPU	Intel Core 2 Duo CPU E8400 @3.00GHz
	メモリ	3.00GB
	その他	特に無し
DB	対象RDBMS	Oracle11.0.2.0.1.0
	ﾃｰﾌﾞﾙ情報01	CREATE TABLE "TBL01"( "COL1" NUMBER(10,0) NOT NULL, "COL2" VARCHAR2(500), CONSTRAINT "PK_TBL01" PRIMARY KEY ("COL1") DISABLE);
	ﾃｰﾌﾞﾙ情報02	CREATE TABLE "TBL02"( "COL1" NUMBER(10,0) NOT NULL, "COL2" VARCHAR2(500), CONSTRAINT "PK_TBL02" PRIMARY KEY ("COL1"));CREATE INDEX "OGW"."IX_T2_C2" ON "OGW"."TBL02" ("COL2") VISIBLE;
	ﾃｰﾌﾞﾙ情報03	CREATE TABLE "TBL01"( "COL1" NUMBER(10,0) NOT NULL, "COL2" VARCHAR2(500), CONSTRAINT "PK_TBL01" PRIMARY KEY ("COL1") DISABLE);
	その他	データ件数：TBL01(100件)、TBL02(100万件)、TBL03(30万件)

実行計画とコスト

｢実行計画｣とはSQLの実行に先んじてデータベース内部で行う処理のことであり、例えばSELECT文であれば、複数のテーブルの結合方法や順序、レコードの取得にインデックスを使用するかなどをSQLやデータベース内部の情報を元に決定することを指します。

データベースエンジン内部で実行計画を決める役割を持った部分を「オプティマイザ」と呼びます。11月回にてオプティマイザの説明がありますのでご存知で無い方はご参照ください。

また、実行計画では「COST（コスト）」と言うSQLの処理に要する負荷のことを表す、パフォーマンスの指標となるものがありますが、このコストの単位は秒数ではないことに注意してください。

1コストが実際に何秒に相当するのかはサーバのハードウェアに影響されるため、厳密な計算が難しいのです。ただ、相対的な比較が可能であり、例えば実際のシステムのレスポンス時間が30秒掛かるため、これを3秒にすることを目標にする、つまりコストを1/10にするようにパフォーマンスチューニングすればいいと言うことができます。

基本的にはオプティマイザが自動的に最適な実行計画を作成するのですが、今回は「ヒント句」を明示的に指定することでよりよいパフォーマンスにチューニングできるかを見ていきましょう。

Oracle ヒント句を利用したパフォーマンスチューニング 2

※図：SQL実行画面の「実行計画ボタン」をクリックすると実行計画を表示

パフォーマンスの悪いSQLが特定できれば、SI Object Browserの「実行計画」画面にて現状のパフォーマンスを測定できます。

検証結果

まずはコストが劇的に軽減されることを実感してみましょう。

Oracle ヒント句を利用したパフォーマンスチューニング 3

「FIRST_ROWS(1000)」と言うヒント句がされ、「OPTIMIZER_MODE」が「ALL_ROWS」から「FIRST_ROWS」と言うモードに変化し、コストの値も1/12ほど軽減されていることが分かります。

では、OPTIMIZER_MODEに指定されていた「ALL_ROWS」と「FIRST_ROWS(n)」とはいったいどんなヒント句なのでしょうか。

■ALL_ROWS
SQL文が最適な単位時間当たりの処理量となるようなアプローチをする（初期値）。
→全表スキャン、ソート/マージ結合が選択されやすいことが特徴

■FIRST_ROWS(n)
(n)で指定された行をもっとも効率よく返す計画を立て応答時間を最短になるよう指示をする。
→インデックススキャンとネステッド・ループ結合が選択されやすいことが特徴

上記のような特徴から、例えば「FIRST_ROWS」を利用する場合、抽出対象件数が多い状況であると先頭1000件だけを抽出するのに特化していると言えます。反対に元々のデータが少ない場合には不向きであるとも言えます。

一部ではありますが、その他のヒント句も見ていきましょう。

Oracle ヒント句を利用したパフォーマンスチューニング 4

図：全テーブルがフルスキャンとなってしまった結合

上記のSQL文に対しヒント句を利用してコストを下げて見ましょう。
結合順序指定（LIEADING（テーブル名））、結合操作（USE_MERGE(テーブル名)）、インデックスを明示的に使用するように指示（INDEX(表名インデックス名)）するように指定してみましょう。

Oracle ヒント句を利用したパフォーマンスチューニング 5

結果、コストが増加しましたね。。。

上記SQL文ではオプティマイザがデフォルトの設定値で最適なパフォーマンスとなるよう実効計画を導き出したものに、ヒント句を用いて余計な処理をさせて悪化させた例となります。

結論

現状のコストベースオプティマイザ（CBO）では、ユーザーがあまり意識することなく最適なアプローチを示してくれます。ただ、Oracleのオプティマイザには60をも越えるヒント句が存在しますので、より複雑なSQL文、データ量やハードウェア環境の状況によってはヒント句によるチューニングが大きな効果を出すことも期待できます。

このヒント句をうまく利用することで、オプティマイザが最適化する方法の指定、結合順序の指定、結合操作の指定、インデックスの利用有無の指定、アクセスパス（FULLスキャン指定）など、ヒント句によって実行計画を固定(操作)させることができるので、独自の実行計画を作成してSQLを実行するということも可能になります。

データ量割合が変動しないテーブルの結合では結合順序を固定したい、作成されたインデックスが有効に機能しているか実行計画上から確認したいなどにも有用です。
パフォーマンス改善にお悩みの方は一度お試しいただければと存じます。

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