|
|
 |
 |
 |
第15章 データベース・バッファ・キャッシュのチューニングを学ぶ |
|
|
|
データベース・バッファ・キャッシュのチューニングは、ディスクI/Oを低減させることでパフォーマンスダウンを防ぐのが目的です。ディスクへの読込みや書込みは、メモリーに比べるとかなり時間がかかりパフォーマンスが低下する可能性があるので、必要なOracleブロックがメモリー内に存在できるように調整する必要があります。
第10章で復習したような共有プールの調整を行うと、残りの利用可能なメモリーをデータベース・バッファ・キャッシュに割当てることができ、よりディスクI/Oを低減できるのでパフォーマンスチューニングには有効です。
本章では、データベース・バッファ・キャッシュのチューニングについて復習しましょう。
|
|
|
 データベース・バッファ・キャッシュの構成
|
まず、データベース・バッファ・キャッシュの構成について復習します。
(1)データベース・バッファ・キャッシュの役割
SQL実行時にサーバー・プロセスがデータベース・バッファ内に必要なデータが存在するか調査し、データベース・バッファ・キャッシュ内に必要なデータ・ブロックがなかった場合は、サーバー・プロセスはデータファイルからデータベース・バッファ・キャッシュにデータ・ブロックを読み込みます。それ以降の処理で同じデータ・ブロックに対するアクセスが必要な場合は、既にデータベース・バッファ・キャッシュ上に読込まれているので、ディスク上のデータファイルにアクセスする必要が無くなります。
つまり、データベース・バッファ・キャッシュ上に必要なデータ・ブロックが存在する確率が高いほど、ディスクI/Oが発生しないためパフォーマンスが向上するということになります。
データベース・バッファ・キャッシュのサイズを指定する、初期化パラメータDB_CACHE_SIZEの設定概要を表1に記します。
表1
| DB_CACHE_SIZEパラメータ |
| 概要 |
データベース・バッファ・キャッシュのサイズをバイトで指定 |
| 構文 |
DB_CACHE_SIZE = n [ K | M | G ] |
| デフォルト |
48M |
| 変更の可/不可 |
可能：LTER SYSTEM ※R8.1.Xまでは静的 |
注意点ですが、R8.1.Xまでは、DB_BLOCK_BUFFERSパラメータでバッファ・キャッシュのサイズを決定します。このパラメータはバイト指定ではなく、バッファ数（ブロック数）で指定します。
(2)データベース・バッファ・キャッシュの構成
データベース・バッファ・キャッシュは、書込みリストおよび最低使用頻度(LRU)リストで管理されています。
書込みリストはデータベース・バッファ内で変更された状態だがディスクに書き込まれていないという、使用済みバッファを保持します。
LRUリストとは、データベース・バッファ内で最近読み込まれたバッファ、使用可能バッファ、使用中バッファおよび書込みリストに移動していない使用済みバッファを保持します。
使用可能バッファは、有効なデータが含まれていない上書き可能なバッファです。使用中バッファは、現在アクセスされているバッファです。
サーバー・プロセスがバッファにアクセスするとき、そのバッファをLRUリストの最高使用頻度側(MRU)に移動します。
また、全表スキャンを実行するときには、デフォルトでは表のブロックをバッファに読込んでLRUリストのMRU側ではなくLRU側に入れます。
以下にデータベース・バッファ・キャッシュの構成のLRU、MRUリストについて、図1で説明します。
図1：データベース・バッファ・キャッシュ構成図
|
データベース・バッファ・キャッシュは図1のように使用されます。
①サーバー・プロセスがデータベース・バッファ・キャッシュ内に必要なデータが存在しているか検索する。
②データが見つからない場合、サーバー・プロセスはデータ・ブロックを読込むために必要な使用可能バッファ
があるか、LRUリストのLRU(最低使用頻度)側から検索する。
③LRUリストの検索時に使用済みバッファを見つけた場合、サーバー・プロセスはそのバッファを書込みリスト
に移動させる。
④使用可能バッファを見つけると、サーバー・プロセスはデータ・ファイルからデータ・ブロックを読込む。
⑤バッファをLRUリストのMRU(最高使用頻度)側に配置する。
|
データベース・バッファ・キャッシュのチューニング
|
上記でデータベース・バッファ・キャッシュの構成を復習しましたので、次にデータベース・バッファ・キャッシュのチューニング方法を説明します。
(1)バッファ・キャッシュ・ヒット率
バッファ・キャッシュ・ヒット率とは、メモリー上にアクセスしたいブロックが存在した（ディスクからブロックを読込まずに済んだ）割合です。ヒット率が高いほどデータベース・バッファ内に必要なデータが存在しており、メモリー上で効率的にデータ・ブロックの読込みが行われているという事になります。
ヒット率はV$SYSSTATビューを参照することにより確認できます。一般的には、ヒット率90％以上が目標です。
■バッファ・キャッシュ・ヒット率の計算
V$SYSSTATビューから以下の統計情報を調べてヒット率を計算します。
| ・physical reads |
：ディスクから読込まれたブロック数 |
| ・session logical reads |
：(session logical reads = db block gets + consistent gets)
アクセスしたブロックの総数 |
| ・physical reads direct |
：バッファ・キャッシュをバイパスして読み込まれたブロック数
（LOBのダイレクト読込みは除く） |
| ・physical reads direct(lob) |
：バッファ・キャッシュをバイパスして、LOBの読み込み中に読み込ま
れたブロック数。 |
ヒット率は以下のように算出します。
ヒット率 = 1 －
((physical reads － physical reads direct － physical reads direct(lob))
/ session logical reads)
ダイレクト処理などが最初から引かれた値を示す、physical reads cacheという統計情報が10gから提供されているので、R10.1からは、以下の計算式で算出します。
ヒット率 = 1 － (physical reads cache /
(consistent gets from cache + db block gets from cache))
また、ヒット率が非常に高い値を示していても、必ずしも最高のパフォーマンスであるとは限りません。小さな表の全表スキャンを繰返したり、相関副問合せを頻繁に使用している場合は、ヒット率が非常に高い値になる場合があります。
以下にヒット率の調査例を記します。
例)データベース・バッファ・キャッシュのヒット率を調査する |
SQL> SELECT 1 - ((SUM(DECODE(name,'physical reads cache',value,0))) /
2 (SUM(DECODE(name,'consistent gets from cache',value,0)) +
3 SUM(DECODE(name,'db block gets from cache',value,0)))) "HIT RATIO"
4 FROM v$sysstat;
| HIT RATIO |
|
| ------------- |
| .967882852 |
|
|
|
上記の結果から、データベース･バッファ・キャッシュヒット率は96.7%ということが分かります。
<<データベース・バッファ・キャッシュ関連する待機イベント>>
表2にデータベース・バッファ・キャッシュに関する待機イベントを示します。I/O関連のイベントが発生していないのであれば、キャッシュ・ヒット率が低くてもパフォーマンス上の問題はありません。
表2
| イベント名 |
説明 |
| db file scattered read |
全表スキャンによる読込みで発生する待機。全表スキャンが実行されるとこの待機は発生する。ただし、待機時間が非常に長い場合は、SQLのアクセス・パス、I/Oに問題がある可能性が高い。 |
| db file sequential read |
索引スキャンによる読込みで発生する待機。db file scattered read同様、待機時間が非常に長い場合は、I/Oに問題がある可能性が高い。 |
| free buffer waits |
データベース・バッファ・キャッシュに空きバッファがない場合に発生する待機。データベース・バッファ・キャッシュが小さすぎるか、DBWRの書込みが遅延していることを示す。 |
| buffer busy waits |
複数のプロセスが同じバッファに対してアクセスすることによって発生する待機。ブロック競合を解消する。 |
| latch: cache buffers chains |
cache buffers chainsラッチはバッファ・キャッシュでバッファ・リストを保護する場合に使用される。このラッチの待機はアクセス頻度が高いホットブロックが存在することを意味する。ブロック競合を解消する。 |
| latch: cache buffers lru chain |
キャッシュ内のバッファのリストを保護するラッチの競合。複数バッファ・プールを利用するとラッチ数が増加するため、競合が減少する可能性がある。 |
|
| R9.2まではラッチ競合による待機イベントは、「latch free」で示されますのでご注意ください。 |
(2)データベース・バッファ・キャッシュの効率化
データベース･バッファ・キャッシュを効率的に使用するにあたりヒット率が非常に低い場合や、待機イベントが問題となっている場合には以下の点を確認してください。
■大量ブロックにアクセスしているSQLの見直し
データベース・バッファ・キャッシュの待機イベントが問題となっているような場合のほとんどは、SQLが効率化されていないことが大きな原因です。全表スキャンしているSQLなどを見直してください。
■データベース・バッファ・キャッシュのサイズ増加
DB_CACHE_SIZEパラメータを変更し、データベース・バッファ・キャッシュのサイズを増加することを検討します。増加して効果がみられた場合は、さらに増加を検討します。
■複数バッファ・プールの使用
一般に、ほとんどのシステムでは1つのバッファ・キャッシュが適切です。しかし、頻繁にアクセスされるオブジェクトとほとんどアクセスされないオブジェクトを明確に分けることが可能な場合は、用途に合わせた複数のバッファ・プールを用意できます。複数バッファ・プールについては、第16章で説明します。
■DBWRプロセスの増加
ヒット率が適切で、I/Oも均等に分散できているにも関わらず、free buffer waits待機イベントでの待機時間が長いような場合は、DBWRプロセスの数を増加することを検討します。
複数のDBWRプロセスは、I/O負荷を分散するだけでなく、書出し前のLRUリストの走査といった作業も分散されます。
DBWRプロセス数を増加する場合は、DB_WRITER_PROCESSESパラメータでプロセス数を指定します。
以下の表3にDB_WRITER_PROCESSESパラメータの設定概要を記します。
表3
| DB_WRITER_PROCESSESパラメータ |
| 概要 |
DBWRプロセスの数を指定する |
| 構文 |
DB_WRITER_PROCESSES = 整数 |
| デフォルト |
R9.0.1から：1、またはCPU_COUNT/8のいずれか大きいほう
R8.0.Xまで：1 |
| 変更の可/不可 |
静的 |
|
| NOTE |
： |
大量のブロックに対するアクセスが発生する意思決定支援システムでは、バッファ・キャッシュのチューニングはそれほど重要ではなく、I/Oチューニングが必須となる場合があります。
|
|
以下に、データベース・バッファ・キャッシュにキャッシュされているセグメントの調査例を記します。
例）データベース・バッファ・キャッシュにキャッシュされているセグメントを調べる。
SQL> SELECT o.object_name,count(*) number_of_blocks
2 FROM dba_objects o,v$bh bh
3 WHERE o.data_object_id = bh.objd
4 AND o.owner NOT IN('SYS','SYSTEM','SYSMAN')
5 GROUP BY o.object_name
6 ORDER BY count(*);
| OBJECT_NAME |
NUMBER_OF_BLOCKS |
| ------------------------------ |
---------------- |
| DEPT |
6 |
| EMP |
127 |
|
V$BHビューは、現在SGA内に存在するすべてのブロックのデータ・オブジェクトIDを示します。
|
| データベース・バッファ・キャッシュのアドバイザ機能 |
バッファ・キャッシュのサイズを変更する際に上記に記述したような調整をする方法の他に、バッファ・キャッシュ・アドバイザを利用して見積もることができます。
アドバイザ機能を有効にして代表的な処理負荷を実行したあとV$DB_CACHE_ADVICEビューを問いあわせて使用し、現在のバッファ・サイズの10％～200％のサイズにしたときの物理読込みブロック予測値を参照できます。
設定の方法は、DB_CACHE_ADVICEパラメータをONに設定し、アドバイザ機能を有効します。
以下の表4にDB_CACHE_ADVICEパラメータの設定概要を記します。
表4
| DB_CACHE_ADVICEパラメータ |
| 概要 |
バッファ・キャッシュ・アドバイザを有効または無効にする |
| 構文 |
DB_CACHE_ADVICE = { ON | OFF | READY } |
| デフォルト |
R9.2から ：STATISTICS_LEVELパラメータのデフォルトががTYPICALで
あるため有効
R9.0.1 ：OFF(無効) |
| 変更の可/不可 |
可/ALTER SYSTEM |
V$DB_CACHE_ADVICEビューは、データベース・バッファ・キャッシュのサイズを変更した場合の、物理読取り回数の推定値に関する情報が表示されます。現在設定しているサイズの10％～200％（この範囲は設定されているサイズによって変わります）における見積り値を表示します。
主な列は以下です。
<主な列>
| ・ＩＤ |
：バッファ・プールのID |
| ・ＮＡＭＥ |
：バッファ・プール名 |
| ・BLOCK_SIZE |
：このプールのブロック・サイズ |
| ・ADVICE_STATUS |
：アドバイザ機能の状態。ONは現在収集中。
OFFは以前の履歴 |
| ・SIZE_FOR_ESTIMATE |
：測定用のキャッシュ・サイズ（MB単位） |
| ・BUFFERS_FOR_ESTIMATE |
：測定用のバッファ数 |
| ・ESTD_PHYSICAL_READ_FACTOR |
：実際の物理読込み数に対する、仮想サイズで見積もった
物理読込みの比率 |
| ・ESTD_PHYSICAL_READS |
：仮想サイズで見積もった物理ブロック読込み数 |
| ・ESTD_PHYSICAL_READ_TIME |
：見積もったディスク読取り時間 |
現在のバッファ・キャッシュのサイズを変動させると物理ブロック読込み量がどのように変化するか、についてバッファ・キャッシュ・アドバイザ機能を使用して調査した例を以下に記します。
例）現在のバッファ・キャッシュのサイズを変動させるとどのように物理ブロック読込み量
が変化するか
SQL> SELECT name,buffers_for_estimate buffers,
2 estd_physical_read_factor physic_factor,
3 estd_physical_reads physical_reads
4 FROM v$db_cache_advice
5 WHERE name = 'DEFAULT';
| NAME |
BUFFERS |
PHYSIC_FACTOR |
PHYSICAL_READS |
| ---------- |
------- |
-------------- |
--------------- |
| DEFAULT |
501 |
1.1256 |
446744 |
| DEFAULT |
1002 |
1.0728 |
425776 |
| DEFAULT |
1503 |
1.0564 |
419257 |
| DEFAULT |
2004 |
1.0515 |
417307 |
| DEFAULT |
2505 |
1.0389 |
412303 |
| DEFAULT |
3006 |
1.0312 |
409261 |
| DEFAULT |
3507 |
1.0197 |
404680 |
| DEFAULT |
4008 |
1.0196 |
404668 |
| DEFAULT |
4509 |
1.016 |
403211 |
| DEFAULT |
5010 |
1.0113 |
401355 |
| DEFAULT |
5511 |
1.0078 |
399969 |
| DEFAULT |
6012 |
1.0059 |
399229 |
| DEFAULT |
6513 |
1.0038 |
398395 |
| DEFAULT |
7014 |
1.002 |
397655 |
| DEFAULT |
7515 |
1 |
396880 |
| DEFAULT |
8016 |
.9974 |
395835 |
| DEFAULT |
8517 |
.9837 |
390396 |
| DEFAULT |
9018 |
.9281 |
368336 |
| DEFAULT |
9519 |
.3381 |
134204 |
| DEFAULT |
10020 |
.3233 |
128296 |
|
実際の物理読込み数に対する、仮想サイズで見積もった物理読込みの比率が1になっている行が現在の状況です。
よって、色の付いている行となります。
上記の結果から、バッファを増加することにより物理読込みブロックが減少していることが分かります。特に9519バッファ以上にすると大幅に読込むブロックが減っていることが分かります。
|
| 全表スキャンのチューニング |
次に全表スキャンのチューニングについて説明します。
(1)CACHE句
全表スキャンによって読込まれたブロックは、すぐにバッファからフラッシュされるようにLRUリストの末尾に配置されます。
また小規模表（バッファ・キャッシュ・サイズの2％未満の表）は、全表スキャンされた場合もLRUリストのMRU側に配置されるという動作になります。
小規模表ではない表でもCACHE句を設定していれば、全表スキャンで読込まれたブロックがLRUリストのMRU側に置かれます。よって全表スキャンによるアクセスが頻繁にあるオブジェクトに対してはCACHE句の指定を検討します。そうする事で効率的に使用する事ができるのでヒット率が上がりディスクI/Oを低減できます。ただし、大規模な表にCACHE句を指定すると、ヒット率を低下させる可能性があるので注意が必要です。
SQLの構文は以下です。
・表の作成時 CREATE TABLE 表名…CACHE
・表の変更 ALTER TABLE 表名 CACHE
・SQLで使用 SELECT /*+CACHE (表名)*/
(2)DB_FILE_MULTIBLOCK_READ_COUNTパラメータ
DB_FILE_MULTIBLOCK_READ_COUNTパラメータは、全表スキャンと高速全索引スキャンの単一I/Oで読込むブロックの個数を指定します。この値を大きくすると、より少ないI/O操作で多くのブロックを読込むことができます。
以下に設定概要を記します。
| DB_FILE_MULTIBLOCK_READ_COUNTパラメータ |
| 概要 |
全表スキャンの単一I/Oで読込むブロック数を指定 |
| 構文 |
DB_FILE_MULTIBLOCK_READ_COUNT = 整数 |
| デフォルト |
8 |
| 変更の可/不可 |
可：ALTER SYSTEM、ALTER SESSION |
■ガイドライン
・OLTP環境では索引スキャンが中心であるため、通常4～16が適正です。
・DSSおよびデータ・ウェアハウス環境では以下の値まで大きくすることを検討します。
DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE =< OSの最大I/Oサイズ
・RAIDのストライプ・サイズに影響を与えることに注意してください。
ストライプ・サイズについては、「第7章ストライプ化とは」をご参照ください。
| NOTE |
： |
DB_FILE_MULTIBLOCK_READ_COUNTの設定値を高くすると、コストベース・オプティマイザは全表スキャンを選択する可能性が高くなります。索引スキャンしていた処理が全表スキャンに変更される場合があるので注意してください。
|
以下にCACHE句をつけて表を作成する例を記します。
例）CACHE句をつけて表を作成する。
SQL> CREATE TABLE test
2 (NO number)
3 CACHE;
表が作成されました。
SQL> SELECT table_name,cache
2 FROM user_tables
3 WHERE table_name IN('TEST','EMP');
| TABLE_NAME |
CACHE |
| ---------------- |
------------ |
| EMP |
N |
| TEST |
Y |
SQL> ALTER TABLE emp CACHE;
表が変更されました。
SQL> SELECT table_name,cache
2 FROM user_tables
3 WHERE table_name IN('TEST','EMP');
| TABLE_NAME |
CACHE |
| ---------------- |
------------ |
| EMP |
Y |
| TEST |
Y |
|
|
なお、エクステント内にDB_FILE_MULTIBLOCK_READ_COUNTの値より少ないブロックが残った場合は、その読込みに1回のI/O操作が必要となりパフォーマンスに影響が出る可能性がありますので、エクステント・サイズをDB_FILE_MULTIBLOCK_READ_COUNTの倍数にする必要があります。
以下に例を記します。
例）エクステントが4つあり、各エクステントのブロック数が10個ある表がある場合
40個全部のブロックをフル・テーブル・スキャンする場合、何回I/O操作されるかを調査します。
DB_FILE_MULTIBLOCK_READ_COUNTの設定値はデフォルトの8です。
 ⇒エクステントが4つの場合8回の読み込み
よって、１つのエクステントを40ブロックで構成すれば、計5回のI/O操作で済むというこになります。
|
以上でデータベース・バッファ・キャッシュのチューニングについての説明は終了です。
共有プールのチューニングと共にご検討いただければと思います。
第16章では、複数バッファ・プールについて説明します。
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
 第16章 複数バッファ・プールとは |
|
|
|
|