/* Grid Thinking */

Oracleのメモリ管理について（8i−9i）

DB

１、概要

　Oracleのメモリ管理はSGA（System Global Area）とPGA（Private Global Area）に分かれている。
　SGAに関しては、共用グローバルであり、UNIX系OSではマルチプロセスであるため、
Oracle用の共用メモリセグメントを設定する必要がある（一つ若しくは複数）。
一方、Windowsではシングルプロセスマルチスレッドであるため、
共用メモリセグメントを設定する必要がない。
PGAはプロセス（スレッド）のプライベートエリアである。
Oracleが共用サーバモード（MTS）の場合、PGAの一部UGAが共用メモリlarge_pool_sizeに含まれる。
　SGAに関しては、Sqlplusを通して調べられる。

SQL> show sga;

Total System Global Area  285212672 bytes
Fixed Size                  1218968 bytes
Variable Size              75499112 bytes
Database Buffers          201326592 bytes
Redo Buffers                7168000 bytes
Fixed Size：
　Oracleのバージョンによって違うかもしれないが、環境を特定するための固定値で、
SGA各コンポーネントの情報が含まれて、SGAを作成するブートエリアとも言える。

Variable Size：
　Shared_pool_size、java_pool_size、large_pool_size等のメモリ設定が含まれている。

Database Buffers：
　データバッファーのことで、8iではdb_block_buffer＊db_block_size、
buffer_pool_keep、buffer_pool_recycleの三つのエリアが含まれる。
9iではdb_cache_size、db_keep_cache_size、db_recycle_cache_size、
db_nk_cache_sizeが含まれる。

Redo Buffers：
　Redoログバッファーであるが、v$parameter、v$sgastat、v$sgaから取得した値は
それぞれ違う可能性がある。v$parameterの値はユーザが初期化パラメータに設定した値である。
v$sgastatの値はOracle実際割り当てたRedoログバッファーのサイズ
（バッファーは実際連続エリアでもなく、最小Blockサイズ単位での割り当てでもない）、
v$sgaの値はOracleがRedoログバッファーを割り当てた後に、バッファーを保護するために、
ある程度保護ページも設定している。（環境によって異なる可能性あり）
参考：
SQL> select substr(name,1,10) name, substr(value,1,10) value from v$parameter where name='log_buffer';

NAME                                     VALUE
-------------------------------------------------------------------------------                                                                                        
log_buffer                               7057408
SQL> select * from v$sgastat where name='log_buffer';

POOL         NAME                            BYTES
------------ -------------------------- ----------
             log_buffer                    7168000
SQL> select * from v$sga where name='Redo Buffers';

NAME                      VALUE
-------------------- ----------
Redo Buffers            7168000
＊それぞれのメモリの用途はOracleアーキテクチャに参照

２、SGAのサイズ

