ORACLEのメモリ(チューニング編)

このページでは一般的なメモリ設定のチューニング方法について記述します。


SGAとPGAのサイズ決定

まずはSGAとPGAの合計サイズを決定しますが基本方針としてはswap in/outが頻繁に発生しない範囲で最大限大きなメモリを割り当てることを検討します。 該当サーバがDB専用の場合、

(OS全体の物理メモリ - OSの推奨物理メモリ) * 0.8(安全率)

程度をまずSGAとPGAの合計サイズに設定するとよいと思います。 SGAとPGAの割合の最適値は使い方次第ですが、大量のソートやハッシュ結合等が必要な重い処理が多い場合はPGAを、 様々な種類のSQLが同時多重で多く実行される場合はSGAを多めに割り当てます。予測がつかない場合はまずSGAとPGAを1:1で割り当ててパフォーマンス測定し、 以下のような観点でそれぞれSGAとPGAを調整します。

SGAのチューニング

SGAが足りない場合は以下のような傾向が確認できます。

バッファキャッシュ
バッファキャッシュが少ない場合バッファキャッシュヒット率が低下し、物理DISKの読み込み回数が増加します。 目安としてはバッファキャッシュヒット率が95%以下である場合はバッファキャッシュの増加を検討します。

なお、バッファキャッシュヒット率が十分であるというだけでバッファキャッシュのチューニングの余地がないと判断することはできません。 これはLOBや巨大なテーブルのブロックを読み込む場合にバッファキャッシュを経由しないでブロックを読み込む(11g以降で実装されたダイレクトシングルリード)動作があるためです。 これらのケースでバッファキャッシュにブロックを乗せておきたい場合は明示的にCACHE句を指定する必要があります。 バッファキャッシュヒット率は以下のSQLによって確認することができます。

・バッファキャッシュヒット率 
SELECT 
    NAME, DB_BLOCK_GETS, CONSISTENT_GETS, 
    PHYSICAL_READS,TRUNC((1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)))*100,2) "HIT(%)"
FROM
    V$BUFFER_POOL_STATISTICS
共有プール
共有プールが少ない場合ライブラリキャッシュヒット率やディクショナリキャッシュヒット率が低下します。 目安としてはそれぞれのヒット率が99%、95以下の場合は共有プールの増加を検討します。

アプリケーションから実行されるSQLがバインド変数化によって共有されていない、動的にselect句やwhere句が変わるといった場合共有プールを増やしてもあまり意味はなく、 逆にライブラリキャッシュが大き過ぎることでライブラリキャッシュのロック時間が大きくなりSQLのパフォーマンスが落ちる可能性があるので過剰に大きくすることはお勧めできません。

サーバが搭載するメモリ容量は年々増加していますので今後大容量メモリに適した改善がされていく可能性は高いと思いますが11g位までのリリースにおいては大きくとも1GB程度あれば十分な場合が多いです。 インスタンス起動時から現在までの累計ライブラリキャッシュヒット率、ディクショナリヒット率は以下のSQLにて確認できます。

・ライブラリキャッシュヒット率 
SELECT
    SUM(PINS) SUM_PINS,
    SUM(RELOADS) SUM_RELOADS,
    ROUND((1 - SUM(RELOADS) / SUM(PINS)) * 100, 2) || '%' HIT_RATIO
FROM
    V$LIBRARYCACHE
・ディクショナリキャッシュヒット率 
SELECT
    SUM(GETS) SUM_GETS,
    SUM(GETMISSES) SUM_GETMISSES,
    ROUND((1 - SUM(GETMISSES) / SUM(GETS)) * 100, 2) || '%' AS HIT_RATIO
FROM
    V$ROWCACHE

PGAのチューニング

PGAが足りない場合はソートやハッシュ結合等の処理で一時表領域へのDISK書き出しが発生します。 一時表領域への書き出しは最近のバージョンではダイレクトI/Oによって書き出されることから 以下のようなSQLで物理ソートが発生した可能性のあるSQLを抜き出すことができます。
※ダイレクトI/Oはダイレクトインサート等でも発生するため、sort時にダイレクトI/Oが発生していたとは限りません。

・ソート時にDISK書き出しが発生した可能性のあるSQL(10g以降でのみ実行可能) 
SELECT
    SQL_ID,SQL_TEXT,EXECUTIONS,ELAPSED_TIME,DIRECT_WRITES
FROM
    V$SQL
WHERE
    DIRECT_WRITES > 0 AND SORTS > 0
SQL文レベルではなくインスタンスまたはセッション単位のソート回数、ソート行数、DISK書き出しが発生したソートの数の合計は以下のSQLで確認することができます。

・インスタンス単位 
SELECT
    NAME,VALUE
FROM
    V$SYSSTAT
WHERE
    STATISTIC# IN (349,350,351)
・セッション単位 
SELECT
    A.SID, B.NAME, A.VALUE
FROM
    V$SESSTAT A,V$STATNAME B
WHERE
    A.STATISTIC#=B.STATISTIC# AND A.STATISTIC# IN (349,350,351)
ORDER BY
    SID,NAME
また、接続数が非常に多い場合や高多重のパラレル処理の実施時に1プロセスあたりに割りあてるPGAが少なくなるため 最大限のパフォーマンスが発揮できません。自動PGAメモリ管理(pga_aggregate_target)の機能を利用している場合、 1プロセスに割り当てられるPGAはpga_aggregate_targetのサイズよりもかなり少なくなるため 特定の処理のみPGAを多く割り当てたい場合、手動PGA管理に変更して明示的にPGAのメモリサイズを指定する必要があります。

以下の処理では実行セッションを手動PGA管理へ変更し、ソート領域に16000000byte利用するようにしています。 
ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL
ALTER SESSION SET SORT_AREA_SIZE = 16000000             --ソート処理のメモリサイズ
--ALTER SESSION SET HASH_AREA_SIZE = 16000000           --HASH結合処理のメモリサイズ
--ALTER SESSION SET CREATE_BITMAP_AREA_SIZE = 16000000  --BITMAP索引作成処理のメモリサイズ
--ALTER SESSION SET BITMAP_MERGE_AREA_SIZE = 16000000   --BITMAP索引検索処理のメモリサイズ
※1つのSQLで複数のソート等が走る場合、SORT_AREA_SIZE * n の領域が割り当てられます。 　また、パラレル処理時は各パラレルスレーブサーバそれぞれに、SORT_AREA_SIZE * n 分の領域が割り当てられる可能性があるため 　必ず実際に確保されたPGAサイズが問題がないか確認します。

マニュアル

・概要
・パフォーマンス・チューニングガイド
★ORACLE案件承ります