Oracleの実行計画について学んだことをまとめる

SQL, Programming, DB13 November 2020

Oracleの実行計画について、実務を通して学んだこと。速度・性能改善に役立つことをまとめていきます。

実行計画とは

実行計画はSQL実行時にどのようにテーブルやビューなどにアクセスするかをDBが定義したもの。 性能の悪いSQL（速度が遅い）は実行計画の内容が悪くなる。

当然ながら大前提として、速度や性能の良し悪しはそのSQLの要件によって大きく変わります。 例えば、オンラインで稼働するAPI等でミリ秒タイムでの性能改善が求められるものや、1時間に１回バッチ処理のために動くものなど様々なので、その考慮が非常に重要。

実行計画の取得方法

sqlplusにて以下のコマンドを実行することで、実行計画の取得が可能になります。

SQL> set autotrace traceonly

# 表示を綺麗にしたい時
set lin 10000 pages 0 long 500000 longc 500000
col plan_plus_exp format a1000

ちなみに上記はtraceonlyを指定しています。 これはSQLの実行結果については表示せず、実行計画と統計結果のみを表示することになります。

詳しくはこちらのサイトに一覧があります。

実行速度も測定する

基本的に実行計画を取得するときには、クエリの実行速度も測定したい場合も多いかと思います。 以下のコマンドで測定可能です。

SQL> set timing on

上記のまとめ（実行時コピペ用）

set lin 10000 pages 0 long 500000 longc 500000
col plan_plus_exp format a1000
set autotrace traceonly
set timing on

大前提

  • アクセスパスの見方 ネストの低いところから見ていく。（その順番で実行される）
  • Predicate どうやってそのデータに絞ったかどうかが書かれる、indexを利用した場合はaccessとなり、そうでない場合はfilterとなる

内容と統計の見方（POINT）

実行計画と統計を見る時のポイントを箇条書きします。 - Plan hash value 実行計画に割り当てられるハッシュ値。 ここの値が等しいと仮にSQLの内容が異なっても実行計画的に同様ということになる。 - physical reads 物理的に読み込みにいってる - MERGE JOIN データ量の差を無理やり結合させようとする。あまり良くない。 - NESTED LOOP いわゆるfor文のようなイメージで該当の情報にアクセスを絞っていく。 基本的にデータ量が極度に少ない場合を除き、良い実行計画と言える。 テーブルA < テーブルBの時によく出る（LEFT JOIN時）

INNER JOINの場合は、テーブルA > テーブルBでもやってくれるかも - HASH JOIN データ量が少なく、NESTED LOOPするよりも結合した方が早い場合は有効。 レコード数に見あってHASH JOINとなってればOK テーブルAとテーブルBのサイズが同等だと出やすい

  • consistent gets 実際にブロックにアクセスした数。 あるSQLと別のSQLを比較して速度に差があっても、ここの数が近ければキャッシュなど速度を疑った方が良い。

ちなみに１ブロックは約8KB

  • NESTED LOOPS SEMI EXISTSの時に、存在が見つかったらその時点で内部表（EXISTS先）の参照を終了する。 一番下まで見にいかないので性能がいい。 参考

速度改善Tips

  • WHERE句でのIndex直指定 WHERE句でIndexに作成したカラムの値を直指定すると速度が出やすい。

  • 例

# Index
CREATE INDEX ${INDEX_NAME} ON ${TABLE_NAME} ( ${COLUMN} );

# Select
SELECT
    *
FROM
    ${TABLE_NAME}
WHERE
    ${COLUMN} = 'column_id';
  • LEFT OUTER JOINを使う時のIndexの利かせ方 LEFT側の表が必ず外部表、JOIN先の表は内部表になる。 なので外部表をWHERE句で絞った上で、内部表のレコード数を増やしておくと その中でIndexが作成済みであればOracleがIndexを使いやすくなる。

Predicate Informationのfilterとaccess

  • filter filterはindexが効いてないで実際に取得時に検索条件を実行している時
  • access accessはindexが効いてアクセスできている状態

ORを使う時

where句の条件でorを使用する時は一部注意が必要。 オラクルはor existsなどが苦手なので実行計画上うまく性能を保てない可能性がある。

tags: SQL, Programming, DB