ORA-12518まとめ

FYI Oracle

以下のエラーに関する顛末のまとめ
ORA-12518:　TNS:　リスナーはクライアント接続をハンドオフできませんでした


サポートからは以下の情報

          • -

WINDOWSプラットフォームでは、
PGA_AGGREGATE_TARGET, SORT_AREA_SIZEパラメータが大きめの値に設定されている等、
各スレッドが多くのメモリを消費し、プロセスに割り当て可能なメモリの上限に達してしまう際に出力される
また、このような状態でリスナーがクライアントからリクエストを受け取るとTNS-12518が発生し、
接続が不可となる（ことがある）

          • -


メモリ上限ってなによ？ってWin32プラットフォームではアプリに分け与えられるメモリは2Gまで。
それを超えるとお腹いっぱいになってしまうと。


っつてもなぁ。記録では1.5G程度の消費で多少の余裕はあるんだけどなぁ。
じゃぁってんで、PGA＿AGGREGATE_TARGETを600Mまで減らしてたのだが、やはり発生する時がある。
（もちろん、カーネルでつかめるメモリを2Gから3Gへ増やす方法は
　あることはあるのだが、MSもOracle社も推奨してないということで却下）


取り敢えず、セッション絞っていこうかということで現在保留中。
PGAは結構奇妙な動きをすることが判明している


１．ＰＧＡの概要

ＰＧＡは、大きくわけで、ＣＧＡとＵＧＡに分けられる。


１）ＵＧＡ
ＵＧＡは、ソートやハッシュ結合などの作業領域として利用される。
したがって、ソートするデータハッシュ結合するデータ量が多くなると、
ＵＧＡのサイズは増加する傾向があると考えられる。


２）ＣＧＡ
ＣＧＡは、カーソル変数や、バインド変数、配列は、スタックなどの
プログラム上の変数を保持する領域とされている。
しがたって巨大な配列や、数多くの変数、スタックサイズの増大なのにより
増加する傾向があると考えられる。


２．Windowsのメモリアーキテクチャ
３２ビットアドレッシングでは、アプリケーションが利用できるメモリ空間が２ＧＢまでとなっている。
（残り２ＢＧはカーネルが利用しているためユーザアプリケーションでは、通常は利用できない。）
この制限が足かせとなり、oracle.exeが利用できるメモリは、２ＧＢまでとなっている。
また、unix系とは異なり、v$processビューで確認できるプロセスは、
oracle.exeという１プロセス内の１スレッドという位置付けとなっている。
これは、専用サーバー接続の場合、clientからのセッションにより起動される専用サーバーが
oracle.exeの１スレッドになるということを意味する。


したがって、大雑把に考えると、
Ｏｒａｃｌｅのインスタンスのサイズ（最大２ＧＢ）
　＝Oracleバイナリサイズ＋ＳＧＡのサイズ＋ＰＧＡのサイズ


ということになる。
OracleのバイナリサイズとＳＧＡのサイズは固定サイズとして求めることができるが、
ＰＧＡのサイズは接続してくるセッション数等により増減する。
ちなみに、現対象サーバでは、バイナリサイズ＋SGAサイズの合計は、約１．３ＧＢ程度であるため
ＰＧＡとして、利用できる空きサイズは、約７００ＭＢ強程度である。


この７００ＭＢを各セッションに割り振らなけれならないが、
ＰＧＡの割り振りについては、来る者は拒まない仕様となっているようだ。
（つまり後で記載するPGA_AGGREGATE_TARGETパラメータの意味を失うということ。
　少なくとも、パラメータの説明通りの動作はしていないということだ）
次に、ＰＧＡを制御する初期化パラメータとその機能について、調べた点について記載する。


３．ＰＧＡに関連するパラメータ（公開、非公開含む）


１）PGA_AGGREATE_TARGET
インスタンスでＰＧＡに割り当てるメモリサイズの総量の目標値。
ＰＧＡのサイズの上限ではないことに注意すること。
したがって、来る者は拒まず、要求されれば、それ以上のサイズになることはある。


