突然のSQLパフォーマンスダウンを防ぐためのバッチ処理SQLリテラル化のススメ

またまた前回の更新から時間が空いてしまいました・・・。
いろいろと忙しく時間が取れないのですが、このままだと2010年まったくblogを書いていないことになってしまうのと、
twitterで後押しをいただいたこともあり、何とか書き上げました。お役に立てば幸いです。

### はじめに

最近、Oracle Databaseのパフォーマンスに関連するトラブルシューティング
を担当することが多いのですが、特に多い事例が、
ある特定のSQLのパフォーマンスが突然ダウンするような問題です。

原因はいろいろなのですが、バッチ処理で実行されるSQLに、バインド変数
を使用しているためにパフォーマンスが突然ダウンしてしまった状況が多くみられました。
（もちろんケースバイケースの側面はありますが、）バッチ処理で実行されるSQLは
一般的にバインド変数化するべきではありません。
SQLをバインド変数化することにはメリットもありますが、デメリットもあります。
特に、デメリットの部分が広く知られていないため、プログラムをコーディング
するときに、開発者がある種機械的・盲目的にSQLをバインド変数化していることが
この問題の背景にあるのではと思っています。

本エントリでは、SQLをバインド変数化のメカニズムと
SQLをバインド変数化することのメリットとデメリットを説明した上で、
バッチ処理で実行されるSQLをリテラルSQL化することをお勧めします。

### SQLのバインド変数化とは何か？

SQLのバインド変数化とは、SQLのWHERE句に含まれるリテラルを変数化することです。
たとえば、SQL*PlusでSQLを実行するケースを例にすると、

SELECT * FROM EMP WHERE empno = 1000; — (*1)

というSQLには、SQLにWHERE句に”1000″というリテラルが含まれます。(*2)

このリテラルを”empno”という変数に置き換えることで、バインド変数化する
ことができます。置き換えた変数に対してあらかじめ”1000″という値を
設定しておくことで、上記のリテラルを含むSQL(*1) と同等のSQLを実行する
ことができます。

variable empno=1000;
SELECT * FROM EMP WHERE empno = :empno;

なお、上の説明では、SQL*PlusでSQLをバインド変数化した場合を例にしていますが、
SQLをバインド変数化する方法は、SQLの実行形態（端的にはどのプログラミング
インタフェースを使用するか）によリ異なります。
たとえば、JDBCを使用してSQLを実行する場合は、preparedStatementオブジェクト
を使用してSQLをバインド変数化します。

PreparedStatement pstmt
= conn.prepareStatement(“SELECT * FROM EMP WHERE empno = ?”);
pstmt.setInt(1, 1000)

* (*2) 本稿では、バインド変数化されていない、リテラルを含むSQLをリテラルSQLと呼ぶことにします。

### バインド変数化するメリットは？

SQLをバインド変数化すると以下の点で処理効率の向上が期待できます。

1. 共有プールのメモリ使用量を削減できる
2. SQLの解析処理(ハードパース)の実行を回避することで、処理時間を短縮し、CPU使用量を削減できる

処理効率が向上できるメカニズムを説明しましょう。

Oracleでは、SQLを実行すると、1つのSQLに対して1つ、共有カーソルと呼ばれる
データ構造を作成し、共有プール内にキャッシュします。

共有プール
SELECT * FROM EMP WHERE empno = 1; ──→ 共有カーソルA
SELECT * FROM EMP WHERE empno = 2; ──→ 共有カーソルB
SELECT * FROM EMP WHERE empno = 3; ──→ 共有カーソルC

したがって、多くの種類のSQLを実行するシステムでは、
共有カーソルが共有プールの多くの領域を使用する傾向を持ちます。

ところが、SQLをバインド変数化すると、検索条件が異なる複数の検索処理を、
1つのSQLで実行でき、共有カーソルの数を減らすことができるため、
共有プールの使用量を削減することができます。

variable empno=1　　　　　　　　　　　　　　　　共有プール
SELECT * FROM EMP WHERE empno = :empno;　 ─┬→　共有カーソルX
variable empno=2　　　　　　　　　　　　　　│
SELECT * FROM EMP WHERE empno = :empno;　 ─┤
variable empno=3　　　　　　　　　　　　　　│
SELECT * FROM EMP WHERE empno = :empno;　 ─┘

また、検索条件が異なる複数の検索処理において、1つの共有カーソルを
使いまわす形になるため、共有カーソル生成時に実行される処理である
ハードパースの処理回数を削減することができます。
ハードパースの代わりに、負荷が軽いソフトパースが実行されます。

