はじめに
前回は、パフォーマンス改善のために検討したい、メモリチューニング以外のOracle初期化パラメータ設定について紹介しました。
今回は、Oracleの設定でパフォーマンスへの影響が大きい、Oracleのメモリ設定について書きたいと思います。
Oracleのメモリ領域について
まず、Oracleのメモリ領域について簡単におさらいします。
Oracleのメモリ領域は、大きく分けて以下の2つに分かれています。
SGA(システムグローバル領域)
ストレージに保管されている表データや、SQLの実行計画等をメモリにキャッシュし、クライアントからのリクエスト処理間で共有する事で、ディスクアクセスを減らし、処理効率(パフォーマンス)を上げる為の処理領域。
また、SGA内は更に以下のような領域に分かれています。
| 領域名 | 用途 |
|---|---|
| データベース・バッファキャッシュ | ストレージ(ディスク)に保管されている、表や索引等のデータをキャッシュする領域です。SQLでの問い合わせ対象データが、バッファキャッシュに存在するかどうかを確認し、存在すればストレージ(データファイル)にはアクセスせず、メモリ上にキャッシュされているデータを返します。存在しない場合は、ストレージからデータを取り出し、バッファキャッシュにキャッシュした上で、結果を返します。 |
| 共有プール | 実行されたSQL文、実行計画、PL/SQLのコード、データディクショナリー(DB内のオブジェクト情報等の管理情報)等がキャッシュされています。この領域にキャッシュされている情報は、データベースで行われるほぼ全ての操作に関連します。その為、データベース・バッファキャッシュ同様、パフォーマンス的に重要な領域です。SQLの実行要求がくると、サーバー・プロセスは、共有プールのライブラリ・キャッシュ内に、実行しようとしているSQLと全く同じ(大文字・小文字・空白含めて完全に一致)ものが無いか確認します。 |
| REDOログバッファ | データベースに対して行われた、Insert、Update、Delete等の変更情報が格納されており、SQLをcommitすると、REDOログバッファから、オンラインREDOログファイルに変更情報が書き込まれます。 |
| ラージプール | 共有プールへの割当には適さない、大量のメモリ割当用途のオプション領域です。例えば、Recovery Manager(RMAN)による、バックアップ、リカバリ処理用のメモリ領域として使用されます。 |
| Javaプール | Oracleのデータベースエンジン内には、JavaVMが内蔵されており、OracleTextのAPIなどは、Javaで実装されています。この領域には、DBセッション固有のJavaコードやデータが格納されます。 |
| ストリームプール | バッファキューメッセージが保存されています。expdp、impdpの処理に使用されるメモリ領域です。 |
PGA(プログラムグローバル領域)
PGAは、SQLを実行するサーバー・プロセス毎に割り当てられるメモリ領域です。
PGAは、ログインセッション情報の保持、SQLのソート処理、集計処理、表の結合処理などに使用されます。
Oracleのメモリ設定の勘所
Oracleに限らず、データベースは、I/O負荷が高いシステムです。
ですが、ディスクアクセスは、メモリへのアクセスに比べはるかに遅いため、SQLのアクセスパスが悪いなどしてディスクアクセス(Readsが大きい、ロードアベレージが高い状態)が増えると、I/O処理待ちとなり、パフォーマンスも悪化します。
その為、よくアクセスするデータをSGAになるべくキャッシュし、ディスクアクセスを減らす事が必要です。また、SQLのソート処理などにはPGAの領域が使われますが、この領域が不足すると一時表領域が使われ、この際にもディスクアクセスが発生するため、PGAのサイズについても調整が必要になります。
メモリチューニングの流れ
Oracleにおけるメモリ設定のチューニング対象は、SGAとPGAになりますが、これらのサイズをどの程度にすればよいかは、Oracleを利用するシステム(アプリケーション)や、データサイズにより異なるため、最適値を出すのが非常に難しいものでもあります。
というより、専任のDBAがベースラインを収集しながら、日々最適化しているようなシステムでもない限り、普通ではまず最適値を出すことは難しいと思います。
よって、現実解として、負荷テスト環境や本番環境で定期的にAWRやStatspackレポートを取得し、そこに記載されている、Memory Statisticsの情報、SGAやPGAに関するアドバイザリー情報を参考に、SGAおよびPGAのサイズを調整していくのが、メモリチューニングの基本的な流れになります。
※Statspackレポートの見方(詳細)は、別記事で記載します。
Memory Statisticsの確認
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 32,511.6 32,511.6
SGA use (MB): 24,466.3 24,466.3
PGA use (MB): 766.6 718.5
% Host Mem used for SGA+PGA: 77.6 77.5
上記から、ホスト(サーバーのメモリ)、SGAおよびPGAの使用状況(Statspackのスナップショットの開始時と終了時)が確認できます。
SGAとPGAの割り当て配分
一般的には、複雑でないSQLを使用するOLTPシステムでは、PGAは小さくし、SGAを大きくする(ソート処理を減らし、なるべく索引を利用するようにするため)ようにします。
DWHなどのOLTPシステム以外では、PGAを大きくし、SGAを小さくする(フルスキャンが多く、バッファキャッシュを増やしてもヒット率向上が見込めないため)ようにします。
メモリ設定の方針
Oracleのメモリ領域について、各領域毎に手動で細かく指定する事も可能ですが、システムの利用状況により、各領域に必要なサイズは変動します(例えば、昼間のシステム利用時と夜間のバッチ処理やバックアップ実行時とで必要な領域は変わります)。
Oracleには、「自動メモリ管理」、「自動共有メモリ管理」、「自動PGAメモリ管理」という自動的にメモリ領域を管理してくれる機能があるため、手動で細かく設定する事はせず、極力メモリ管理はOracleに任せ、SGAやPGAの上限値の指定のみを行うようにします(SGA内でバッファキャッシュや共有プールにどれ位メモリを割り当てるかはOracleに任せる)。
自動PGAメモリ管理
PGA全体でメモリをどの位使用するか指定し、PGAのメモリ管理をOracle任せにする機能です。
この機能は、初期化パラメータ「PGA_AGGREGATE_TARGET」を設定することで動作します。
この値はAWRやStatsPackの「PGA Memory Advisory」などを使用して、最適な値に設定します。
PGA Memory Advisoryでの確認方法
PGA Memory Advisory DB/Inst: XXXXXXX/xxxxxxx End Snap: 28977
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd Extra Estd
PGA Aggr W/A MB Estd Time PGA Estd PGA
Target Size W/A MB Read/Written to Process Cache Overalloc
Est (MB) Factr Processed to Disk Bytes (s) Hit % Count
------------- ------ ------------ -------------- ------------- -------- ----------
256 0.1 5,031,491 6,222,496 1,413.5 45.0 80,739
512 0.3 5,031,491 4,306,025 1,172.8 54.0 54,174
1,024 0.5 5,031,491 279,986 667.1 95.0 0
1,536 0.8 5,031,491 279,986 667.1 95.0 0
2,048 1.0 5,031,491 279,768 667.1 95.0 0
2,458 1.2 5,031,491 265,531 665.3 95.0 0
2,867 1.4 5,031,491 265,531 665.3 95.0 0
3,277 1.6 5,031,491 265,531 665.3 95.0 0
3,686 1.8 5,031,491 265,531 665.3 95.0 0
4,096 2.0 5,031,491 265,531 665.3 95.0 0
6,144 3.0 5,031,491 265,531 665.3 95.0 0
8,192 4.0 5,031,491 265,531 665.3 95.0 0
12,288 6.0 5,031,491 265,531 665.3 95.0 0
16,384 8.0 5,031,491 265,531 665.3 95.0 0
-------------------------------------------------------------
PGA Memory Advisoryでは、初期化パラメータ「pga_aggregate_target」のサイズを増減させる事により、一時表領域へのアクセスや、SQLのソート、結合等の処理効率がどの程度変化するか(の予想)が確認できます。
Size Factrが1.0になっている行が、現状設定での統計情報です。
Estd PGA Overalloc Countの値が0で、Estd Extra W/A MB Read/Written to Diskの値が一番小さくなるサイズに「pga_aggregate_target」の値を近づけるようにします。
ただし、1プロセスで使用できるメモリには上限がある点、PGA_AGGREGATE_TARGETで指定した値より多くのメモリが使われる場合がある点について、注意が必要です。
自動共有メモリ管理
SGAのバッファキャッシュとその他のプール(共有プール、ラージ・プールなど)間でサイズを自動調整する機能です。
この機能は、初期化パラメータ「SGA_TARGET」(及び「SGA_MAX_SIZE」)を設定することで動作します。
この値は、AWRやStatspackの「SGA Target Advisory」などを使用して、最適な値に設定します。
SGA Target Advisoryでの確認方法
SGA Target Advisory DB/Inst: XXXXXXX/xxxxxxx End Snap: 28977
SGA Target SGA Size Est DB Est DB Est Physical
Size (M) Factor Time (s) Time Factor Reads
---------- ---------- ---------- ------------ ----------------
6,144 .3 ######## 12.8 1,282,065,151
9,216 .4 ######## 2.6 201,119,277
12,288 .5 ######## 1.6 110,473,200
15,360 .6 835,956 1.3 71,734,051
18,432 .8 754,248 1.2 55,239,811
21,504 .9 660,063 1.0 36,180,085
24,576 1.0 639,844 1.0 32,108,702
27,648 1.1 607,340 .9 25,523,207
30,720 1.3 588,401 .9 21,699,061
33,792 1.4 567,350 .9 16,584,145
36,864 1.5 567,158 .9 14,875,962
39,936 1.6 567,030 .9 13,077,874
43,008 1.8 567,030 .9 12,008,655
46,080 1.9 566,966 .9 11,591,241
49,152 2.0 566,966 .9 11,591,241
SGA Target Advisoryでは、SGAのサイズを増減させる事により、DB時間やディスクアクセスがどの程度変化するのか(の予想)が確認できます。
SGA Size Factorが1.0になっている行が、現状設定での統計情報です。
SGAのサイズを増やす事で、Est DB Time (s)、Est Physical Readsの値が小さくなるようであれば、I/O待ちが少なくなり、処理性能が改善する可能性があります。
ただし、DB_nK_CACHE_SIZE、DB_KEEP_CACHE_SIZE、DB_RECYCLE_CACHE_SIZE、LOG_BUFFER(デフォルト値はCPU数とSGAサイズなどから算出される)は自動調整されないので注意が必要です。
自動メモリ管理について
Oracleのメモリ管理方式には、上記で説明した「自動PGAメモリ管理」、「自動共有メモリ管理」の他に、SGA、PGAのサイズを自動調整してくれる「自動メモリ管理」がありますが、「自動PGAメモリ管理」と「自動共有メモリ管理」組み合わせた方が柔軟に対応ができるため(私の個人見解です)、ここでは説明しません。
SGA内の領域を更に詳細に確認したい場合は
SGA内の各メモリ領域の管理をOracleに任せるとしても、SGAのうちデータベース・バッファキャッシュが足りていないのか、共有プールが足りていない(バインド変数を使っていない事で無駄なパースが発生しているなど)のか、もう少し詳細を確認したい場合もあると思います。
その場合は、AWRやStatspackレポートの「Buffer Pool Advisory」、「Shared Pool Advisory」、「Memory Dynamic Components」の情報を確認します。
Buffer Pool Advisoryの確認方法
Buffer Pool Advisory DB/Inst: XXXXXXX/xxxxxxx End Snap: 28977
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For Estimate
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
- ---------- ----- ------------ ------ ------------- ------------ ---------
D 2,112 .1 260 226.4 7,269,924 35,848,464 ######
D 4,224 .2 520 39.9 1,282,063 6,253,227 977.3
D 6,336 .3 780 6.3 201,122 910,634 142.3
D 8,448 .4 1,040 3.4 110,477 462,618 72.3
D 10,560 .5 1,299 2.7 85,871 341,004 53.3
D 12,672 .6 1,559 2.2 71,734 271,132 42.4
D 14,784 .7 1,819 1.7 55,242 189,616 29.6
D 16,896 .8 2,079 1.3 43,049 129,355 20.2
D 19,008 .9 2,339 1.1 36,181 95,411 14.9
D 21,120 1.0 2,599 1.0 32,109 75,283 11.8
D 23,232 1.1 2,859 0.9 28,971 59,774 9.3
D 25,344 1.2 3,119 0.8 25,524 42,734 6.7
D 27,456 1.3 3,378 0.7 21,700 23,833 3.7
D 29,568 1.4 3,638 0.6 18,814 9,573 1.5
D 31,680 1.5 3,898 0.5 16,583 2,724 .4
D 33,792 1.6 4,158 0.5 14,877 2,652 .4
Buffer Pool Advisoryでは、バッファキャッシュサイズを増減させる事により、ディスクアクセスがどう変化するか(の予想)が確認できます。
Size Factrが1.0となっている行が、現状設定での統計情報です。
バッファキャッシュサイズを増やす事でEstimated Phys Reads (thousands)、Est Phys Read Timeの値が小さくなる場合、I/O待ちが少なくなり、処理性能が改善する可能性があります。
SGA_TARGET指定をしている場合、デフォルトではバッファキャッシュサイズはOracleによって自動的に決められ、動的に変化します。しかし、最低でも〇〇MBはバッファキャッシュサイズを確保したいといった場合は、初期化パラメータ「DB_CACHE_SIZE」にサイズを指定する事で、その値をバッファキャッシュの最低サイズとして指定する事ができます。
※ただし、領域不足(ORA-4031など)が発生してしまうような場合は、発生しないようにすることが優先され、その最低サイズより小さくなる場合があります。
Shared Pool Advisoryの確認方法
Shared Pool Advisory DB/Inst: XXXXXXX/xxxxxxx End Snap: 28977
-> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
-> Note there is often a 1:Many correlation between a single logical object
in the Library Cache, and the physical number of memory objects associated
with it. Therefore comparing the number of Lib Cache objects (e.g. in
v$librarycache), with the number of Lib Cache Memory Objects is invalid
Est LC Est LC Est LC Est LC
Shared SP Est LC Time Time Load Load Est LC
Pool Size Size Est LC Saved Saved Time Time Mem
Size (M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits
---------- ----- -------- ------------ ------- ------ ------- ------- -----------
・・・・・・
2,240 .9 1,265 25,532 ####### .9 ####### 74.6 ###########
2,304 .9 1,329 26,345 ####### .9 ####### 56.3 ###########
2,368 .9 1,393 27,266 ####### 1.0 ####### 37.9 ###########
2,432 1.0 1,456 28,130 ####### 1.0 75,535 19.5 ###########
2,496 1.0 1,520 29,014 ####### 1.0 3,876 1.0 ###########
2,560 1.0 1,583 29,979 ####### 1.0 3,770 1.0 ###########
2,624 1.1 1,647 30,892 ####### 1.0 3,761 1.0 ###########
2,688 1.1 1,711 32,196 ####### 1.0 3,756 1.0 ###########
2,752 1.1 1,775 33,708 ####### 1.0 3,749 1.0 ###########
2,816 1.1 1,838 35,276 ####### 1.0 3,745 1.0 ###########
2,880 1.2 1,902 36,314 ####### 1.0 3,742 1.0 ###########
2,944 1.2 1,966 37,345 ####### 1.0 3,739 1.0 ###########
・・・・・・
Shared Pool Advisoryでは、共有プールのサイズを増減させる事により、ライブラリキャッシュへのアクセス効率がどう変化するか(の予想)が確認できます。
Size Factrが1.0となっている行が、現状設定での統計情報です。
共有プールのサイズを増やす事で、Est LC Time Saved (s)、Est LC Load Time (s)の値が小さくなる場合、ライブラリキャッシュへのアクセス効率が改善する可能性があります。
SGA_TARGET指定をしている場合、デフォルトでは共有プールのサイズはOracleによって自動的に決められ、動的に変化します。しかし、最低でも〇〇MBは共有プールのサイズを確保したいといった場合は、初期化パラメータ「SHARED_POOL_SIZE」にサイズを指定する事で、その値を共有プールの最低サイズとして指定する事ができます。
※ただし、領域不足(ORA-4031など)が発生してしまうような場合は、発生しないようにすることが優先され、その最低サイズより小さくなる場合があります。
Memory Dynamic Componentsの確認方法
Memory Dynamic Components DB/Inst: *******/******* Snaps: 28976-28977
-> Op - memory resize Operation
-> Cache: D: Default, K: Keep, R: Recycle
-> Mode: DEF: DEFerred mode, IMM: IMMediate mode
Begin Snap End Snap Op Last Op
Cache Size (M) Size (M) Count Type/Mode Last Op Time
--------------- ------------ -------- ------ ---------- ----------------
D:buffer cache 21,120 0 SHRINK/IMM 31-12月 08:32:36
PGA Target 2,048 0 STATIC
SGA Target 24,576 0 STATIC
Shared IO Pool 512 0 GROW/IMM 31-12月 08:32:36
java pool 128 0 STATIC
large pool 192 0 SHRINK/DEF 31-12月 07:51:17
shared pool 2,496 0 STATIC
Memory Dynamic Componentsでは、バッファキャッシュなどのコンポーネントが期間中にどのように変化したかを見ることができます。
「Last Op Type」は、最後の操作タイプがSHRINK(縮小)、GROW(拡張)、STATIC(変化なし)を示しています。
「Last Op Mode」は、最後の操作モードがDEFerred(統計情報を基にMMANプロセスが30秒間隔で行う基本操作モード)、IMMediate(起動時または枯渇した際に緊急的に拡張させるための操作モード)を示しています。
上記例では、共有プールが不足したため、バッファキャッシュのサイズを減らし、共有プールのサイズを増やした事を示しています。
(参考)使用頻度の高いデータブロックをKEEPバッファ・プールにキャッシュする
SGAのデータベース・バッファ・キャッシュにキャッシュされたデータがいっぱいになると、使用頻度の低いデータブロックは、キャッシュからエージアウトされ、必要なデータブロックが新たにキャッシュされます。
キャッシュに特定のオブジェクトのデータブロックが存在しないと、全体のパフォーマンスに大きな影響を及ぼすようなSQLがある場合に、SQLで扱うオブジェクトの設定でKEEPバッファ・プールにオブジェクトのデータブロックをキャッシュさせる(例：ALTER TABLE EXAMPLE_TBL STORAGE (BUFFER_POOL KEEP);)事で、データベース・バッファ・キャッシュより、エージアウトされにくくする事ができます。
KEEPバッファを利用する場合には、初期化パラメータ「DB_KEEP_CACHE_SIZE」で、KEEPバッファ・プールのサイズを指定します(キャッシュしたいオブジェクトが格納できるサイズにする必要があります)。
このパラメータは、本番環境などでアプリケーション側で対処ができない場合に、運用上暫定的な対応として使われるものです。
また、この領域は、自動共有メモリ管理(SGA_TARGET指定)の対象外である為、物理メモリの空きについても注意が必要です。
物理メモリとの兼ね合い
当たり前の話ではありますが、SGAやPGAに物理メモリの大半の領域を割り当ててしまうと、最悪の場合システム停止やページング、スラッシングの多発による大幅なパフォーマンス低下に繋がります。
その為、OSやOracle以外のM/Wが使うメモリも加味し、Oracleへのメモリ割り当てを行う必要があります。
目安としては、OSが使うメモリとして最低1GB、バックアップソフトや監視ソフトなどで使うメモリとして最低2GB、更にバッファとして1GB程度の合計4GB程度は、Oracle以外で使うものと想定し、残りのメモリ(物理メモリが8GBであれば、4GB程度)をOracle(SGA、PGAなど)に割り当てるようにします。
※物理メモリが多い環境では、Oracleに多くのメモリを割り当て可能ですが、監視設定で物理メモリの90%以上使用はアラート通知するなどの設定がされる場合があるため、予め調整が必要になる場合があります(コンテンツによっては、Oracleには物理メモリの半分程度割り当てると記載しているものもありますが、物理メモリに余裕がある場合は、半分以上のメモリを割り当てても問題ありません)。
※DBサーバーに、Oracle以外にどのようなソフトウェアがインストールされる可能性があり、それらがどの程度メモリを必要とするかは、サーバースペック見積もり前に確認する必要があります。
※上述したKEEPバッファを利用する場合には、SGA、PGAのサイズに加え、KEEPバッファのサイズも加味する必要があります。
PGAで必要となるメモリ見積もりの際の注意点
Oracleでは、SGAとPGAに割り当てるメモリを検討する必要がありますが、PGAについては、最大、同時アクティブDBセッション数×SQL実行時の平均使用メモリ分のメモリが必要となるため注意が必要です。
※この点は、DBサーバーのメモリのサイジング時にも考慮が必要です。アプリケーションによっては、1DBセッションあたり100MB程度のメモリが必要になる事もあります。
1DBセッションあたり、SQLでどの程度のメモリが必要になるかは、Linux環境などでは、ps auxコマンドなどで確認できます。Windows環境では、Statspackレポートなどで、PGAの使用サイズと、平均アクティブDBセッション数を確認の上、必要メモリを確認します。
SGAについては、設定値以上のメモリが使用される事はありませんが、PGAについては、初期化パラメータ「PGA_AGGREGATE_TARGET」で指定したサイズ以上のメモリが使われる場合があり、アプリケーションによりメモリ使用量が変動するため、どの程度PGAが使われるのかを把握した上で、OSのメモリが溢れないよう確認が必要です。
おわりに
ここまで、Oracleのメモリ設定の考え方について記載してみました。
Oracleのメモリをチューニングするにあたり、Oracleのメモリ管理に仕組み、各領域の用途について理解している事が前提ではあるのですが、それだけでは適切なメモリチューニングを行う事は難しいのが実情です。
sar、vmstat、dstat、パフォーマンスモニターなどのOSのパフォーマンスログ、AWR、Statspackレポートなどから、チューニングに必要な情報を収集しつつ、この記事がOracleのパフォーマンスチューニングに携わる方々の役に少しでも立てば幸いです。
次回は、メモリ設定、初期化パラメータ設定以外で、Oracleのパフォーマンス改善に寄与できる可能性がある設定について記載してみようと思います。