新・ソートに関する検証　その９

＜新・ソートに関する検証　その９＞
ペンネーム　グリーンペペ

さて、先週に引き続き”共有サーバー接続時のORA-4031エラーを回避せよ”
の回答です。

■解決策その３
～sort_area_retained_sizeを設定する～

共有サーバー接続時のメモリソート領域は、以下の公式により所定の領域に
割り当てられます。

sort_area_size – sort_area_retained_size = ＰＧＡ
sort_area_retained_size = ＳＧＡ

では早速、検証してみましょう。

▽環境

OS:Red Hat Linux release 7.1
Oracle:9.2.0.1

▽設定値の確認

SQL> select name,value,isdefault from v$parameter
     where name in ('workarea_size_policy','pga_aggregate_target')
     or name like 'sort%';

NAME                           VALUE                     ISDEFAULT
------------------------------ ------------------------- ---------
sort_area_size                 104857600                 FALSE
sort_area_retained_size        0                         TRUE
pga_aggregate_target           4294967296000             FALSE
workarea_size_policy           AUTO                      TRUE

sort_area_size = 100MB,sort_area_retained_size = 0
となっています。公式に当てはめると、
sort_area_size – sort_area_retained_size = PGAに割当てられるメモリソート領域
100MB – 0 = 100MB
となり、SGAに割り当てられる領域はないはずです。
では、実際に集計処理を実行してみましょう。

▽セッション情報の確認

SQL> select sid,serial#,server from v$session
     where username = 'XPRT';

       SID    SERIAL# SERVER
---------- ---------- ------------------
        10       2105 SHARED

共有サーバー接続になっています。

▽集計処理の実行

SQL> select id,sum(cost) from code group by id;

ORA-04031: 共有メモリーの8192バイトを割当てできません("shared pool",
"unknown object","sort subheap","sort key")

おかしいです。
先ほどの公式によって得られた情報だと全てのメモリソート領域はＰＧＡに割
り当てられるはずなのに、エラーメッセージに出力されているshared poolは
ＳＧＡなので矛盾してます。と悩んでたら、マニュアルに以下の記述がありま
した。

以下、Oracle9iデータベース・リファレンス,リリース9.2より転載↓
注意: V$PARAMETER 動的パフォーマンス・ビューに反映されるデフォルト値
は0です。ただし、明示的にこのパラメータを設定していない場合、実際には
SORT_AREA_SIZE パラメータ値が使用されます。

ということはメモリソート領域は、sort_area_sizeに設定したサイズ全てが
ＳＧＡに割り当てられようとしたようです。では、sort_area_retained_size
を明示的に設定したらどうなるのか、検証してみましょう。
とその前に空きメモリがない状況なので、これ以上ＰＧＡが割り当てられると
ページングが起きてしまいます。
事前に大きくしすぎたshared_pool_sizeを小さくしておきましょう。

▽sort_area_sizeを小さくする

SQL> alter system set shared_pool_size=200M;

システムが変更されました。

▽sort_area_retained_size = 0 の設定

SQL> alter session set sort_area_retained_size=0;

セッションが変更されました。

SQL> select id,sum(cost) from code group by id;

ORA-04031: 共有メモリーの8192バイトを割当てできません("shared pool",
"unknown object","sort subheap","sort key")

”０”に設定してもだめなようです。

▽sort_area_retained_size = 1 の設定

SQL> alter session set sort_area_retained_size=1;

セッションが変更されました。

SQL> select id,sum(cost) from code group by id;

1000000行が選択されました。

集計処理は問題なく、完了しました。

■解決策その４
～sort_area__sizeを設定する～

最後の解決策です。
空きメモリがないようなので、ディスクソートで集計処理するように
sort_area_size を小さく設定してみましょう。

▽sort_area_sizeの設定

SQL> alter session set sort_area_size=1;

セッションが変更されました。

SQL> select id,sum(cost) from code group by id;

1000000行が選択されました。

集計処理は問題なく、完了しました。

さて、読者の皆様。今回の問題の手ごたえは如何でしたか？
難しかったですか？簡単でしたか？
感想・質問など寄せてくれると、大変励みになります。
今号で、新・ソートに関する検証シリーズはおしまいです。
また、皆様に有益なメルマガを提供できるよう、グリーンペペは旅に出てきます。
それでは！

お花見スポットはどこですか？茅ヶ崎にて。

新・ソートに関する検証 その９