[Oracle] 共有プールサイズの監視とチューニング

共有プールはライブラリキャッシュ、ディクショナリキャッシュとして使われるメモリー領域で、ライブラリキャッシュには解析されたSQLやPL/SQLプロシージャとその実行計画などがキャッシュされており、ディクショナリキャッシュにはデータベース内のオブジェクトに関する情報がキャッシュされています。

Oracleデータベースにアクセスする際には、これらの領域にキャッシュされたデータをなるべく再利用したほうが効率がいいということになります。

これらの領域が効率よく再利用されているかどうかは、各領域のキャッシュヒット率で判断します。

ライブラリキャッシュヒット率はv$librarycacheから以下の要領で算出します。

select sum( pins ) TOTAL_PINS, sum( reloads ) TOTAL_RELOADS,
  trunc( ( 1 - sum( reloads ) / sum( pins ) ) * 100, 3 ) "HIT_RATIO(%)"
  from v$librarycache;

TOTAL_PINS TOTAL_RELOADS HIT_RATIO(%)
---------- ------------- ------------
  75344388         43835       99.941

ディクショナリキャッシュヒット率はv$rowcacheから以下の要領で算出します。

select sum( gets ) TOTAL_GETS, sum( getmisses ) TOTAL_MISSES,
  trunc( ( 1 - sum( getmisses ) / sum( gets ) ) * 100, 3 ) "HIT_RATIO(%)"
  from v$rowcache;

TOTAL_GETS TOTAL_MISSES HIT_RATIO(%)
---------- ------------ ------------
 174878489       122923       99.929

ライブラリキャッシュヒット率は99%以上、ディクショナリキャッシュヒット率は95%以上が望ましいとされています。

キャッシュヒット率が著しく低い場合は、共有プールサイズが小さすぎる可能性があるため、初期化パラメータファイルSHARED_POOL_SIZEの値を大きくすることを検討します。

また、ライブラリキャッシュヒット率が低い場合は、バインド変数が有効に利用されているかどうかなど、SQLがなるべく再利用されるようアプリケーション側を見直すことも重要です。