Oracleのメモリ管理について(8i−9i)
1、概要
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アーキテクチャに参照
2、SGAのサイズ
次にメモリパラメータの最適設定について、実は特定の環境に対してそれぞれ 異なる最適設定が存在し、汎用的な最適設定は存在しない。 ではなぜここで検討するかというと、目的はひとつで、エラーを犯さないことである。 事実上、任意の製品システムが正式納入する前に、実際の運用データがない状態で チューニングしているため、可能はa.ドキュメントに基づいて設定する、b.経験により設定する。 この二つしかない。相対的にいうと、経験ベースの設定値がドキュメントより良いと考えている。 特に24*7のシステムにおいては。 仮な環境で、サーバ上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に書き込み完了として知らせる。この部分のファイルバッファーは比較的大きな メモリエリアが必要(通常02−0.3倍メモリ大きさ)。ただし、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が 100個の場合、上記4つのバラメータがOracle8iでのデフォルト値により、 専用モードでPGAのサイズを計算してみる。 セッションはあまりcreate_bitmap_area_sizeとbitmap_merge_area_sizeを使用しないため、 この4つのパラメータに対して和を求めない。他にセッション変数、 スタック等の情報を考慮すると2Mと推定し、100プロセスの場合は200MB位のPGAとなる。 上記の仮定に基づいて、SGAはどれぐらいになるか? 1GBのメモリが実装されたサーバ上SGAに配分できるメモリの大きさは約400-500MB。 2GBのメモリの場合約1GBまで割り当てられ、8GBの場合5GBまで割り当てられる。 ここではデフォルトのソートエリアsort_area_size=64Kで計算している。 通常では下記の式で表している。(注:あくまで参考) OS利用メモリサイズ + SGA + 同時接続プロセス数 ×(sort_area_size+hash_area_size+2MB) < 0.7 × トータル物理メモリサイズ 実際、RAWデバイスを使用する場合SGAを若干増やしても良い。 現時点の主なOSはバーチャルキャッシュを使用するため、実際SGAを比較的に 多く設定してもエラーにならないと考える。その代わりに、頻繁にページイン・ページアウト が発生する。その場合メモリの設定を調整する必要がある。
3、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が設定された場合、この部分も含まれる。
4、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パラメータは無視される。
5、メモリパラメータの調整について
パラメータの調整はOracleの複雑性の一つである。
通常ではユーザ側のstatspackとOS監視状況により行う。
(1)メモリの調整はまずデータバッファーのビット率を観察する。 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 ここでヒット率の計算式は x=physical reads direct+physical reads direct (lob) ヒット率=100−(physical reads − x)÷(consistent gets+db block gets−x)×100 ヒット率が90%以下なら、データバッファーを増やすことをお勧めする。
(2)次にshared_poolのヒット率を観察する SQL> select sum(pinhits-reloads)/sum(pins)*100 "hit radio" from v$librarycache; hit radio ---------- 86.1785892 Shared_poolのヒット率が95%以下なら、アプリを調整する(Bind変数の利用)か メモリを増やすかをおすすめする。
(3)ソートについて 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が小さいことを示している。
(4)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の比例が1%以上の場合、 log_bufferを増やしたほうが良いでしょう。