L
o
a
d
i
n
g
.
.
.

ホーム

お知らせ

製品・ソリューション

サービス

導入事例・パートナー

EXEM Academy・ブログ

会社情報

採用情報

2021.11.10

SQLチューニング(第18回)「WITH節理解と効率的なSQLの作成」(3/3)

今回は、「WITH節理解と効率的なSQLの作成」に関しての三回目(最終回)となります。では、今回のお話を始めます。


4.1.3 WITH節を使う時、注意しなければならない点は?

4.1.3.1 同時性が高い場合MATERIALIZE動作方式は避けよう

 With節がMaterialize動作方式で実行されるSQLをモニタリングをしてみると、control file sequential readというOracle Wait Eventが頻繁に発生するのを見ることができる。 このイベントはDBサーバーのコントロール ファイルを読む時発生するWait Eventである。 With節を処理する過程でMaterialize動作方式はGlobal Temporary TableにWith節のSQLで抽出したデータを保存しし、この保存されたデータを読んで処理しなければならないのでコントロール ファイルを読むことになる。

 したがって同時性が高いプログラムに性能を改善する目的でWith節を使ってMaterialize動作方式を適用するならば、かえってcontrol file sequential read待機イベントを待機するセッションが急増してかえってDBサーバー性能に悪影響を与える恐れがあるので注意しなければならない。

4.1.3.2 抽出件数が多い場合WITH節は避けよう

 SQLに同じデータを繰り返して抽出する部分があって、1回実行コストがたくさん発生する場合、性能問題が発生する可能性がある。 このような場合、非効率を除去するためにWith節を活用した。 しかし、With節に明示したSQLの抽出結果が多いならば、かえってWith節を使わない方がより良いこともある。 なぜなら、With節がMaterialize動作方式で使われる場合、With節で抽出した多くのデータをGlobal Temporary Tableに保存しなければならなく、またWith節のデータを読まなければならない時も多くのデータを処理しなければならないので、With節を使っても性能が大きく改善にならないこともあるためである。

 With節は同じデータを反復問い合わせる時できる非効率を除去できる良い方法である。 しかし、むやみに使えばかえって逆効果が発生する可能性がある。 したがってWith節は同じデータを反復照会時1回処理するには多くの時間が必要とされるが、抽出結果がGlobal Temporary Tableに対する書きおよび読み取り作業のコストが少ない場合に限って使うことが望ましい。.

4.1.3.3 WITH節宣言は、SQLの最も前に位置させよう

 Oracle 10.2.0.4バージョンでSQLの性能を改善するためにWith節を利用したSQLで再作成して、性能は良くなったがデータ整合性が毀損される問題を体験した経験がある。 原因を探している間SQL[2]とともにWith節をSQLの最も前に明示をしたところ、データ整合性問題はこれ以上発生しなかった。 したがってSQL作成時With節宣言部を必ずSQLの一番前に明示しなければならないと考える。

SQL[1]. 誤ったデータが照会されたSQL方式

    select *
      ...
      select *
        ...
        with ~ as (
           ...

SQL[2]. 正常データ照会されたSQL方式

    with ~ as (
      ...
    select *
      ...

4.1.3.4 WITH節に動作方式ヒントを追加しよう

 With節を使う時必ずその動作方式に対応するヒントを追加しなければならない場合がある。 SQLの構造が変更されたといっても、実際の実行される動作方式は同一だったり性能改善の目的と対応しない動作方式で実行されることができるためである。 “階層クエリーのデータ処理最小化すること”で/+ MATERIALIZE/ ヒントを適用した時のようにSQLの性能改善のための動作方式に誘導するためには必要である。

 今回、私たちはWith節の基本動作方式とWith節を活用した性能改善方法に対して調べてみた。 最後に一つ強調したい点は、いくら良い薬でも乱用すれば毒になるようにWith節も、やはり動作方式と性能の利点を考慮しないでむやみに使うならば、SQL性能はもちろん、DBサーバーの性能に悪影響を与える恐れがある危険がかくれているので、先に対象になるSQLの性格を正確に把握した後慎重に使わなければならない。


 いかがでしたでしょうか。今回でWith節関連は終了し、次回からは、「MERGE構文理解と効率的なSQLの作成」のお話になります。よろしく、お願いします。では、See You ^^


チューニングのための詳細情報取得ならMaxGauge