　次にメモリパラメータの最適設定について、実は特定の環境に対してそれぞれ
異なる最適設定が存在し、汎用的な最適設定は存在しない。
ではなぜここで検討するかというと、目的はひとつで、エラーを犯さないことである。
事実上、任意の製品システムが正式納入する前に、実際の運用データがない状態で
チューニングしているため、可能はa.ドキュメントに基づいて設定する、b.経験により設定する。
この二つしかない。相対的にいうと、経験ベースの設定値がドキュメントより良いと考えている。
特に２４＊７のシステムにおいては。
　仮な環境で、サーバ上OSとDBしか存在しないと考え、単純な環境でメモリの配分を考える。
その前にいくつか質問をしてみる。
(1)物理メモリはいくつか？
(2)OSはどれぐらいのメモリが必要か？
(3)DBはファイルシステムかRAWか？
(4)同時接続数はいくつか？
(5)応用モデルはOLTPかOLAP（DWH）か？
　これらの質問の答えから、大まかにシステムのメモリ設定を確定できる。
まず物理メモリの大きさの質問が一番簡単で、次に、OSがどれぐらいメモリを使用するか？
経験上、そう多くない、通常200MB以内（大量PCB（プロセス制御ブロック）が含まない場合）。
次にFS（ファイルシステム）とRAWデバイスについて、OSがFSに対して、
大量にBufferを利用してOSブロックをキャッシュしている。そうすると、
データベースからデータブロックを抽出する場合、SGAにないけど、実際OSのファイルバッファー
から取得可能。仮にデータベースとOSが非同期IOをサポートしている場合、
データベースDBWRプロセスがディスクに書き込むとき、OSがファイルバッファーに
Write Delayとしてマークし、本当にディクスに書き込んだ後に、
OSがDBWRに書き込み完了として知らせる。この部分のファイルバッファーは比較的大きな
メモリエリアが必要（通常０２−０．３倍メモリ大きさ）。ただし、RAWを利用する場合、
このバッファーがないため、SGAをさらに引き上げる可能がある。
　データベースはどれ位の同時接続があるかはPGAの大きさ（MTSの場合large_pool_sizeも）
に関わる。実際OLTPとOLAPにも関係がある。OLTPの場合OracleがMTSを利用する傾向にある、
OLAPの場合独立モードを利用する。まとめてUGAに反映する場合が多い。
UGAは下記エリアが含まれている。
SQL> show parameter area_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size               integer     1048576
create_bitmap_area_size              integer     8388608
hash_area_size                       integer     131072
sort_area_size                       integer     65536
一番注目されているのはおそらくsort_area_sizeである。これはSelectがソートする場合、
データベースがこのエリアを利用してソートする。メモリが足りない場合、
TEMP表領域を利用してディスクソートを行う。ディスクとメモリの効率差は桁単位なので、
このパラメータの設定がとても重要である。この四つのパラメータはセッション単位のため、
データベース全体ではなく、セッション別のメモリサイズであることを間違いないように。
MTSが設定された場合UGAはlarge_pool_sizeに割り当てる、つまり共用メモリにあり、
異なるプロセス（スレッド）間で共用可能。これに基づいて仮に同時接続ServerProcessが
１００個の場合、上記４つのバラメータがOracle8iでのデフォルト値により、
専用モードでPGAのサイズを計算してみる。
セッションはあまりcreate_bitmap_area_sizeとbitmap_merge_area_sizeを使用しないため、
この4つのパラメータに対して和を求めない。他にセッション変数、
スタック等の情報を考慮すると２Mと推定し、１００プロセスの場合は200MB位のPGAとなる。
　上記の仮定に基づいて、SGAはどれぐらいになるか？
1GBのメモリが実装されたサーバ上SGAに配分できるメモリの大きさは約400-500MB。
２GBのメモリの場合約１GBまで割り当てられ、８GBの場合５GBまで割り当てられる。
ここではデフォルトのソートエリアsort_area_size=64Kで計算している。
　通常では下記の式で表している。（注：あくまで参考）

OS利用メモリサイズ ＋ SGA ＋ 同時接続プロセス数
 ×（sort_area_size＋hash_area_size＋2MB）　
     ＜　0.7 × トータル物理メモリサイズ

　実際、RAWデバイスを使用する場合SGAを若干増やしても良い。
現時点の主なOSはバーチャルキャッシュを使用するため、実際SGAを比較的に
多く設定してもエラーにならないと考える。その代わりに、頻繁にページイン・ページアウト
が発生する。その場合メモリの設定を調整する必要がある。

３、SGAのパラメータ設定（参考）

Log_buffer：
　Redoログバッファサイズに関して、LGWRのトリガー条件により、
通常3MBを超えてもあまり意味がない。
納入システムの場合大抵log_buffer=1−3MBで設定し、運用状況によって調整する。

Large_pool_size：
　MTS使わない場合、20−30MBで十分と考える。同時Select時の情報とRMANで利用される。
MTSの場合UGAも移ってくるため、具体的なサーバプロセス数とセッションメモリパラメータ
によって設定する。

Java_pool_size：
　データベースがJava使っていない場合、通常10−20MBで十分と考える。
実際もっと少なくても良い、最低32KBでも稼動可能だが、
インストール時のコンポーネント次第（例HttpServer等）

Shared_pool_size：
　この部分は一番争議のある設定である。
一部のドキュメントではデータバッファーと同じぐらいのサイズが望まれるが、
実際異なる場合が多い。まず、この部分の用途は解析後のSQLバッファーとして利用されている。
新しいSQL文（shared_poolに解析後の同じSQLがない）に対して、
データベースが再度ハード解析を行う。これは大変処理時間の掛かる作業である。
もし解析済みのSQLが存在している場合、shared_poolから探すソフト解析だけで、
利用リソースが少なくて済む。なのでできるだけたくさんのSQLを共用したい。
このパラメータの設定が小さい場合、ora-04031のエラーが頻繁に発生する。
新しいSQLを解析するのに十分な連続メモリがない。
ただし、このパラメータが大きすぎると、バッファーしたSQLを管理する効率が悪くなる。
通常ではshared_pool_sizeは300MB以内に維持したい。大量にストアードプロシージャー、
関数、パッケージ等を使用した場合（例OracleERP）500MB超える可能性はある。
仮に1GBのメモリを持つシステムの場合100MB、2GBメモリがある場合150MB、
8GBメモリのある場合は200−300MBにしたほうがいいでしょう。
　Bind変数を利用しないシステムでは殆どLiteral SQLのため、問題が起こりやすい。
例えば下記のSQLは通常異なるSQLと認識し再度ハード解析が必要：
　Select * from EMP where name='TOM’;
　Select * from EMP where name='JOHN’;
‘TOM’と‘JOHN’を変数Varに変えたらBind変数を利用しているため、SQLが共用できる。
システムを監視し、大変なCPU問題がなければShared_poolのヒット率を上げるため
shared_pool_sizeを大きく設定しても良いが、通常800MBを超えないこと方がよいでしょう。
　実際、可能であればソフト解析もできるだけ回避したいところだ。
Session_cached_cursorsを設定することで一部解消できる（PGAが増大する）

