今さらsort_area_sizeについて考えさせられる：オラクルデータベースの技術メモ：So-netブログ

トランケートについて知っておくべきこと｜12cR2のローカルUNDOがローカルに.. ブログトップ

今さらsort_area_sizeについて考えさせられる　[オプティマイザ] [編集]

先日12.2でソートの実行計画がおかしいという話を聞いて調べてみたときのこと。前のシステム(11.1)ではメモリソートをする実行計画だったのに、12.2ではなぜかソート列に張られている主キーの索引をフルスキャンしテーブルアクセスする実行計画になっていた。SQLはこんなイメージ。

select * from emp where hiredate=to_date('20181001','YYYYMMDD') order by empno;
※主キーemp_pkはempnoに張られている状況

前者の実行計画は表のマルチブロックリード（hiredateで絞り込み)＋結果セットのメモリソートなので高速、後者は索引のマルチブロックリード(全件)＋表シングルブロックリード(hiredateで絞込み)ということで、後者の方がかなり遅かった。

/*+ FULL(emp) */ ヒント句をつければ確かに速くなったのだが、問題はなぜ12.2のオプティマイザは後者の実行計画を選択したか、という点であった。なお、前提としては、表の統計はなし（NULL統計でロック）、テーブルに格納するデータは同じ（レコード数も同じ）である。

そこで10053トレースを取得してみて調べてみた。10053トレースはオプティマイザのコスト計算のトレースであり、複数ある実行計画のコストをどう計算し、最終的な実行計画を導いたかがわかる。ここから、12.2のオプティマイザはソートのコストを極めて高く見積もっていることがわかった。それは、メモリソートに収まるサイズが1MBとなっており、ソートスピルのためにtemp表領域にI/Oが発生するコストとなっていたからだった。なお、11.1ではpga_aggregate_target(PAT)を使わず、sort_area_sizeを明示的により大きく(32MB)設定していたため、メモリソートに収まるため、コストが低く見積もられていた。

PATはセッション毎の最大ソート領域は自動チューニングのため、個別セッションのソート領域を通常意識する必要はないと思っていた。実際、セッション毎の最大ソートサイズは_smm_max_sizeで決定し、これはお概ねPATのサイズに比例して大きくなる。しかし、オプティマイザのソートコスト計算での前提となるメモリサイズはセッション毎の最小ソートサイズ(_smm_min_size)であり、どうやらこの最大値は1MBであるらしい。つまり、どれだけPATを大きくしても、オプティマイザのコスト計算上はsort_area_size=1MB相当となってしまう。最大ソートサイズまでPGAは利用できるのに、コスト計算上はこれが考慮されず、必然的にソートコストが高めに計算される、という訳だ。

結局、_smm_min_sizeを前システムのsort_area_sizeの値に設定し、オプティマイザのコスト計算を従来と同様にする対処をすることとした。いまどき新規で作る際PATを使わない、という選択はあまりしないと思うが、もしupgradeなどでPATを使い始める際には、実行計画を安定させる上でこの点の考慮が有用かもしれない。

なお、このソート周りのオプティマイザのコスト計算を理解する上で非常に有用だったのが、Jonathan Lewis氏のCost-Based Oracle Fundamentalsである。これ以上、オラクルのコストについて熱く（厚く）書かれた書籍を私は知らない。残念ながら12cの話は含まれていないが、それでもなお、基本的な考え方は変わっておらず、オプティマイザに関しては私のバイブル的な本である。