あるシステムでバッファ・キャッシュが枯渇した件について、調査をしました。
運用中の本番環境で、構成は以下の通りです。
このDBでは自動メモリ管理(AMM)を使用しており、memory_targetは設定されていますが、sga_target,pga_aggregate_targetは未指定です。
AWRレポートを確認すると、バッファ・キャッシュサイズが3GB近くから1GB近くまで減少し、更に減り続けてついには32MBまで縮小していました。
その分、増えていたのが共有プールで、SGAサイズ自体は変わっていません。
AWRレポートのSGA breakdownで確認すると、特に使用量が増えているのは共有プール内のKGLH0である事がわかりました。
バッファ・キャッシュサイズが3GB程度だった時には600MB程度だったものが、3GB程度まで増加しています。
KGLH0はSQLエリアに関わるメモリ領域で、子カーソルごとに消費される為、大量の子カーソルが発生すると使用量が増えます。
それで「SQL ordered by Version Count」から子カーソル多いSQLを確認してみました。数百の子カーソルを持つSQLはいくつかありましたが、特に増加はしていません。
また、「SQL ordered by Sharable Memory」で確認した共有メモリを多く使用しているSQLとも一致していませんでした。
KGLH0の増え方からして不具合の可能性が高そうなので、いつもの通り、MOS(My Oracle Support)で調べたところ、複数の不具合が見つかったものの、バージョン等の条件から合致しそうなのは以下の一つでした。
Bug 20370037 - memory leak kglh0 growth leading to ORA-4031 (ドキュメントID 20370037.8)
回避策は以下の通りです。
"_optimizer_use_feedback" = FALSE
"_optimizer_gather_feedback" = FALSE
このうち、"_optimizer_use_feedback" = FALSEは既に設定済なので、"_optimizer_gather_feedback" = FALSEを設定する事にしました。
また、PGAには3GB程度が割り当てられているのですが、実際に使用されているのは1GB程度なので、sga_targetを指定してSGAの最低値を確保する事もあわせて提言しました。