SQLチューニングの備忘録

前置き：SQLチューニングをまとめてみました。メモですので、間違いがあるかもしれません。

SQLチューニングは、処理時間・アクセスする必要のあるデータ・ブロック数を減らすことを目的とします。
結果、データベース・バッファ・キャッシュがより効率的になり、キャッシュ・ミスの場合でも必要なデータ・ファイルへのI/Oは小さくなる。システム負荷を低減することが可能です。

用語：オプティマイザとは、表へどのような経路でアクセスし、どのような順番、方法で結合するか実行計画を効率的に決定するのがオプティマイザの役割。Oracle 10gからはコストベースのオプティマイザのみがサポート対象。
統計情報は、DBMS_STATSパッケージもしくはANALYZEコマンドで取得します。

＜レコードアクセス方法＞

１．全表スキャン
２．ROWIDスキャン
３．索引スキャン
がある。
索引スキャンでは、「索引ブロックの読み込み＋データ・ブロック」の読込みとなる。
検索したいレコード件数が、レコード全体の5～15％程度までの場合は、索引スキャンの方が効率的といわれています。

＜結合方法＞

１．ネステッド・ループ結合
ネステッド・ループ結合を効率化するためには、レコード数がより少ない方を外部表とする。
レコード数に大差がない場合には、結合条件列の索引スキャンがより効率的な方を内部表とする。

２．ソート／マージ結合
結合対象が多く、なおかつ結合条件が等価条件ではない場合に使用する。

３．ハッシュ結合
結合条件に等価条件が指定され、大量のレコードなど表の大部分を結合する場合に有効な結合方法。

＜SQLチューニングが必要なもの＞

１．1実行当たりの実行時間が長いSQL
２．ディスク読み取りブロック数が多いSQL
３．バッファの読み取り数が極端に多いSQL
４．実行回数が極端に多いSQL

＜対象SQLの取得方法＞

動的パフォーマンスビューより、取得します。
主にV$SQL、V$SQL_TEXT、V$SQL_PLANの3つとなります。共有SQL領域に保持されているSQLの情報を表示します。
V$SQL_TEXTを参照することで完全なSQLを取得することが可能。

↓全文を取得するSQLの例

set pages 100 feed off timing off echo off lines 140
SELECT sql_text
FROM v$sqltext
WHERE hash_value=？
and address='？'
ORDER BY piece;

＜SQLの記述を統一する＞

実行されるたびに条件の値が異なるSQLを実行しているアプリケーションでは、
リテラル値部分を変数化し、SQLの記述を統一する。

＜SQL対処例＞

■NULL値の検索
‐列名 IS NULL ‐NULL値を別のデータに置き換える
‐ビットマップ索引を使用する

■暗黙の型変換
‐比較するデータ型を列のデータ型に合わせる

■索引列に対して、関数や算術を実施
‐関数索引を使用する（Oracle 9i以上で使用可能）

■LIKEの中間一致、後方一致
‐できるかぎり使用を控える

■!=、<>の使用
‐inで置き換える（可能な場合）

★複合索引の有効利用は、検索性能を向上させるうえで、利用できる機会が多い。

■件数の多い表同士を結合し、全レコード出力する場合
ネステッド・ループ結合→不向き
ソート／マージ結合→結果を結合列でソートして出力する場合に有効。
双方の結合列にNOT NULL制約が指定されており、索引が存在する場合、非常に効率的
ハッシュ結合→システム・リソースに余裕がある場合には最適

■一方の表に絞り込み条件を指定して表を結合し、少数のレコードを出力する場合
ネステッド・ループ結合→目安として索引を使用して表の15％以内の絞り込みであれば最適
ソート／マージ結合→不向き
ハッシュ結合→目安として索引を使用して表の15％以上の絞り込みで、なおかつ等価条件があれば使用を検討

★結合条件が等価条件でないためにハッシュ結合を行えない場合以外は、大量の結合処理では、まずハッシュ結合！

■更新系
‐MERGE文を利用する
‐ダイレクトロードインサートを利用する
‐パラレルDMLを利用する

★パラレルDMLを利用するには以下の手順で設定が必要

1．初期化パラメータ「PARALLEL_MAX_SERVERS」に適切な値を設定する

2．パラレルDMLを実行するセッションでパラレルDMLの利用を可能にする
SQL> alter session enable parallel dml;

3．パラレルDMLのSQLを実行する

SQL> DELETE FROM lineitem;

↓確認

SQL> SELECT table_name,degree

  2  FROM user_tables

  3  WHERE table_name='LINEITEM';



TABLE_NAME                     DEGREE

------------------------------ --------------------

LINEITEM                                1

DEGREEが「1」の場合、パラレル度が設定されていません。

4．パラレルDMLの実行を確認

SQL> SELECT * FROM v$pq_sesstat;

＜その他＞

・駆動表を意識してSQLを書く
・NOT IN ではなく NOT EXIST を使う
・where 条件には　'%値%' はなるべく使わない
・EXPLAINやTRACEの使い方
・Where句の解析順序とインデックスの作成順
・Like文の使い方の注意
・Where句の左辺での関数使用の禁止
・ヒント文の使い方に注意
・外部結合しまくりのSELECT句はできるかぎり書かない。
・FROM句にテーブル名をいっぱい書かない。
・半角カタカナ使わない。
・GOTOは使わない。
・暗黙カーソルでの問合せは使わない。
・SQL構文は、すべて大文字で記述したほうがパフォーマンスがよくなる。
また、PL/SQLについてもすべて大文字で記述したほうがよい。

以上。