!!!SQLチューニング {{category Oracle,nolink}}性能改善を中心としたSQLチューニングなど。 !!!SQL文の記述 Oracleは、SQLが発行されると、共有プール内に過去に同一のSQLが実行されていないかを確認する。同一のSQLが存在する場合は、SQL文の構文解析(Pares)処理が省略され、実行計画が共有さる。 !!SQL文の記述を大文字(小文字)に統一する 構文解析されたSQL文の命令自体は、大文字と小文字の区別はされない。しかし、構文解析前の状態では、同一内容のSQL文の命令でも大文字と小文字が違う場合は、違うSQL文として処理される。よって、解析処理は省略されず再実行され、共有プールに複数登録される事になる。 プログラムで設定する場合、同一のSQLを複数のプログラムから発行される可能性は低い。しかし、SQL文の記述を統一することで可読性の向上が期待できる。 !!SQL文内の不要なホワイトスペースは削除する 上記の理由と同様に、語句間のホワイトスペースが異なる場合も同一のSQLと見なされない。また、共有プールにはそのまま残されるため、エリアの無駄使いにもなる。 !!動的SQLのバインド変数を使用する 検索・更新条件や挿入・更新の内容を実行毎に、別々に記述したり組み立てると別々のSQL文として構文解析を行う。 バインド変数を使用し、変化する値をバインド変数にセットすることで、同一のSQL文として処理され、構文解析が省略される。 また、バインド変数を使用することで値が変化する箇所が明確になり、プログラムの可読性の向上が期待できる。 !!必要な項目のみを列挙する 不必要な項目が増えることにより余計なI/Oが発生する。また、SELECT文で*(アスタリスク)を指定した場合、*をテーブルの各項目に展開するステップが発生する。 表定義が変更された場合に、該当項目が関係ない物でも影響を受けるためプログラムの変更が必要になる。 !!テーブル名には短い別名を付ける 複数のテーブルを結合した際に、テーブル名には短い別名を付ける事により、どのテーブルの項目から取得しているのがわかりやすくなる。 !!!条件(WHERE句)の記述 必ずしも適用出来る訳ではない。 !!INDEX項目には関数を使用しない WHERE句に指定する際に、INDEXを作成した項目に関数を使用するとINDEXが使用されない。 !!INDEX項目の比較条件にNULLを指定しない INDEXを作成した項目に対してNULL検索(IS NULL,IS NOT NULL)を行うと全件検索になる。 !!INDEX項目の比較条件にNOTを指定しない NOTを使用すると全件検索をした方が早いと判断され、INDEXが使用されない。 !!INDEX項目に対し、中間一致・後方一致の条件を指定しない 前方一致の場合はINDEXを使用するが、中間一致・後方一致の場合には、該当項目がINDEX似合っても使用されない。 !!INDEX項目に対し、範囲検索を行う場合は、上下限を指定する 範囲検索で上限及び下限を指定できる場合は、双方を指定することで、INDEXの無駄な読み込みを減らすことが出来る。 !!連結INDEXは、設定されている順番にあわせる ルールベースの解析の場合は、WHERE句に指定する順番を連結INDEXの順番にあわせる。 !!連結INDEXは、後方一致の検索は行わない 連結INDEXの場合に、最初の項目を指定しないと、INDEXは使用されない。 !!項目比較のデータ型を一致させる 異なるデータ型を比較する場合、暗黙の型変換が行われるため、処理のオーバーヘッドが発生する。固定値書くときなどは、比較先の項目の型に合わせるとよい。 !!完全一致の場合は、LIKE句を使用せず、"="を使用する LIKE句を使用しないことにより、余計な検索を減らす事が出来る。 !!INDEXのない項目の効率的な順序 ルールベースの場合(もしくは、コストベースでヒント句を使用し、ルールベースで実行計画が立てられるようにした場合)、WHERE句の順序にしたがって評価される。Oracleは、ANDキーワードで接続された牽引の付いていない等号条件をボトムアップで評価する。また、ORキーワードはトップダウンで評価する。 したがって、AND条件のWHERE句の記述は、最も負荷の高い順に記述する SELECT … FROM … WHERE 最大負荷の条件 AND … AND 最小負荷の条件 また、OR条件のWHERE句の記述は、最も負荷の低い順に記述する SELECT … FROM … WHERE 最小負荷の条件 OR … OR 最大負荷の条件 !!結合条件使用時の、他の条件指定 !!データの存在チェックをする際は、条件に"ROWNUM=1"を追加する データが存在するかどうかのチェックが出来ればよいのであれば、条件に"ROWNUM=1"を追加することで、１件ヒットした時点で検索処理が終了する。 !!!並べ替え(ORDER BY句)の記述 !!並べ替え時のINDEXの利用 ORDER BY句を構成する全ての列が同じ順序で連結INDEXに含まれており、全てNOT NULL定義がされている場合、並べ替え時にINDEXが利用される。 !!データの絞込み 上記以外の場合は、INDEXを利用しないがWHERE句でデータを絞り、メモリのみでソートができれば、大きなパフォーマンスの低下にはならない !!不要な内部ソートを行わない DISTINCT, GROUP BY, ORDER BY, UNION, INTERSECT, MINUS などは処理の中でソートが行われる(ただし、出力される内容がソートされる保障はない)。そのため、パフォーマンスに影響するので、必要以外は行わないようにする。 !!!INDEXの利用 !!INDEXのマージ(複合INDEX) 単一列からなる牽引が一つの表に複数ある場合、その列がWHERE句の等号条件(=)で指定されていると、INDEXがマージされ複合INDEXになる。ただし、UNIQUE INDEXや連結INDEXはマージされない。 !!連結INDEXの条件指定 複数の列からなる連結INDEXをWHERE句で利用するとき、列の順番によって使われ方が異なる。 !!複合INDEXと連結INDEXの選択 マージ処理が行われる複合INDEXの方が柔軟性に優れ、条件を複数指定する検索画面等の用途に向いている。しかし、一般的に連結INDEXの方がレスポンスが優れているため、定型の検索画面(検索条件が決まっている)の場合は、連結INDEXを使用することが望ましい。 !!INDEX検索よりも全件検索が早い場合 Oracleのブロック構造およびデータベースを格納するDISK(iStorage)の機能上、INDEXで検索するよりも、全件検索をしたほうが早い場合がある。 *SQL文の条件により、ヒットするレコード数が全体に対する割合が高い場合 *データ種類の低いカラム列を指定する場合。ただし、比率が極端に違う場合はINDEX検索が早くなる !!!SQLコーディング/設計 !!複数のSQL文を一つにまとめる SQL文を発行する毎にOracleでは多くの内部処理が行われる。複数のSQLをまとめる(発行回数を減らす)ことができれば、事前処理等を減らせれる。また、ネットワークの負荷も軽減でき、性能改善に有効である。 パラメーター類を必要の都度に取得するのでなく、処理の最初に一括して検索する等、複数のSQLをまとめれるように工夫をする。 !!INSERT… SELECT… による、データの一括挿入 ある表から、別の表にデータを挿入するような場合で、INSERT…SELECT…が利用できる場合は、利用して一括挿入を行うことで性能改善が望める。 !!SELECT… FOR UPDATE と ROWID を利用しデータ更新 ある条件でレコードを抽出し、そのレコードに対して更新を行う場合、FOR UPDATE句を利用し排他制御を行う。また、抽出時にROWIDを取得し、更新時にROWIDを指定して更新を行う。 また、データを作成し登録先のテーブルに同一キーが存在する場合はUPDATE、ない場合はINSERTと言う場合も同様に行う。 !!EXIST(NOT EXIST)句の利用 IN(NOT IN)句の中で副問い合わせを行っている場合、EXIST(NOT EXIST)句に置き換えることにより、性能改善できる。 ""'''注意''' ""IN(NOT IN)句とEXIST(NOT EXIST)句の意味が同じではない。 ""同様の結果が望めるだけであり、必ずしも、置き換え出来るわけではない。 !!データ件数は、COUNT(*) で取得する 一般に、COUNT(*)で行うほうが、COUNT(1)で行うよりも1〜2割高速である。 また、単純に、COUNT(*)では、全件検索になってしまうので、INDEXを利用するようにする必要がある。 +INDEX_FFSヒント句を記述し、使用するINDEXを指定する +上記で指定したINDEXを使用するように、全件が対象となる条件を指定する SELECT /*+ INDEX_FFS(tb, tb_pk) */ COUNT(*) FROM tb WHERE KEY1 > ' '; !!表の結合はSQL(DB)で行う 表の結合は、アプリケーション側のロジックで行わず、SQL(DB)で結合して取得する。ただし、テーブルの結合のしすぎには注意する。 !!表データの削除方法 表データの削除方法には、DELETEとTRUNCATEの2種類がある !DELETE 特定のデータを消す場合に使用する。しかし、大量のデータを削除する場合削除情報が、UNDO領域(ロールバックセグメント)に保存されるため、レスポンスの低下、ロールバックセグメントの不足等によるエラーが発生する可能性がある。 大量データ削除による、ロールバックセグメント不足のエラーを回避するには、一定の間隔(EX.千件単位)でコミットを行うようにする。 !TRUNCATE 全件が対象で、かつ、ロールバックの必要がない(ワークテーブルのクリア等)場合は、TRUNCATEを利用するほうが高速である。しかし、ロールバックをすることが出来ないため、使用する際は、慎重に行う必要がある。 !!ヒント句の利用 ヒント句を指定し実行計画の作成内容(INDEXの利用など)を指定することが出来る。ただ、最適化の処理をOracleにまかせ自動化することで、生産性の向上、環境変化に応じた最適化を行うことで性能を向上させるため、実行計画に問題がある場合以外は利用しない。 実行計画をコストベースで行っている場合、ANALYZEコマンドによる統計情報を利用して最適な実行計画を立てている。しかし、開発時や本番稼動直後など、テーブル内のデータによって、INDEX検索より全件検索のほうが早いと判断される場合がある。 !!NULL値の排除 ある項目がNULLの場合、検索する際にIS NULL句を利用したり、INDEXが適切に利用されなかったりする。業務用件により異なるが、文字列はスペース、数値は0をセットするなど、明示的に値をセットし、NULLはセットしないようにする。 ただし、完全にNULLを排除する必要はない。テーブル定義で全てにNOT NULL制約をつけるのは間違いである。 !!!SQL文の可読性 第三者が保守する場合はもちろん、書いた本人であっても時が経つにつれて忘れてしまうものである。SQL文に限らずであるが、見やすいSQL文を書くことを心がける必要がある。 !!演算子の優先順位 SQL文の演算子にも優先順位が存在する。算術演算子は、乗算・除算、加算・減算。論理演算子は、NOT、AND、ORの優先順位になる。間違いの防止や可読性の向上のためにも明示的に括弧を付けるようにする。 !!適切な改行を行う 長くなりやすいWHERE句等は適切な改行をいれ見やすくする。 !!適切なコメントを付加する 適切なコメントを付与し何をしているかなどをわかりやすくする。