２）_PGA_LARGE_EXTENT_SIZE （隠しパラメータ）
隠しパラメータであるため、マニュアルには記載されていない。
この単位でＰＧＡ（ＵＧＡ、ＣＧＡも)が確保割り当てられている模様。
デフォルトでは、１ＭＢ。オラクルのマニュアルには明確に記載されていないものの、
１スレッドを確保すると１ＭＢをデフォルトで割り当てるとなっているのは
これを意味していることである可能性は高い。


３）_PGA_MAX_SIZE （隠しパラメータ）
これも隠しパラメータであることから、マニュアルには記載されていない。
しかし、情報によると、_PGA_MAX_SIZEは、１プロセスの割り当てられるＰＧＡサイズの上限となっている。
このパラメータのデフォルトサイズは、２００ＭＢ。

これ、テストしてみたら、PGA_AGGREGATEがいくつに指定していようが
ワンセッションあたり２００MBまでなら、容赦なくメモリ食いまくるということが解った。



４．ＰＧＡの割り当てロジック


PGA_AGGREGATE_TARGETを含めたパラメータを基にOracleがどのようにＰＧＡを割り当てているのか？


ＣＧＡに関する部分の記述はWebでは、見つけられなかったもののＵＧＡに関する動きを
細かく書いているサイトは多数あった。（おら！オラ！Oracleとか）


１）　ＵＧＡに関する動作
前述した通り、ＵＧＡはソートなどに利用される。
メモリ（つまりＵＧＡ）内で足りなくなれば、ＤＩＳＫ（一時表領域を利用する）を利用して作業する
ことになる。


メモリ（ＵＧＡ）内だけで処理できた場合は、statspackレポートなどPGAに関するレポートでは
optimalで処理できた回数としてレポートされる。
１回だけＤＩＳＫを利用した場合には、1-passとしてレポートされ、
複数回ＤＩＳＫを利用した場合には、m-passとしてレポートされる。


このＵＧＡのメモリ割り当ての仕様では、１プロセス（１スレッド）が利用できるＵＧＡサイズの
最大値が各パラメータをもとに算出される。


PGA_AGGREGATE_TARGET*5%　及び
_PGA_MAX_SIZE*50%


から小さい方の値を１プロセス（１スレッド）で利用できるＵＧＡの最大サイズとして制限している。


PGA_AGGREAGTE_TARGETを６００ＭＢとすると、PGA_AGGREGATE_TARGET*5=３０ＭＢ。
_PGA_MAX_SIZE*0.5=１００ＭＢであるから、現時点で、１プロセス（１スレッド）が利用できる
ＵＧＡの最大サイズは、３０ＭＢと計算できる。


ＵＧＡだけから見た場合には、１スレッド最大３０ＭＢとして、空きメモリ７００ＭＢである場合
最大で２３セッションが同時に実行できる計算となる。ただ必要なＵＧＡが足りない場合には、
ＤＩＳＫを利用することになりメモリはそれ以上要求されない。


２）　ＣＧＡについて
ＣＧＡの割り当てロジックについての記述は見当たらなかったが、ＵＧＡに割り当てた残りが、
ＣＧＡとして利用できることになる。
ＣＧＡ自体のサイズを制限するパラメータは存在しないようなので、
_PGA_MAX_SIZEからUGAに割り当てる最大サイズのあまりが事実上の最大サイズということにある。


※※※※※※※※※※※※※※※※※
_PGA_MAX_SIZEを減らせば、１セッションあたりのPGAサイズは制御できるかも
しれないが、_PGA_MAX_SIZEは、ユーザのセッション以外にどのような影響があるのか
全く記載されていないため、安易にサイズを減らすことはできない。
Oracle社サポートからの指示・回答が無い限りは触るべきではなさそうだ・・・。
※※※※※※※※※※※※※※※※※


尚、ＣＧＡ、ＵＧＡ、を含めたＰＧＡのサイズは前述した各サイズにある程度のオーバーヘッドを
含んだサイズとなるという記載がある。
（テスト結果からも_PGA_MAX_SIZEより若干大きなサイズになっていることは確認できた。）