実行される処理
SELECT * FROM EMP WHERE empno = 1; ハードパース → SQL実行
SELECT * FROM EMP WHERE empno = 2; ハードパース → SQL実行
SELECT * FROM EMP WHERE empno = 3; ハードパース → SQL実行

variable empno=1 実行される処理
SELECT * FROM EMP WHERE empno = :empno; ハードパース → SQL実行
variable empno=2
SELECT * FROM EMP WHERE empno = :empno; ソフトパース → SQL実行
variable empno=3
SELECT * FROM EMP WHERE empno = :empno; ソフトパース → SQL実行

ハードパースは、多くのCPU時間を使用する重い処理であるため、
ハードパースの処理回数を削減することは、処理時間の短縮、CPU負荷の軽減に有効です。

このように、SQLのバインド変数化には処理負荷軽減の観点で2つのメリットが
ありますが、メリットの恩恵を得るには、リテラル値のみが異なる多くの種
類のSQLを実行する状況に限定されることに注意してください。

たとえば、以下のようなSQLが実行される場合は、バインド変数化する意味が
ありますが、

SELECT * FROM EMP WHERE empno = 1;
SELECT * FROM EMP WHERE empno = 2;
SELECT * FROM EMP WHERE empno = 3;

以下のようなSQLが実行される場合は、バインド変数化する意味がありません。

SELECT * FROM EMP WHERE empno = 1;
SELECT * FROM EMP WHERE deptno = 2;
SELECT * FROM EMP WHERE ename = 3;

このようなSQL(のみ)が実行される場合、バインド変数化しても、共有プールの
メモリ使用量は削減できませんし、ハードパースの実行回数も削減することは
できません。

### バインド変数化するデメリットとは？
共有カーソルには実行計画が含まれます。
バインド変数化すると、検索条件が異なる複数の検索処理において、
1つの共有カーソルを使いまわす形になり、結果として同じ実行計画が使用され(*3)、
意図しないパフォーマンスダウンをもたらす可能性があります。

* (*3) 共有カーソルが共有プールからage-out(キャッシュからpurge)されるまで、
1つの共有カーソルが使いまわされます。
カーソルの共有条件にはいくつかの例外・条件はありますが、ここでは割愛します。
気になる人は リファレンスマニュアルのV$SHARED_CURSORSを調べて見てください。

同じ実行計画を使用する動作は、OLTP系の処理などでよく見られる、
主キーをWHERE句に指定するSQLの場合は問題とはなりません。

SELECT * FROM ORDERS WHERE order_no = :order_no;

なぜなら、主キーをWHERE句に指定している場合、どのようなバインド変数値が指定された
場合でも、インデックスアクセスを使用した実行計画のみが適切な実行計画であるためです。
このため、バインド変数値が異なる複数の検索処理で同じ実行計画が使用されても問題になりません。

しかし、バッチ処理やデータウェアハウス系の処理などでよく見られる
WHERE句の指定が複雑なSQLの場合は問題となりえます。

