ソート領域のチューニング

1Z0-033
  • ソート処理を必要とするSQL
    • 索引の作成（通常）
    • ANALYZEコマンド
    • order by
    • group by
    • distinct
    • UNION、INTERSECT、MINUS演算子
    • ソートマージ結合

※どのタイプのjoin（ハッシュマップ、ソートマージ、ネストループ）が使われるかは実行計画で決まる

  • ソート処理を回避できるSQL
    • NOSORT句を使用した索引作成（あまり使える状況が無い）
    • order by句に使用される列に索引があり、主キー制約かNOT NULL制約が設定されている場合
    • UNION ALL
    • ネストループ結合
    • ANALYZEコマンドでESTIMATE STATISTICSオプションを使用する場合、ソート処理がなくなるわけではないが、COMPUTE STATISTICSオプションを使用する場合よりはソート量を減らすことができる
  • ソート処理のメモリ用パラメータ
    • SORT_AREA_SIZE
      • ソートに使用するメモリサイズ
      • OLTPなら基本的にデフォルトの64Kで問題ない
    • SORT_AREA_RETAINED_SIZE
      • ソート完了後に保持されるメモリサイズ
      • 基本的にはSORT_AREA_SIZEと同じ値
      • メモリが非常に不足している場合、共有サーバ構成の場合は値を小さくすることを検討する
  • 一時セグメント
    • 一時セグメントは最初のディスクソート操作時に作成される
    • データベースがクローズされると削除される
    • ディスクソートの規模に応じて拡張される
    • セグメントは複数のセッションで使用される
    • 一つのエクステントは一つのセッションでのみ使用される
    • ローカル管理表領域でもディクショナリ管理表領域でもOK
  • 自動PGAメモリ管理
    • PGA_AGGREGATE_TARGET
      • すべてのサーバー・プロセスで取得できるPGAメモリの上限
      • 有効にすると、PGA内でソート領域を使用する専用サーバー接続のユーザーに割り当てるメモリの量を、Oracleが自動的に決定
    • WORKAREA_SIZE_POLICY
      • 自動PGAメモリ管理を使用するかどうか
      • AUTOの時はSORT_AREA_SIZEやSORT_AREA_RETAINED_SIZEは無効
      • MANUALのときはSORT_AREA_SIZEやSORT_AREA_RETAINED_SIZEが有効
  • ソート処理のチューニング目標
    • ソート処理をできるだけ回避する
    • できるだけメモリソートにする。ただし、メモリ設定をあまり大きくしすぎるとOSレベルでスワッピングやページングが発生して逆に効率が悪い。
    • 一時セグメントのサイズを適切にし、領域割り当て処理を低減する
    • メモリソートに対するディスクソートの比率は、5%未満
  • ソート処理の診断
    • メモリソート回数、ディスクソート回数はv$sysstatで確認
    • ディスクソートに関する情報はv$sort_segment,v$sort_usageでも確認