Data buffer：
　SGAの大きさが決まれば残りは全部データバッファーに回す。
このエリアの主な用途はDBブロックをキャッシューすることで、
できるだけディスクへのIOアクセスを軽減すること。
8iではdb_block_buffers*db_block_sizeで決められている。
Buffer_pool_keepとbuffer_pool_recycleが設定された場合、この部分も含まれる。

４、9iでのパラメータ変化

　Oracleバージョンの更新いつもパラメータの更新に伴い、さらなる簡潔化に繋がる。
実際9iではデータベース自身からシステムチューニング参考値を取得可能
（v$db_cache_advice, v$shared_pool_advice）、
PGAに関してもv$pga_target_adviceビューが存在する。
Data buffer：
　9iでは8iの殆どを保留したが、新たにパラメータを設け、旧パラメータを無視している。
例えば9iではdb_block_buffersの代わりにdb_cache_sizeを採用した、
buffer_pool_keepの代わりにdb_keep_cache_sizeを採用した、
buffer_pool_recycleの代わりにdb_recycle_cache_sizeを採用した。
注意するのは9iで設定したのは実際のバッファーサイズでブロック数ではないこと。
9iでdb_nk_cache_sizeが追加されて、同じデータベース上ことなるブロックサイズを
利用可能になった。Db_block_lru_latchesは手動設定しない方が良い。

PGA：
　9iでは大きく変化した。専用モードでは従来のUGA関連パラメータではなく、
新しいパラメータ例えばworkarea_size_policy=AUTO（デフォルト）等、
pga_aggregate_target次第で、他の*_area_sizeパラメータは無視される。

５、メモリパラメータの調整について

　パラメータの調整はOracleの複雑性の一つである。

通常ではユーザ側のstatspackとOS監視状況により行う。

（１）メモリの調整はまずデータバッファーのビット率を観察する。
SQL> select value from v$sysstat where name='physical reads';

     VALUE
----------
     28278

SQL> select value from v$sysstat where name='physical reads direct';

     VALUE
----------
        46

SQL> select value from v$sysstat where name ='physical reads direct (lob)';

     VALUE
----------
         0

SQL> select value from v$sysstat where name ='consistent gets';

     VALUE
----------
   3767104

SQL> select value from v$sysstat where name = 'db block gets';

     VALUE
----------
    506965
ここでヒット率の計算式は

 ｘ＝physical reads direct＋physical reads direct (lob)
 ヒット率＝100−（physical reads − x）÷（consistent gets＋db block gets−x）×100

ヒット率が９０％以下なら、データバッファーを増やすことをお勧めする。

（２）次にshared_poolのヒット率を観察する

SQL> select sum(pinhits-reloads)/sum(pins)*100 "hit radio" from v$librarycache;

 hit radio
----------
86.1785892
Shared_poolのヒット率が９５％以下なら、アプリを調整する（Bind変数の利用）か
メモリを増やすかをおすすめする。

（３）ソートについて
SQL> select name,value from v$sysstat where name like '%sort%';

NAME                            VALUE
------------------------------- ----------
sorts (memory)                  157812
sorts (disk)                         0
sorts (rows)                    7734343
sorts(disk)÷（sorts(memory)＋sorts(disk)）の比例が高いなら、
sort_area_sizeが小さいことを示している。

（４）log_bufferについて
SQL> select name,value from v$sysstat 
  where name in('redo entries','redo buffer allocation retries');

NAME                                  VALUE
------------------------------------- ----------
redo entries                          361607
redo buffer allocation retries        3
もし、redo buffer allocation retries/ redo entriesの比例が１％以上の場合、
log_bufferを増やしたほうが良いでしょう。

Permalink | コメント(0) | トラックバック(1) | 17:54