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

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

さて、先週の問題の回答です。
まず、shared poolにメモリソート領域が割り当てられようとしているのは何
接続の場合だったでしょうか？
セッション情報を確認してみます。

■セッション情報の確認

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

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

SERVER列がSHAREDになっていることから共有サーバー(MTS)接続であることが
わかります。
詳細はメルマガバックナンバーVol.191をご参照下さい。

では、多くのメモリソート領域がshared poolに割り当てようとされたこと原
因で発生しているORA-4031エラーは回避可能なのでしょうか？
皆様から寄せられた回答を検証してみましょう。

■解決策その１
～large_pool_sizeの設定～

初期化パラメタにlarge_pool_sizeが設定された場合、共有サーバ接続の場合
のメモリソート領域はlarge poolに割り当てられます。
ただし、今回の設定条件は充分な空き領域がありませんのでlarge_pool_size
を大きく設定することはできません。
仮に100MBのlarge_pool_sizeを設定してみます。

SQL> alter system set large_pool_size=100M;

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

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

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

100MB程度のlarge_pool_sizeの設定では焼け石に水のようです。
もちろんshared_pool_sizeを減らして、更に大きなlarge_pool_sizeを設定す
ることは可能ですが集計処理のためだけに巨大なlarge_pool_sizeを設定して
おくことはあまり現実的ではありません。

■解決策その２
～専用サーバー接続に変更する～

そもそも、ORA-4031エラーはshared poolやlarge poolなどのＳＧＡにメモリ
ソート領域が割り当てられたことが原因で発生しています。
これをＰＧＡに割り当てるように変更することでエラーを回避することができ
ます。メモリソートがＰＧＡに割り当てられるようにするには、専用接続に変
更することで可能です。
tnsnames.ora の CONNECT_DATA句に『 (SERVER=DEDICATED) 』と記述したサー
ビスを作成し、作成したサービスを使用して接続すれば専用サーバー接続にな
ります。仮に専用サーバー接続にて集計処理を行います。

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

       SID    SERIAL# SERVER
---------- ---------- ------------------
        10         14 DEDICATED

SQL> select id,sum(cost) from code where rownum<=1000000 group by id;

1000000行が選択されました。

エラーを回避することができました。
メモリソート領域をＰＧＡに割り当てるようにするにはもうひとつ方法があり
ます。

続きは来週。

週末は雪でした。箱根にて。

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