2022.02.10

KEEP BUFFER 活用方案

(株)エクセムコンサルティング本部/DBコンサルティングチームチャン・ジョンミン

概要

Oracleは、ユーザーが要求したタスクをすばやく処理するためにBuffer Cacheと呼ばれるものを使用します。

Buffer CacheはSGAにあり、Oracleインスタンスに接続するすべてのプロセスによって共有されます。

この Buffer Cache は、Oracle I/O 管理の中核となる、頻繁に使用されるデータファイルのブロックをメモリに常駐させることで、物理的な I/O Operation を削減する役割を果たします。 Buffer Cacheを効果的に使用すると、物理I / Oが削減され、I / Oパフォーマンスの問題を自然に解決できます。

Oracleのバージョンが上がるにつれて、Buffer Cacheを処理するアルゴリズムは絶えず改善され、新しい管理方法が提供されてきました。

Oracle 7までの Buffer Cache は 1 つの枠として運営されており、各 Object の特性による差別的な Buffer Cache の利用が難しかったのです。

このような面を解決するためにOracle 8からMultiple buffer poolという機能をサポートすることになり、これにより各Objectの特性やアクセス頻度など差別性を考慮してBuffer Cacheをより細かく管理できるようになりました。

Keep Bufferは、このMultiple Buffer Poolを構成するいくつかの領域の1つです。

KEEP Bufferの使用目的と特性

Keep Buffer の使用目的は、本来の Buffer Cache の目的と同様に、Object をメモリに常駐させることで物理的な I/O を避けることになります。

Keep Buffer PoolとDefault Buffer Poolは、データブロックをキャッシュまたはフラッシュするときに異なるアルゴリズムを使用します。

それにもかかわらず、1つだったBuffer Cache領域をMultiple Buffer Poolに分けることになった理由は、Objectの特性を考慮したBuffer Cache利用のためです。

KEEP Buffer のメモリ空間は Sequential に管理されます。

一度KEEPされたセグメントは、KEEP Bufferのメモリ空間をすべて割り当てるまでメモリに保持され、KEEP Buffer空間がすべて割り当てられると、最も古いブロックからdefault poolに押されます。

そのため、KEEP対象となる3つのセグメントのサイズを正確に計算してKEEP Bufferサイズを適切に割り当てる必要があります。

KEEP Bufferの使用順序

Keep Buffer の使用は次のような手順で進めます。

1. KEEP対象を選ぶ

2.OS Memory & SGAスペースの確認

3. KEEP Bufferの設定

4. テーブル/インデックス属性の変更

5. テーブル/インデックス Keeping

6. KEEP効率チェック

7. KEEP対象選定基準

KEEP対象を選ぶ

KEEP対象選定において明確な基準点はないです。

実際の業務を考慮して各DBの運用環境に合う対象を選定しなければならないです。

KEEPするターゲットが非常に頻繁に使用されるブロックであれば、基本的なDefault Bufferを使用してもCacheになっている可能性が高いのですが、この場合にはKeep Bufferの使用がパフォーマンス上の利点をもたらされます。

逆に、頻繁に使用されていないブロックをKeep Bufferに常駐させると、使用しないメモリがあるため、全体的なパフォーマンスを低下させる要因になることがあります。

そのため、Keep対象を選定するに当たっては、実際の業務の考慮が不可欠です。

例えば、1日1回だけ行われるプログラムなのに、何らかの形で実行時間を短縮させなければならない場合や、多くの業務を処理する時間帯に必ず行われなければならないのに、多くのI/Oのためにボトルネック現象を起こしてシステムに全体的に悪影響を及ぼすプログラムなどがあるかもしれません。

これらのプログラムが使用するオブジェクトはKEEPターゲットになることができます。

ただし、上記のような業務プログラムで使用されるすべてのセグメントをKEEP

Bufferに常駐させることはできません。

したがって、KEEP Bufferに常駐させる対象は、各DB運用環境を考慮して選定しなければならないのです。

他にも、サイズ、DML頻度、データアクセス頻度に応じてKeepするのに適したセグメントが存在します。

選定基準[1]。プログラムの重要度

Keep 対象選定において最も重要な部分がそのセグメントを照会する業務プログラムの重要度です。そのプログラムが重要でない場合は、あえてKeep Bufferを使用する必要はないです。

