【SQL】実行計画、実行計画変動リスクなど

実行計画とは？

SQLは、ただ闇雲に実行されるわけではありません。

DBMSの環境に応じて、下記のポイントで、「Oracleオプティマイザ」が、どうすれば最も高速かを分析しプランを立ててから実行に移ります。

これを、SQLの「実行計画」と呼びます。

• 表に、どの順番で実行するか。
• 表にどのような方法でアクセスするか。（インデックスを使うか、それとも一行ずつ地道に調べていくか。）

オプティマイザは、実行計画を立案時に、「共有プール」の「データ・ディクショナリ・キャッシュ」に含まれているデータ（テーブル、権限、索引等）を利用します。

「共有プール」、「データ・ディクショナリ・キャッシュ」という用語については、下記の記事で解説しています。

【Oracleアーキテクチャ】「共有プール」の構造や仕組みについて

オプティマイザとは？

Oracle内部で、問い合わせの処理の最適化を行ってくれる機能のことです。

オプティマイザの動作の仕組みや、設定方法については下記の記事をご覧下さい。

【Oracle】オプティマイザとは？

実行計画の確認方法

EXPLAIN PLAN FORコマンドを使う。

特徴

実行時に、オプティマイザが立てたSQLの実行計画を観察するツール

メリット

インデックスを作成することで、どのくらいSQLが早くなるか目安を得たりするのに便利。

適切な実行計画が作成されるようにするには？

正しい統計情報を集めることです。テーブルのデータが大きく更新されたらカタログの統計情報もセットで更新するようにすることが重要です。

具体例

バッチ処理などで大きくデータ件数を更新する場合は統計情報も更新するような処理を入れておくこと。

なお、Oracleでは定期的に統計情報を自動で更新する仕組みになっていたり、SQL Serverのように更新処理が行われたタイミングで自動で統計情報も更新するようなDBMSもあります。

実行計画変動リスク

実行計画はオプティマイザによって常に変動します。

最も変動しやすいのが複数の選択アルゴリズムを持っている「結合処理」になります。（なお、MySQLはネステッドループしかないので結合の実行計画変動影響は低いです。）

具体的には、リリース当初はレコード件数が少なかったので「ネステッドループ結合」が選ばれていたものが、システムの運用中にレコード件数が増えたことによって「ソートマージ結合」や「ハッシュ結合」が選ばれるようになったなどの具合です。

対策としては

非正規化にする。

これも選択肢の一つかもしれませんね。

ウインドウ関数で相関サブクエリや結合を置き換える。

これも最近のDBMSならではの対策と言えますね。

ヒント句を使ったり、統計情報を凍結する。

MySQLなどは対応してないですが、これも選択肢の一つになるでしょう。