Oracleデータベースのメモリチューニング徹底解説！SGA最適化でパフォーマンスを向上させる方法

メモリチューニングとは、空きメモリをOracleの各メモリコンポーネントに配分・再割り当てする作業のことです。主にOracleクエリのパフォーマンスチューニングを目的として実施されます。

近年、多くのお客様にとって最大の課題となっているのがデータベースのパフォーマンス問題です。本記事では、データベースのメモリを適切にチューニングすることで高いパフォーマンスを実現する方法を解説します。

なお、チューニングの手法にはOSレベルのプロセスチューニング、CPUチューニング、RAMチューニング、データベースチューニングなどさまざまな種類がありますが、本記事ではデータベースにおけるメモリチューニングに焦点を当てます。

データベースのメモリチューニング＝SGAのチューニング

データベースのメモリチューニングとは、端的にいえばシステムグローバル領域（SGA：System Global Area）のチューニングです。SGAチューニングに関わる主要コンポーネントは以下の通りです。それぞれ詳しく見ていきましょう。

1. Keep Pool（キーププール）
2. Default Pool（デフォルトプール）、Recycle Pool（リサイクルプール）、Stream Pool（ストリームプール）
3. DBバッファキャッシュ：フリーバッファ、ダーティバッファ、ピンバッファ
4. 共有プール（Shared Pool）

Keep Pool（キーププール）

日々の業務で常用されるパッケージがある場合、パフォーマンスを向上させるには、そのパッケージをSGA上に保持しておくのが有効です。プロシージャを使えば、対象のパッケージをKeep Poolに常駐させることができます。

ただし注意点として、SGAへの割り当てがシャットダウンされると、SGAはRAMに再割り当てされる必要があり、その際にはKeep Poolに格納していたパッケージもフラッシュされてしまいます。次回データベースを再起動して再度パッケージをKeep Poolに格納すれば、今度はデータベースをシャットダウンするまで保持され続けます。このようにすることでI/Oを削減できます。

また、ヒット率を確認することで、ライブラリキャッシュからパースコードや実行計画がフラッシュされていないかを把握できます。ライブラリキャッシュのヒット率は85%以上が目標です。

Stream Poolは、Data Pump用のバッファを作成するために使用されます。

毎日アクセスされる小規模なテーブルについては、パフォーマンス向上のためにSGA（Keep Pool）に保持しておくのが望ましいでしょう。こうすることで、ディスクからではなくメモリから直接データを取得できるようになります。一方、大規模なテーブル向けにはRecycle Poolが用意されています。

デフォルト設定では、テーブルをSELECTすると、そのブロックがデータバッファキャッシュにフェッチされ、Default Poolに格納されます。しかし、別のテーブルをSELECTするとDefault Poolの内容はフラッシュされます。バッファに十分な空きがあれば問題ありませんが、空きがなければ古いテーブルのブロックが追い出され、新しいテーブルが格納される仕組みです。

そのため、パフォーマンスを重視するなら、小規模テーブルはDefault PoolではなくKeep Poolに配置しましょう。DBバッファキャッシュのヒット率は95%以上が目標です。95%を下回る場合は、データベースバッファキャッシュのサイズ変更（リサイズ）を検討してください。

メモリチューニングの中核となるのは、DBバッファキャッシュと共有プールです。これらはメモリ上に十分なデータを維持するために重要な役割を果たします。SGAチューニングのためには、DBバッファキャッシュの基本的な動作を理解しておく必要があります。記事の後半では、ヒット率を算出してメモリをチューニングするためのサンプルSQLも紹介します。

DBバッファキャッシュの3つのバッファ状態

フリーバッファ（Free Buffers）

変更前のデータは、フェッチされたブロックがデータバッファキャッシュにコピーされた状態であり、これらのバッファを「フリーバッファ」と呼びます。たとえば、ディスク上の値が1000で、メモリ上の値も1000であるような、両者が同一の状態を指します。

ダーティバッファ（Modified Buffers／Dirty Buffers）

ダーティバッファは「変更済みバッファ」とも呼ばれます。ディスク上のイメージとデータバッファキャッシュ上のイメージが一致しなくなったものの、まだディスクへ書き込まれていないバッファのことです。

ピンバッファ（Pinned Buffers）

DBバッファキャッシュ内のデータは常に変化しています。サーバープロセスは、後続のトランザクションのためにこれらの変更済みデータを選択します。この選択されたデータこそがピン留めされたデータ、すなわちピンバッファです。

読み取りの種類とパースの仕組み

物理読み取り（Physical Reads）：OracleがI/Oを実行してディスクから読み込むデータブロックのことです。

論理読み取り（Logical Reads）：Oracleがデータベースバッファキャッシュ自体からの読み込みだけで要求を満たせる場合の読み取りです。

DBブロック取得（DB Block Gets）：Oracleがバッファキャッシュ内に目的のデータを見つけた際、そのデータがコミット済みかどうかを確認し、コミット済みであればバッファからフェッチします。このバッファは「DBブロック取得」と呼ばれます。

一貫読み取り（Consistent Reads）：バッファキャッシュ内に存在するものの、変更済みで未コミットのブロックに対しては、undoデータファイルからデータを取得する必要があります。

DBバッファキャッシュの目標は、論理読み取りを最大化することです。

ソフトパース（Soft Parsing）：ライブラリキャッシュに実行計画がすでに存在する場合、ディスクにアクセスせず、既存の実行計画をもとにパースコードを作成します。これをソフトパースと呼びます。

ハードパース（Hard Parsing）：パースコードを作成する際、まず実行計画が利用可能かどうかを確認します。実行計画があればソフトパースで処理し、なければハードパース、つまりディスクへアクセスして処理を行います。

共有プール（Shared Pool）

共有プールは、ライブラリキャッシュとデータディクショナリキャッシュの組み合わせで構成されます。

ライブラリキャッシュ：ライブラリキャッシュの目標は、ソフトパースの比率を高めることです。

目標を達成するための4つの手法

1. バインド変数を使用する
2. ストアドプロシージャを作成する
3. ヒット率を管理する（85%以上を目標。下回る場合は共有プールサイズを拡張する）
4. DBMS_SHARED_POOLパッケージを活用する

SQLクエリを使ったメモリチューニングにおいて、ヒット率は非常に重要な指標となります。

データディクショナリキャッシュ：「行キャッシュ（Row Cache）」とも呼ばれます。データディクショナリキャッシュのヒット率も85%以上が必須で、下回る場合は共有プールサイズを拡張してください。

ヒット率を算出するサンプルSQL

ライブラリキャッシュのヒット率

select (sum(pinhits)/sum(pins))*100 as lchitratio from v$librarycache;

select namespace, pins, pinhits, reloads from v$librarycache order by namespace;

DBMS_SHARED_POOLパッケージを利用するには、次のスクリプトを実行してください。

@$ORACLE_HOME/rdbms/admin/dbmspool.sql

select namespace, kept, locks, executions from v$db_object_cache where type like '%PROC%';

データディクショナリキャッシュのヒット率

select (sum(gets-getmisses-fixed))/sum(gets) as "ddchitratio" from v$rowcache;

REDOログバッファキャッシュ

select name, value from v$sysstat where name like 'redo%';

まとめ

本記事では、Oracleデータベースのメモリチューニングの基本として、SGAの主要コンポーネントやバッファの状態、パースの仕組み、そしてヒット率の算出方法を解説しました。これらのポイントを押さえておけば、データベースのメモリチューニングを効率的に進められるはずです。

ご意見やご質問がある場合は、フィードバックフォームからお気軽にお寄せください。