逆に、そのセグメントを照会するプログラムの重要度が非常に高く、何とか実行時間を短縮する必要がある場合は、プログラムの実行頻度に関係なく、KEEP対象への選定を考慮することができます。

選定基準[2]。セグメントサイズ

セグメントサイズが一定ではなく、大きすぎるセグメントはKeep Bufferの効率を低下させる可能性があります。

Keepされたセグメントは、Keep Bufferの容量が不足すると古いブロックからDefault Bufferに押されてしまいます。

したがって、一定サイズまたは変動量が厳しくなく最大サイズが一定レベル以下の場合のセグメントを選定することが望ましいです。

例えば、「最大サイズが10万ブロック以下のセグメント」などの基準を定めることができます。

選定基準[3]。 Full Table Scan & Index Full Scan & Index Fast Full Scan

KEEPバッファ内のKEEPされたセグメントについて問い合わせる場合、効率を最大化するためにいくらか大量の処理が必要になる場合があります。

Scanの範囲が広い非効率のIndex Scan、Full Table Scan、Index Fast Full Scanで処理されるセグメントが対象となりえます。

KEEPターゲット選択SQLScript

SELECT owner ,

table_name , index_name , partition_name ,

SUM( blocks ) AS t_blocks FROM (

SELECT sg.owner ,

decode( SUBSTR( s.ob_type , 1 , 5 ) , ‘TABLE’ , s.ob_name , ‘INDEX’ , (

SELECT table_name FROM dba_indexes

WHERE index_name = s.ob_name

) ) AS table_name ,

decode( SUBSTR( s.ob_type , 1 , 5 ) , ‘INDEX’ , s.ob_name ) AS index_name , sg.partition_name ,

sg.blocks

FROM (

SELECT DISTINCT object_name AS ob_name , object_type AS ob_type

FROM v$sql_plan

WHERE ( operation = ‘TABLE ACCESS’ AND options = ‘FULL’ )

OR ( operation = ‘INDEX’

AND options = ‘FULL SCAN’ ) OR ( operation = ‘INDEX’

AND options = ‘FAST FULL SCAN’ ) –> 選定基準[3]

) s , dba_segments sg

WHERE s.ob_name = sg.segment_name

)

GROUP BY owner ,

table_name , index_name , partition_name

HAVING SUM( blocks ) > 100000 –> 選定基準[2]SELECT * FROM DUAL

OS Memory & SGAスペースの確認

OS Memory SGAスペースチェックSQLScript

$ cat /proc/meminfo MemTotal: 4055152 kB

MemFree: 1390308 kB

Buffers: 166768 kB

Cached: 2019992 kB

SwapCached: 0 kB

Active: 1118484 kB

Inactive: 1277864 kB

………

SGA空間検証SQLScript

●SGAフルサイズ確認

SELECT name ,

ROUND( bytes/1024/1024 ) “size(MB)” FROM V$SGAINFO;

NAME size(MB)

——————————- ———-

Fixed SGA Size	2
Redo Buffers	5
Buffer Cache Size	48
Shared Pool Size	128
Large Pool Size	0
Java Pool Size	24
Streams Pool Size	0
Shared IO Pool Size	0
Granule Size	4
Maximum SGA Size	207
Startup overhead in Shared Pool	72
Free SGA Memory Available	0

Data Buffer size 確認

SELECT name ,

current_size FROM v$buffer_pool;

NAME CURRENT_SIZE

——————– ———— DEFAULT 48

KEEP BUFFER設定

KEEP Bufferの設定は、KEEP BufferのサイズとSGAの空きスペースに応じて、OnlineジョブまたはOfflineジョブで行います。

この文書のスクリプトは、SGA領域のメモリ管理を手動で行う場合に基づいて作成されました。

KEEP Buffer設定スクリプト

@sga

NAME size(MB)

——————————– ———-

Buffer Cache Size	500
Maximum SGA Size	1019
Free SGA Memory Available	228
@bc

NAME CURRENT_SIZE

——————– ———— DEFAULT 500

●KEEP Buffer のサイズが SGA の Free 空間より小さい場合 Online ( KEEP Buffer 100M )

SQL> alter system set db_keep_cache_size = 100M scope = both;

System altered.

SQL> @sga

NAME size(MB)

——————————– ———-

