KEEPプールの設定方法についてちょっと学んだのでメモ。
私の環境はCentOS7上に構築したOracle19c。
全般的な基本知識については以下を参照。
https://blogs.oracle.com/otnjp/post/tsushima-hakushi-13
データベース・バッファ・キャッシュのチューニング
KEEPプールについて
主に私の理解を書きます。違うところもあるかもしれませんあてにし過ぎないでください。
Oracleのバッファキャッシュの一種。ネットで検索するとKEEPプールだったり、KEEPバッファとかKEEPキャッシュとか表記ゆれが見られるような気がしますが、ここではKEEPプールと呼びます。
バッファキャッシュにはDEFAULTの領域が存在するが、全テーブルで共通で使用されているので追い出しが起きる。
使用頻度の高いテーブルなどを安定してキャッシュ配置するために、テーブル単位でKEEPプールに指定する。
なので、主に性能改善などのために使用を検討されることが多いと思われる。
一般的には使用頻度の高い小さめのテーブルを配置することが推奨されているようだ。
大きいテーブルを配置することも可能だが、メモリ上に配置するという性質上、それなりの設計とメモリ容量の確保が必要になってくる。
KEEPプールの設定
本来であれば、KEEPプールに乗せるテーブルを選定してサイズを見積る必要がありますがここではその工程は省きます。
見積り方法などは公式ドキュメントなどを参照。
初期化パラメータの設定
KEEPプールを設定するためには、KEEPプールのためにメモリ容量を指定する必要があります。
DBサーバのメモリ＞MEMORY_TARGET＞DB_KEEP_CACHE_SIZE＞対象オブジェクトのサイズ の関係になるように設定する。
なお正確なことは分かりませんが、DB_KEEP_CACHE_SIZEよりも対象オブジェクトのサイズが大きくなったときに性能が劣化することがありました。
KEEPプール対象のオブジェクトにアクセスした際にKEEPプールに載っていないとそのたびにのっけようとするっぽいのでKEEPプールからの追い出し乗せ替えが多発していた可能性があります。ご参考までに。
今回の私のテスト環境ではDBサーバのメモリ=4G、MEMORY_TARGET=1Gなので、
DB_KEEP_CACHE_SIZE=500Mとして設定します。
・初期化パラメータの設定例（抜粋）
/u01/app/oracle/product/19.0.3/dbhome_1/dbs/inittest_db.ora
memory_target=1G db_keep_cache_size=500M
初期パラメータを修正したらSPファイルを作成し直す。
[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on 木 10月 7 06:18:07 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. アイドル・インスタンスに接続しました。 SQL> CREATE SPFILE FROM PFILE; File created. SQL> startup ORACLE instance started. Total System Global Area 1073737800 bytes Fixed Size 8904776 bytes Variable Size 482344960 bytes Database Buffers 578813952 bytes Redo Buffers 3674112 bytes データベースがマウントされました。 データベースがオープンされました。 SQL>
パラメータ表示して設定どおりになっていればOK。
SQL> show parameter db_keep_cache_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_keep_cache_size big integer 500M SQL>
対象オブジェクトの設定
KEEPプールに乗せるオブジェクトは一般的にテーブルやインデックスになる。
設定はスキーマごとに設定するものなので、複数スキーマを運用している場合には少し気を付けた方がいいかもしれない。
設定自体は以下のSQLを実行することで設定できる。
ALTER TABLE EMP STORAGE(BUFFER_POOL KEEP); ALTER INDEX PK1 STORAGE(BUFFER_POOL KEEP);
SQL> ALTER TABLE EMP STORAGE(BUFFER_POOL KEEP); 表が変更されました。 SQL> ALTER INDEX PK1 STORAGE(BUFFER_POOL KEEP); 索引が変更されました。 SQL>
設定を外す場合にはデフォルトに戻せばよい。
ALTER TABLE EMP STORAGE(BUFFER_POOL DEFAULT);
設定はされているかどうかはuser_tablesテーブルのbuffer_poolカラムを見ればいいので例えば以下のようなSQLで確認できる。
SELECT table_name,buffer_pool FROM user_tables WHERE buffer_pool = 'KEEP';
KEEPプールに乗せる
上記ではあくまで設定しただけなので、対象オブジェクトのKEEPプールに実際のデータは基本的にはまだ載っていない状態。
設定は行っているので、データアクセスが発生すれば自然と載っていくと思うが性能改善などを目的にしている場合には初回アクセスで期待した性能がでないなどの事態が考えられるので予め乗せておく方が無難だと思われる。
以下のようなSQLを実行する。
SQL> SELECT /*+ FULL(EMP) */ COUNT(*) FROM EMP; COUNT(*) ---------- 1001 SQL> SELECT /*+ NOPARALLEL_INDEX(T PK1) INDEX_FFS(T PK1) */ COUNT(*) FROM EMP T; COUNT(*) ---------- 1001 SQL>
実際に乗っているかどうかは以下のようなSQLで確認することができる。
select o.owner, o.object_name, o.object_type, o.status, s.buffer_pool, count(*) as blocks from dba_objects o, dba_segments s where o.owner = 'WANTAN' and s.owner = 'WANTAN' and o.object_name=s.segment_name and o.object_type=s.segment_type and s.buffer_pool != 'DEFAULT' group by o.owner,o.object_name,o.object_type,o.status,s.buffer_pool order by blocks;
OWNER OBJECT_NAME OBJECT_TYPE STATUS BUFFER_POOL BLOCKS WANTAN PK1 INDEX VALID KEEP 1 WANTAN EMP TABLE VALID KEEP 1
キャッシュなのでORACLEの停止、再起動などで全ておろされる。
設定はもちろん消えませんが、必要に応じて起動、再起動時に乗せ直す必要がある。
久しぶりなのでこんなところで
