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

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

今回も実践編パート２です。
実例を元とした検証を行います。

□背景

xx月xx日：
あるシステムにおいて、集計処理時にORA-4031が頻繁に出力され、処理が異常
終了してしまっている。マニュアルを見ると対処方法として、shared_pool_size
を大きくするように記述があったのでshared_pool_sizeを200MBから倍の400MB
に増やした。

１ヶ月後：
shared_pool_sizeを増やしたことでORA-4031は出力されなくなっていたが、徐
々にデータ件数が増えてくるとまた頻繁に出力されるようになった。
今回はshared_pool_sizeを大きく増やすほど、空きメモリが充分ではなかった
ので物理メモリの追加を行った上でshared_pool_sizeを800MBに設定した。

３ヵ月後：
しばらくはORA-4031が出力されなくなったものの、また頻繁に出力されるよう
になった。さらなる物理メモリの追加も難しい。。。
このままでは月末処理を乗り切ることができないのでどうしようかと担当者は
頭を抱えている。

以下、メッセージマニュアルから抜粋
ORA-04031 共有メモリーを割当てできません
原因: 共有プールに割り当てられた共有メモリーより多くの共有メモリーが必
要です。
処置: 共有プールがメモリー不足の場合、大きいパッケージを確保するために
DBMS_SHARED_POOL パッケージを使用するか、使用している共有メモリーを削減
するか、またはSHARED_POOL_RESERVED_SIZE およびSHARED_POOL_SIZE 初期化パ
ラメータの値を増やすことによって、使用可能な共有メモリーの量を増やして
ください。大きいプールがメモリー不足の場合、LARGE_POOL_SIZE 初期化パラ
メータを増やしてください。

□環境

OS:Red Hat Linux release 7.1
Oracle:9.2.0.1

□問題点の切り分け　その１□
▽現象の再現▽

早速上記背景と同じ環境を作ってテストしてみましょう。
但し、shared_pool_sizeを大きくしてのテストは大変なので、小さな環境で
ORA-4031の出力再現テストを行ってみます。

＠shared_pool_sizeの変更

SQL> alter system set shared_pool_size=12M;

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

＠集計処理の実行１

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

100000行が選択されました。

問題なく処理が完了したようです。
今度はデータを増やして実行してみます。

＠集計処理の実行２
データ件数を100万件に増加

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

1行でエラーが発生しました。
ORA-04031: 共有メモリーの8192バイトを割当てできません("shared pool","unknown
object","sort subheap","sort key")

無事(！？)エラーが出力されました。

shared poolの空きを確認します。

＠shared poolの空き領域を確認

SQL> select pool,name,bytes/1024/1024 MB from v$sgastat
     where name = 'free memory'
     and pool='shared pool';

POOL        NAME                               MB
----------- -------------------------- ----------
shared pool free memory                8.92718124

約9MB空いているようです。
shared poolは空きがあっても、長時間連続稼動していたりすると再利用され
ないSQL文がたまってしまい充分な連続領域を割り当てられない場合がありま
す。これをshared poolの断片化といいます。
以下のコマンドにて、断片化してしまったshared poolをクリアすることがで
きます。

＠shared poolのクリア

SQL> alter system flush shared_pool;

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

再度集計処理を実行してみます。

＠集計処理の実行３
shared poolクリア後

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

1行でエラーが発生しました。
ORA-04031: 共有メモリーの8192バイトを割当てできません("shared pool","unknown
object","sort subheap","sort key")

shared poolのクリアでは問題解決にいたらなかったようです。

ここでORA-4031のエラーメッセージに注目してみます。
メッセージに４つの引数が同時に出力されています。
この引数には以下の意味があります。

引数１：shared pool：メモリを確保しようとしたプール
引数２：unknown object：確保しようとしたオブジェクトの内容
引数３：sort subheap：確保を試みたメモリの大まかな種類、場所
引数４：sort key：確保を試みたメモリのタグ

どうもソート処理がうまくいかなかったようです。
ここでメルマガを毎週欠かさず、愛読されている皆さんはお気づきだと思いま
す。shared poolにメモリソート領域が割り当てられようとしているのは何接
続の場合だったでしょうか？
またこの頭を抱えている担当者を救うような解決策はあるのでしょうか？
皆様から解決策を募集します。

ここでおさらい
１. shared_pool_sizeを大きくするほどのメモリの空き領域はない。
２. 物理メモリの追加もできない。
３. ORA-4031エラーは多くのメモリソート領域がshared poolに割り当てようとされ
たこと原因である。

正解者の中から抽選で当メルマガの本を差し上げます。奮ってご応募くださいネ！
(by TI)

今週はここまで。

太陽がまぶしい茅ヶ崎にて。

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