Buffer Cache Size	600
Maximum SGA Size	1019
Free SGA Memory Available	128

SQL> @bc

NAME CURRENT_SIZE

——————– ———— KEEP 100

DEFAULT 500

●　KEEP BufferのサイズがSGAのフリースペースより大きい場合（KEEP Buffer 300M）

1. SGAのフルサイズを増やした後にKEEP Bufferを割り当てるOfflineタスクが必要

SQL> alter system set sga_max_size = 1100M scope = spfile; System altered.

SQL> alter system set db_keep_cache_size = 300M scope = spfile; System altered.

SQL> shutdown immediate Database closed.

Database dismounted. ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1169227776 bytes Fixed Size 2212696 bytes

Variable Size 301993128 bytes

Database Buffers 855638016 bytes

Redo Buffers 9383936 bytes Database mounted.

Database opened.

SQL> @sga

NAME size(MB)

——————————– ———-

Buffer Cache Size	816
Maximum SGA Size	1115
Free SGA Memory Available	0

SQL> @bc

NAME CURRENT_SIZE

——————– ———— KEEP 304

DEFAULT 512

=> SGA全体のサイズが1Gを超えると、Granuleのサイズが16Mbに増えました。

このとき、指定した値より大きい16の倍数のうち、最も小さいサイズの値が割り当てられます。

2. SGA の他の領域のサイズを縮小した後に KEEP Buffer を割り当てるとオンラインで作業可能

Online 操作可能 Online 状態から変更可能 Parameter 抽出 Script

SELECT name ,

issys_modifiable FROM v$parameter

WHERE name LIKE ‘%size%’

AND issys_modifiable = ‘IMMEDIATE’

NAME ISSYS_MOD

————————————— ———

shared_pool_size IMMEDIATE

large_pool_size IMMEDIATE

java_pool_size IMMEDIATE

streams_pool_size IMMEDIATE

db_cache_size IMMEDIATE

db_2k_cache_size IMMEDIATE

db_4k_cache_size IMMEDIATE

db_8k_cache_size IMMEDIATE

db_16k_cache_size IMMEDIATE

db_32k_cache_size I

MMEDIATE

db_keep_cache_size IMMEDIATE

db_recycle_cache_size IMMEDIATE

_shared_io_pool_size IMMEDIATE

db_flash_cache_size IMMEDIATE

db_recovery_file_dest_size IMMEDIATE

result_cache_max_size IMMEDIATE

workarea_size_policy IMMEDIATE

max_dump_file_size IMMEDIATE

=> 対応するパラメータ値を適切に調整して、Free Memoryを取得した後のKEEP設定

テーブル/インデックス属性の変更

テーブル/インデックスプロパティの変更 Script

ALTER TABLE T1 STORAGE (BUFFER_POOL KEEP); –テーブル属性の変更

ALTER INDEX T1_PK STORAGE (BUFFER_POOL KEEP); –インデックス属性の変更

ALTER TABLE P1 MODIFY PARTITION P1_1 STORAGE (BUFFER_POOL KEEP); –パーティション表属性の変更

ALTER INDEX P1_ID1 MODIFY PARTITION P1_ID1_1 STORAGE (BUFFER_POOL KEEP);– パーティション索引属性の変更

テーブル/インデックス Keeping

Segment の Buffer Pool が KEEP に設定されたテーブルとインデックスは Query 時 KEEP Buffer に

そのセグメントのブロックをロードします。したがって、最初のセグメントをロードするときにDisk I / Oが発生します。最初の実行時を含むすべてのアプリケーションの照会からDisk I / Oを削除したい場合は、作業が進む前にそのセグメントをFull Table ScanまたはIndex Fast Full ScanでKEEP Bufferにロードするだけです。

KEEP Buffer効率判断

KEEP Bufferの使用に明確な基準が定められているのではなく、運用環境によって違いが存在します。そのため、すべての運用環境で同じ方法で効率を判断するには無理があるのです。しかし、次のような資料がKEEP Bufferの効率を判断するための根拠になる可能性があります。

KEEP Buffer Size & Hit Ratio SQLScript

SELECT current_size keep_size , seg_size ,

ROUND( seg_size/current_size*100 , 1 ) “Ratio(%)” FROM v$buffer_pool ,