SELECT * FROM ORDERS WHERE color = :color
AND price > :price
AND :start_date < sold_date AND sold_date < :end_date : このようにWHERE句の指定が複雑である場合、指定されたバインド変数値 によって、適切な実行計画が異なる可能性があります。 たとえば、バインド変数値の指定がパターンXの場合は索引A, B, Cを 使った実行計画が適切であり、パターンYの場合はテーブルスキャンを 使った実行計画が適切であるなどです。 このような状況において、バインド変数値が異なる複数の検索処理で、 同じ実行計画が使用されてしまうと、 バインド変数値にある値を指定した場合は、高速に処理が実行されるが、 別の値を指定した場合は、処理が極端に遅いような状況が発生しえます。 先の例で言えば、バインド変数値の指定がパターンYの場合に、 テーブルスキャンではなく、索引A, B, Cを使った実行計画を使用してしまい、 処理パフォーマンスが大幅に遅くなる状況に相当します。 ### SQLパフォーマンスが突然ダウンするメカニズム WHERE句の指定が複雑である場合、指定されたバインド変数値によって、 適切な実行計画が異なる可能性が高いことはわかっていただけたと思います。 ではなぜ、SQLの処理パフォーマンスが突然ダウンするような事象が発生する のでしょうか？ これは、共有プールにおける共有カーソルの扱いに起因します。 バインド変数値の指定がパターンXの場合は索引A, B, Cを 使った実行計画が適切であり、パターンYの場合はテーブルスキャンを 使った実行計画が適切である状況を例にして、パフォーマンスダウンの 動作メカニズムについて説明します。 #### 1. バインド変数値にパターンXを指定してバインド変数化されたSQLを実行 → 索引A,B,Cを使った実行計画をもつ共有カーソルが作成され、 共有プールにキャッシュされる (*4) (バインド変数値にパターンXを指定) 共有プール SELECT * FROM ORDERS WHERE …; ──→ 共有カーソルA 索引A,B,Cを使った実行計画 #### 2. バインド変数値にパターンYを指定してバインド変数化されたSQLを実行 → テーブルスキャンを使った実行計画が最適であるが、 共有カーソルAが共有プールにキャッシュされているため、 共有カーソルAを使用する。 → SQLは索引A,B,Cを使った実行計画で実行され、パフォーマンスダウンが発生 (バインド変数値にパターンXを指定)　　　 共有プール SELECT * FROM ORDERS WHERE …;　 ─┬→　共有カーソルA 　　　　　　　　　　　　　　　　　　│　　　 索引A,B,Cを使った実行計画 (バインド変数値にパターンYを指定)　 │ SELECT * FROM ORDERS WHERE …;　 ─┘ * (*4) バインド変数化されたSQLに対して実行計画を作成する場合、 デフォルトではSQLに指定されたバインド変数値を考慮します。 この機能はバインドピークと呼ばれ、デフォルトでONになっています。 ただし、バインド変数値の指定がパターンXの場合は索引A, B, Cを 使った実行計画が適切であり、パターンYの場合はテーブルスキャンを 使った実行計画が適切である状況であれば、パフォーマンスダウンが必ず 発生するわけではありません。 上記と同じ順番でSQLを実行しても、パターンYでSQLを実行する前に 共有カーソルAが共有プールからage-out(キャッシュからpurge)された 場合、パフォーマンスダウンは発生しません。 #### 1. バインド変数値にパターンXを指定してバインド変数化されたSQLを実行 → 索引A,B,Cを使った実行計画をもつ共有カーソルが作成され、 共有プールにキャッシュされる (バインド変数値にパターンXを指定) 共有プール SELECT * FROM ORDERS WHERE …; ──→ 共有カーソルA 索引A,B,Cを使った実行計画 #### 2. 共有カーソルAが共有プールからage-out 共有プール 共有カーソルA → age-out #### 3. バインド変数値にパターンYを指定してバインド変数化されたSQLを実行 → テーブルスキャンを使った実行計画をもつ共有カーソルが作成され、 共有プールにキャッシュされる → パフォーマンスダウンは発生しない。 (バインド変数値にパターンYを指定) 共有プール SELECT * FROM ORDERS WHERE …; ──→ 共有カーソルB テーブルスキャンを使った実行計画 このように、実際にパフォーマンスダウンが発生するかしないかは、 共有プールにおける共有カーソルのキャッシュ状態に依存します。 しかし、アプリケーション運用の担当者、エンドユーザーは、 共有カーソルのキャッシュ状態を意識して運用している可能性は極めて低いですから、 突然パフォーマンスダウンが発生したように見えるわけです。 ### ではどうすれば？→リテラルSQLを使用しましょう。 上記の例のような、WHERE句の指定が複雑なSQLを実行したい場合、 バインド変数化されたSQLではなく、リテラルSQLとしてSQLを作成すべきです。 リテラルSQLを使用すると、検索条件が異なるSQLは別々のSQLとみなされるため、 リテラルSQLごとに実行計画が作成され、 最適でない実行計画が選択される可能性を減らすことができます。 もちろん、リテラルSQLを使用すると、先に説明したバインド変数化された SQLを使用する時の以下のメリットの恩恵にあずかれませんが、 1. 共有プールのメモリ使用量を削減できる 2. SQLの解析処理(ハードパース)の実行を回避することで、処理時間を短縮し、CPU使用量を削減できる そもそも、このメリットの恩恵に預かることができるのは、 リテラル値のみが異なる多くの種類のSQLを実行する場合です。 一般にWHERE句の指定が複雑なSQLが実行される状況である、 バッチ処理やデータウェアハウス系では、リテラル値のみが異なる 多くの種類のSQLが実行されませんので、このメリットの恩恵を受けにくい 状況にあり、リテラルSQLを使用しても問題がないといえます。 ### 補足コメント 本エントリの内容は、10.2以前で有効です。 11.1より導入されたACS(優れたカーソル共有)についての考慮が漏れているため、時間を見つけて別のエントリを作成する予定です。