2023.02.14
SQLチューニング（第43回）「ROWNUMの理解と関連SQL性能問題の理解」（3／6回）
今回は、「ROWNUMの理解と関連SQL性能問題の理解」の3回目です。それでは、始めましょう。
12.3 ROWNUM=1は、ROWNUM<=1で使おう
Bind変数を使用しないため、Literal SQLが過多に実行されるDBサーバーがあり、これによってすでにDBサーバーの性能問題は深刻なほどだと仮定しましょう。 このような場合DB管理者はLiteral SQLを除去して、ハード解析を最小化しようと努力するでしょう。 この時、最も有効な方法はプログラム ソースをいちいちBind変数に変更することかもしれませんが、ソースを変更することが不可である場合や、DBサーバーの状態が深刻でプログラムソースを修正する時間がない状況ならば、最も容易にLiteral SQLをBind SQLに変更できる改善案でCURSOR_SHARING (パラメーター)の設定値変更を考慮するでしょう。 ところでこの時ROWNUM =1とROWNUM <= 1の作成方式により性能に違いが発生する可能性があります。 Literal SQLを減らすためにパラメーターを変更するのとROWNUMが何か関係があるのか不思議に思うかもしれません。 これに対して説明していきましょう。
SQL作成時条件に合うデータ中ただ一件だけ抽出しようと思う時、私たちはROWNUM = 1あるいはROWNUM <= 1条件を使用します。 二つのうちどれを使っても結果と性能が同一です。
しかしCUSOR_SHARINGパラメーター値をFORCE (またはSIMILAR)に変更する場合、ROWNUM = 1という条件は私たちが意図したこととは違うように実行されて性能問題が発生する可能性があります。
理解を助けるために以下で詳しく調べてみましょう。
- [CURSOR_SHARING = FORCE or SIMILARを適用した場合の動作差]
(1) ROWNUM = 1 —> ROWNUM = :”SYS_B_0″
CURSOR_SHARING=FORCE または SIMILAR を適用すると、ROWNUM=1 は ROWNUM=:”SYS_B_0″ などの定数処理部分を強制的に Bind 変数に変更します。 その後、変数値としてどの値が入力されるか分からず、変更前は条件を満たす１件が抽出されると実行を停止しましたが、変更後は全データを検索してから１件を抽出する方式で行われるのです。
部分範囲処理で素早く1件を抽出して実行を終了しようとした、ROWNUM = 1条件はCURSOR_SHARINGをFORCEやSIMILARに変える場合、本来の役割を果たせず、全データをすべて抽出した後、1件をインポートする形で 実行されるため、性能に悪影響を及ぼす可能性の要因となります。 このとき、SQL実行計画のOPERATIONはCOUNTと見なされ、STOPKEYが提供されていないため、すべてのデータを処理した後、ROWNUM条件を実行します。
(2) ROWNUM <= 1 —> ROWNUM <= :”SYS_B_0″
反面CURSOR_SHARING=FORCEまたはSIMILARを適用すればROWNUM <= 1はROWNUM <=:”SYS_B_0″に変更されますが、“SYS_B_0”値がどんな値なのか分からないが、”SYS_B_0″値より小さかったり同じ値という範囲(STOPKEY)が決まるので”SYS_B_0″変数の値を確認して小さかったり同じデータを抽出します。 したがって全体データを処理しないで、1件だけ抽出してSQLの実行は終了します。
この時、実行計画のオペレーションはCOUNT STOPKEYです。 COUNT STOPKEYオペレーションは:”SYS_B_0″値に会えば、STOPKEYが発動してSQLの実行を中断して部分範囲処理ができるようになります。
(1),(2)を通じてROWNUM =1とROWNUM <= 1の差を調べてみました。 二つの条件に変数処理しないで定数で直接入力して処理するならば、結果と性能は同一でしょう。 しかしCURSOR_SHARING設定値を変更すれば、ROWNUM = 1条件の定数値1がBind変数に変更されて、これによって部分範囲処理で実行したSQLが全体範囲処理で実行されて深刻な性能問題が発生する可能性があります。 したがってSQLにROWNUM条件を追加する時できるだけROWNUM = 1よりはROWNUM <= 1で作成する習慣を持たなければならないのです。
「ROWNUMの理解と関連SQL性能問題の理解」の第3回目は、いかがでしたでしょうか？次回第4回目は、「NDEX_DESCとROWNUM<=1を共に使うのをやめよう」です。では、その時まで、See You ＾＾