(

SELECT SUM( bytes ) /1024 /1024 seg_size FROM dba_segments

WHERE buffer_pool = ‘KEEP’

)

WHERE name = ‘KEEP’

KEEP_SIZE SEG_SIZE Ratio(%)

———- ———- ———- 304 118 38.8

SELECT db_block_gets ,

consistent_gets , physical_reads , CASE

WHEN db_block_gets+consistent_gets <> 0

THEN ROUND(( 1-( physical_reads/( db_block_gets+consistent_gets ) ) ) *100 , 2 ) END “Keep_Hit(%)”

FROM v$buffer_pool_statistics WHERE name = ‘KEEP’

DB_BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS Keep_Hit(%)

————- ————— ————– ———– 0 44474 4435 90.03

KEEP Buffer を使用する Segment サイズの合計が KEEP Buffer サイズより小さい場合、該当 Segment のサイズがこれ以上大きくならない場合、一度 KEEP 領域に上がった Segment の Cache Hit Ratio が 100% に近づくはずです。

KEEP Bufferのサイズがその領域を使用するSegmentのサイズよりも小さい場合、KEEP Buffer領域で競合が発生し、それによってPhysical I / Oが発生し、Cache Hit Ratioが発生する可能性があります。

このとき、KEEP Bufferのサイズを増やしたり、重要度が落ちるSegmentのKEEP Bufferの使用を防ぐ方法を考えてみることができます。

逆にKEEP BufferのサイズがSegmentの

サイズよりも大きい場合、使用しないメモリ空間を占めているものなので KEEP Buffer の

サイズを減らすことを検討してください。

したがって、システムパフォーマンスとSegmentの重要性に応じて、効率的なKEEP Bufferのサイジングが必要です。

次のスクリプトでdba_hist_seg_statビューを照会して、Segment照会時に発生するI/O発生量に関するAWR情報を確認し、Segment別効率を判断することができます。

Segment I/O SQLScript

accept i_begin_time prompt ‘Enter begin time[YYYYMMDDHH24]: ‘ accept i_end_time prompt ‘Enter end time[YYYYMMDDHH24]: ‘

variable v_begin_time char(10) variable v_end_time char(10)

exec :v_begin_time:=&i_begin_time exec :v_end_time :=&i_end_time

SELECT /*+ leading(k) */ s.dbid ,

decode( SUBSTR( o.object_type , 1 , 5 ) , ‘TABLE’ , o.object_name , ‘INDEX’ , (

SELECT table_name FROM dba_indexes

WHERE index_name = o.object_name AND owner = k.owner

) ) AS table_name ,

decode( SUBSTR( o.object_type , 1 , 5 ) , ‘INDEX’ , o.object_name ) AS index_name , s.snap_id ,

TO_CHAR( w.begin_interval_time , ‘yyyymmdd.hh24’ ) AS begin_time , s.physical_reads_delta ,

s.physical_reads_direct_delta ,

s.physical_reads_delta + s.physical_reads_direct_delta AS total_diskio FROM sys.wrm$_snapshot w ,

dba_hist_seg_stat s , dba_objects o ,

(

SELECT owner ,

segment_name FROM dba_segments

WHERE buffer_pool = ‘KEEP’

) k

WHERE w.begin_interval_time >= to_timestamp( ‘2013062510’ , ‘yyyymmddhh24’ ) AND w.end_interval_time <= to_timestamp( ‘2013062518’ , ‘yyyymmddhh24’ ) AND w.snap_id = s.snap_id

AND w.dbid = s.dbid

AND w.instance_number = s.instance_number AND s.obj# = o.object_id

AND k.segment_name = o.object_name AND k.owner = o.owner

ORDER BY 2 , 3 , 5

結論

KEEP Bufferを使用する上で最も重要なのは業務内容の反映です。

よく使用するオブジェクトだけに常駐する Buffer Cache 1つだけを使用することがシステム全体の観点から見ると効率的かもしれないのです。

しかし、業務の重要性や特性を考慮すると、他の結果が出てくることがあります。少ない実行でも重要度の高い業務があり、実行時間の短縮が非常に重要な業務がある場合があります。

このような業務に対する特性を反映した運用計画を立てる上で、KEEP Buffer を効率的に使用できれば、システム性能向上に大いに役立ちます。

Back to index

KEEP BUFFER 活用方案