こんにちは。SI部の吉原です。
システム開発において、パフォーマンス(処理速度)に悩まされる事があるかと思います。
パフォーマンス改善方法は色々とあると思いますが、今回はOracle Databaseにおけるちょっとした小技で
パフォーマンスが改善される方法を紹介させていただきます。

オプティマイザ

SQLが実行される際、必ず「オプティマイザ」というOracle Databaseの機能が実行されます。

さて、この「オプティマイザ」とは一体何なのでしょうか?

それは、SQLが実行される際にはSQLの解析を行い、問い合わせの実行時間を最小化するような処理方法を判断し、
その判断結果に応じた「実行計画」を以下のような構成で作成する機能となります。


1. 問合せの変換(Query Transformation)

SQL文を論理的に同じ意味を持たせたまま、SQL文自体をより効率的な文への変換をします。
これにより、結合対象のテーブルへフィルタリング条件を追加し、テーブル探索コストの削減などが行われます。

2. アクセス・パス解析(Access Path Analysis)

各表へのアクセス方法(Access Path)や表同士の結合方法(Join Method)、結合順序(Join Order)の選択を行います。
これにより、テーブルのデータ量に応じた最適なアクセス方法や結合方法が選択され、データの取り出し経路が導かれます。


オプティマイザが、実行計画をたてるまでの時間を Oracle Database では Compile Time と呼ばれています。
SQLを最適化し、Oracle Databaseが実行可能な形式に変換するという意味では、 一般的なプログラミング言語でのコンパイラと同じような役割を担っているとも言えます。

オプティマイザは、問い合わせをどのように処理をするかを決める機能であるため、しばしば RDBMS の頭脳とか心臓部とか言われたりもします。
オプティマイザの性能一つで、パフォーマンスが何十万倍も違うという事もありえるため、
その重要性から、オプティマイザは Oracle Database が世に出て何十年も経った今でも日々進化しています。

オプティマイザの落とし穴

しかし、オプティマイザは必ずしも最適な実行計画をたてられるという事を保障しておりません。
なかには、パフォーマンスの悪い実行計画をたててしまう事もありえます。

前述にもある通りオプティマイザは日々進化しているため、アップグレードが行われています。
そのバージョンが異なると、同じSQL、データ量であっても、異なる実行計画がたてられる事もあります。
また、最新バージョンのオプティマイザが必ずしも性能が良いという事はないため、
オプティマイザのアップグレードを行った結果、一部機能のパフォーマンスが落ちてしまった・・・
といったような事も起こりえます。
このような時は一体どのようにすれば良いでしょうか?

ヒント句で解決

Oracle Database はヒント句を使用する事で、
全表スキャンや索引スキャンといったアクセス方法や結合方法/順序などを、オプティマイザに明示的に指示できます。
このヒント句を使えば、実行計画の一部を思うように指定することが可能となるので、性能劣化の改善策として利用できます。
今回は、オプティマイザのアップグレードに伴う性能劣化について簡単に解決できるヒント句を紹介したいと思います。


OPTIMIZER_FEATURES_ENABLE

このヒント句を使用する事で、SQL単位でオプティマイザのバージョン指定を行う事ができます。
これによりオプティマイザのアップグレードに伴い性能劣化した機能については、 アップグレード前に使用していたバージョンを指定する事で簡単に改善が見込めます。
また、ヒント句を使用するにあたって実行計画の問題点の調査が必要ですが、これであれば調査を行わずとも改善する事が可能となります。

使用例

WITH view01 AS (
  SELECT
    col_01
  FROM
    tbl01
),
view02 AS (
  SELECT
    col_02
  FROM
    tbl02
)
SELECT
  /*+ OPTIMIZER_FEATURES_ENABLE('11.1.0.1.1') */
  col_01,
  col_02
FROM
  view01, view02
WHERE
  view01.col_01 = view02.col_02(+)
;

・With句などの副問い合わせの存在するSQLであっても、主となるSELECT句にて指定します
・指定できるバージョンは、マイナーバージョンまで細かく指定できます


OPTIMIZER_FEATURES_ENABLE は、アップグレード後もオプティマイザの以前の動作を保持できることを主な目標として導入されました。
例えば、バージョン[11.1.0.1.1]を指定すると、以降に追加された機能は全て無効となり、 [11.1.0.1.1]までにリリースされた機能のみを利用して実行計画をたてることができるようになります。
その結果、アップグレード前と同様の実行計画をたてることが保障されます。

OPTIMIZER_FEATURES_ENABLE は、設定されているオプティマイザバージョンと相性が悪い一部SQLに対する改善策としてはヒント句一つで簡単に対応できるため有効である方法ではあると思います。

まとめ

今回は、オプティマイザに焦点をあてたちょっと特殊なパフォーマンス改善方法の一例を説明させていただきました。
パフォーマンス改善には、ヒント句によるSQLへの対応のほか、プログラムやデータベースに対するものなど様々な方法があります。
機会があれば、別のパフォーマンス改善方法についても紹介させていただければと思います。