しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。 “しばちょう”こと柴田長（しばた つかさ）です。
今回もOLTP表圧縮についてご紹介させて頂きます。前々回の第14回の冒頭でもご紹介させて頂きましたが、概要マニュアルの「表の圧縮」の項では次のように紹介されていましたね。「データベースでは、表の圧縮を使用して、表に必要な記憶域を削減します。圧縮によりディスク領域を節約し、データベース・バッファ・キャッシュでのメモリー使用量を減らすことができ、場合によっては問合せ実行時間も短縮できます。」 そして、第14回の記事でも圧縮による性能向上をご説明させて頂きましたが、その演習内容はハード・ディスク・ドライブからの読み込み量が減少することによる効果でしたので、この証明にはなっていませんでしたね。と言う事で、今回は上記の赤字部分に切り込んでいきたいと考えています。
「圧縮により、バッファ・キャッシュでのメモリー使用量を減らす」とは具体的にどういう現象を意味するのでしょうか？バッファ・キャッシュとは、ハード・ディスク・ドライブ上のデータファイルから読み取られたデータ・ブロックのコピーを格納（キャッシュ）するメモリー領域ですね。頻繁にアクセスされるデータ・ブロックをこの領域にキャッシュしておくことで、毎回低速なハード・ディスク・ドライブから読み取るよりも、高速に処理することが可能になるのは理解し易いかと思います。
例えば、バッファ・キャッシュに4GBのメモリーを割り当てたデータベース・インスタンスで、1,000,000（1M）個のデータ・ブロックで構成されている表の全レコードをキャッシュすることは可能でしょうか？一つのデータ・ブロックのサイズを一般的な8KBであるとすると、全レコードで8GB（=1M x 8KB）になってしまいますから、答えは「不可能」となりますね。しかし、この表を圧縮した場合はどうなるのでしょうか？圧縮率が40%であった場合、400,000（400K）個のデータ・ブロックにデータ量を削減することができます。つまり、全レコードで3.2GB（=400K x 8KB）になるので、4GBのバッファ・キャッシュに全てキャッシュすることが可能になりますね。
前置きが長くなりましたが、「圧縮により、バッファ・キャッシュでのメモリー使用量を減らす」とは、キャッシュする必要があるデータ・ブロックのコピーの数が減少し、キャッシュ・ヒット率が向上することを意味しているのです。では、今回は表データを圧縮する「OLTP表圧縮」を使用して、本当にキャッシュ・ヒット率が向上して検索処理が高速化するのだ！という体験をして頂きたいと思います。
以下の演習をこれまでの演習で使用したOracle Database 11g Release 2 Enterprise Editionのデータベースで試してみてください。
【今回ご紹介するネタ一覧（逆引き）】
- 階層問い合わせのLEVEL疑似列を使用した連番レコードの生成方法（演習1）
- PL/SQLのFORループ索引の増分値を1以外に指定する方法（演習2）
- V$MYSTATとPIVOT句を使用したキャッシュ・ヒット率の算出方法（演習3）
- CREATE TABLE AS SELECT文を使用した表の複製（演習4）
- 一つのデータ・ブロックに格納されている平均レコード数の確認方法（演習6）
1. 次のSQLを用いて、256MBの非圧縮表（主キーは第一カラム）を作成してください。
sqlplus TRY/TRY
SQL>
drop table TBL16 purge;
create table TBL16 (COL1 number, COL2 date, COL3 varchar2(80), COL4 varchar2(80), COL5 varchar2(80));
insert /*+APPEND */ into TBL16
select LEVEL,
sysdate+LEVEL/24/60/60,
rpad(to_char(mod(LEVEL, 10)), 80, 'A'),
rpad(to_char(mod(LEVEL, 15)), 80, 'B'),
rpad(to_char(mod(LEVEL, 20)), 80, 'C')
from DUAL connect by LEVEL <= 870000 ;
commit ;
alter table TBL16 add primary key(COL1) ;
select SEGMENT_NAME, BYTES/1024/1024 from USER_SEGMENTS where SEGMENT_NAME = 'TBL16' ;
SEGMENT_NAME BYTES/1024/1024
------------------------ ---------------
TBL16 256
これまでも繰り返しご紹介してきたテクニックを使っています。LEVEL疑似列による連番レコードの生成方法は、小さな検証用の表を作成する際に非常に便利なので覚えておくと一目置かれると思います。データベースの環境にも依存しますが、この方法で大量レコードを生成した場合にメモリー不足によるORAエラーが発生することもありますので、そこはご注意くださいね。
2. 次のPL/SQL内のループ文で表「TBL16」に対する検索処理を繰り返し実行してください。
ただし、PL/SQLを実行する前にはバッファ・キャッシュ上のデータ・ブロックをフラッシュしてください。
sqlplus / as sysdba
SQL> alter system flush buffer_cache ;
connect TRY/TRY
set timing on
DECLARE
step PLS_INTEGER := 30 ;
retn VARCHAR2(80) ;
BEGIN
FOR i IN 1..29000 LOOP
SELECT col5 INTO retn FROM TBL16 where col1 = i*step ;
END LOOP ;
END ;
/
経過: 00:00:06.58
非圧縮表「TBL16」に対して合計29,000回のSELECTを実行するシンプルなPL/SQLとなります。SELECT文はWHERE句で主キーによる完全一致の条件が指定され、具体的にはcol1=30, 60, 90, … , 869970, 870000のレコードが毎実行一つずつヒットする処理です。
ちなみに、Oracle DatabaseのFORループではループを反復する度に「1」ずつ増加する記述方法しか存在しない点を覚えておきましょう。ただし、「1」以外の値で増加させる方法としては、マニュアル「PL/SQL言語リファレンス」の「4 PL/SQLの制御文」にも説明があるように、上記の解答例の「i * step」のようにループ索引を参照する際に増分値（step=30）を乗ずることで代替することが可能です。このような細かな部分まで解説されているとは、Oracle Databaseのマニュアル恐るべしですね。。。
3. 演習2のPL/SQLを実行したセッションにおいて、V$MYSTATを使用してキャッシュ・ヒット率を計算してください。
SQL> -- 演習2を実行直後のTRYユーザーにて
select 100 * (1 - ("'physical reads'" / ("'db block gets'" + "'consistent gets'")))
from (select S.NAME, M.VALUE
from V$MYSTAT M, V$STATNAME S
where M.STATISTIC# = S.STATISTIC#
and S.NAME in ('db block gets', 'consistent gets', 'physical reads')
)
pivot (sum(VALUE) for NAME in ('db block gets', 'consistent gets', 'physical reads')) ;
100*(1-("'PHYSICALREADS'"/("'DBBLOCKGETS'"+"'CONSISTENTGETS'")))
----------------------------------------------------------------
70.4165625
いきなりV$MYSTATやらキャッシュ・ヒット率やらと言われても少し難しかったかもしれません。しかし、これらはDBAにとって基本的な知識ですから、完全に理解し使いこなせるようにしておくべきです。
まず、V$MYSTATについてご紹介させて頂きます。このように知らないビュー名が登場した場合に参照すべきマニュアルは何でしたか？そう、答えは「Oracle Databaseリファレンス」ですね。そして、目次によればV$MYSTATは動的パフォーマンス・ビューに分類されることも簡単に理解することができます。V$MYSTATの説明としては、「カレント・セッションの統計情報を示します」とだけ紹介されており、ちょっと物足りない感じがしないわけではないですが。。。 例えば、sqlplusを使用してデータベース・インスタンスへ接続し、SQL文を実行します。そのセッションを「カレント・セッション」と呼びます。また、Oracle Databaseの内部でどのような処理が実行されたのかを分析し易くするために、各種データベース統計情報（オプティマイザ統計情報とは違います）がカウントアップされる仕組みが実装されています。つまり、V$MYSTATではカレント・セッションで実行したSQLによって、Oracle Databaseの内部でどのような処理が実行されたのかを参照することができるのです。なんだかワクワクしてきませんか？実際にどのようなデータベース統計情報が存在しているのかは、先ほどと同じマニュアル「Oracle Databaseリファレンス」の「E 統計情報の説明」に記述されています。
と言う事で、上記の解答例では、このV$MYSTATを参照して演習2で実行したPL/SQLのキャッシュ・ヒット率を計算しており、約70%と言う結果が出ていますね。つまり、このPL/SQLを実行する際に読み込む必要が出てきたデータ・ブロックの70%はバッファ・キャッシュ上にキャッシュされていたことを意味しています。逆を言えば、30%のデータ・ブロックはバッファ・キャッシュ上にキャッシュされていなかったため、ハード・ディスク・ドライブから読み込んだことになります。
ちなみに、データベース統計情報はV$SYSSTATやV$SESSTAT等でも参照することが可能です。ただし、それぞれの動的パフォーマンス・ビューでは統計情報の見え方（集計範囲等）には違いがあるので、チューニングのシチュエーションによって使い分けることになります。この辺りは、今後の連載でも少しずつご紹介していく予定です。
おっと、一つ説明が漏れておりました。もしも、回答例のキャッシュ・ヒット率の計算SQLを実行した際に、「ORA-00942: 表またはビューが存在しません。」が発生してしまった場合には、カレント・セッションのデータベース・ユーザーに動的パフォーマンス・ビューに対する参照権限が不足していることが推測されます。その際は、次のgrant文で「select any dictionary」権限を付与して上げて下さいね。
sqlplus / as sysdba SQL> grant select any dictionary to TRY ;
4. 非圧縮表「TBL16」を元に、OLTP表圧縮を有効化した表「TBL16_COMP」を作成してください。
sqlplus TRY/TRY SQL> drop table TBL16_COMP purge; create table TBL16_COMP COMPRESS FOR OLTP as select * from TBL16 where 1=2 ; insert /*+APPEND */ into TBL16_COMP select * from TBL16 ; commit ; alter table TBL16_COMP add primary key(COL1) ; select SEGMENT_NAME, BYTES/1024/1024 from USER_SEGMENTS where SEGMENT_NAME like 'TBL16%' ; SEGMENT_NAME BYTES/1024/1024 ------------------------ --------------- TBL16 256 TBL16_COMP 47
この演習は楽勝だと思います。さまざまな実行方法がありますので、さらさらっと実行してしまってください。OLTP表圧縮を有効化した表を作成する際は、「COMPRESS FOR OLTP」句を設定することになります。そして、今回の私の検証環境では、元のサイズの20%以下までデータ量を圧縮することができてしまいました。皆さんの環境でも同じ程度になりましたかね？
ちなみに、この現象を『圧縮されて表のサイズが小さくなった』という表現でも良いのですが、是非次のように表現してもらえると、「あ、この人、データベースに本当に詳しいな」となりますよ。『圧縮により一つのデータ・ブロックに格納されるレコード数が増加したことで合計ブロック数が減少した』って感じが素敵です。
5. 圧縮表TBL16_COMP対して演習2と同じPL/SQLを実行し、キャッシュ・ヒット率を計算してください。
sqlplus / as sysdba
SQL> alter system flush buffer_cache ;
connect TRY/TRY
set timing on
DECLARE
step PLS_INTEGER := 30 ;
retn VARCHAR2(80) ;
BEGIN
FOR i IN 1..29000 LOOP
SELECT col5 INTO retn FROM TBL16_COMP where col1 = i*step ;
END LOOP ;
END ;
/
経過: 00:00:02.17
select 100 * (1 - ("'physical reads'" / ("'db block gets'" + "'consistent gets'")))
from (select S.NAME, M.VALUE
from V$MYSTAT M, V$STATNAME S
where M.STATISTIC# = S.STATISTIC#
and S.NAME in ('db block gets', 'consistent gets', 'physical reads')
)
pivot (sum(VALUE) for NAME in ('db block gets', 'consistent gets', 'physical reads')) ;
100*(1-("'PHYSICALREADS'"/("'DBBLOCKGETS'"+"'CONSISTENTGETS'")))
----------------------------------------------------------------
93.2629982
さて、いかがでしょうか？ PL/SQLによる29,000回のSELECT文の実行時間がOLTP表圧縮を適用することで、三分の一以下（検証環境に依存します）まで高速化していることが確認できましたね。そして、この高速化の理由はキャッシュ・ヒット率にも表れていることが明らかです。非圧縮表では、約70%のキャッシュ・ヒット率でしたが、OLTP表圧縮の表では約93%まで上昇しています。
もう少し踏み込むと、これは『圧縮により一つのデータ・ブロックに格納されるレコード数が増加した』ことによる必然的な効果なのですが、正確に理解してもらうために次の演習6を実行してみてください。
6. 非圧縮と圧縮の各表において、一つのデータ・ブロックに格納されている平均レコード数を確認してください。
sqlplus TRY/TRY
SQL> -- TBL16
select RECORDS "TOTAL_RECORDS",
BLOCKS "TOTAL_BLOCKS" ,
RECORDS/BLOCKS "RECORDS_PER_BLOCK"
from (select count(*) "RECORDS",
count(distinct(DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)||DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))) "BLOCKS"
from TBL16) ;
TOTAL_RECORDS TOTAL_BLOCKS RECORDS_PER_BLOCK
------------- ------------ -----------------
870000 32223 26.9993483
SQL> -- TBL16_COMP
select RECORDS "TOTAL_RECORDS",
BLOCKS "TOTAL_BLOCKS" ,
RECORDS/BLOCKS "RECORDS_PER_BLOCK"
from (select count(*) "RECORDS",
count(distinct(DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)||DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))) "BLOCKS"
from TBL16_COMP) ;
TOTAL_RECORDS TOTAL_BLOCKS RECORDS_PER_BLOCK
------------- ------------ -----------------
870000 5836 149.074709
上記の解答例では、以前にもご紹介させて頂いたDBMS_ROWIDパッケージを活用して合計ブロック数を算出しています。もちろん、これ以外にも方法がありますが、この方法が一番正確なブロック数を求めることが可能ですので是非覚えておいてください。
結果としては、非圧縮時には、一つのデータ・ブロックに約27レコードしか格納されていませんでしたが、OLTP表圧縮を適用することで、約150レコードを格納することが可能となっていますね。つまりは、ある一つのデータ・ブロックをハード・ディスク・ドライブから読み込んでバッファ・キャッシュ上にキャッシュした際に、それぞれ何レコードがバッファ・キャッシュ上にキャッシュされていると考えることができるでしょうか？答えは簡単ですね。非圧縮表では27レコード、圧縮表では150レコードがキャッシュされたことになります。
そして、PL/SQLで連続実行したSELECT文を思い出してください。WHERE句では主キーによる完全一致の条件が指定され、具体的にはcol1=30, 60, 90, … , 869970, 870000のレコードが毎実行一つずつヒットする処理でしたね。非圧縮の場合は一つのデータ・ブロック内に27レコードしか格納されていないので、SELECT実行の度にハード・ディスク・ドライブから次々とデータ・ブロックを読み込む必要があります。一方、圧縮表の場合は一つのデータ・ブロックに150レコードが格納されているので、初回のSELECTの実行でバッファ・キャッシュ上にキャッシュしたブロックを2回目～5回目まで使い回す（キャッシュ・ヒット）ことが可能なのです。つまり、5回に1回しかハード・ディスク・ドライブからブロックを読み込む必要が無いということですね。
ちなみに、5回中4回はキャッシュ・ヒットするから、キャッシュ・ヒット率は80%じゃないか？と思われる方もいらっしゃるかも知れませんが、その考え方は表のキャッシュ・ヒット率を考えた場合には正しいのですが、データベースとしては間違えています。と言うのは、今回のSELECT文では主キーのユニーク索引（Bツリー構造）を使用した検索を行っており、索引もバッファ・キャッシュ上にキャッシュされて繰り返し使用されています。よって、キャッシュ・ヒット率を考える際には、この索引のデータ・ブロック分も含めることを忘れてはいけませんよ。
さて、いかがでしたでしょうか？表データを圧縮する「OLTP表圧縮」を使用して、本当にキャッシュ・ヒット率が向上して検索処理が高速化するという体験をしてもらえたと思います。そして、今回の体験から理解できたこと、それは「Oracle Databaseでは、OLTP表圧縮で圧縮されたデータ・ブロックが圧縮されたまま、バッファ・キャッシュ上にキャッシュされる」と言う事ですね。言い過ぎな感は否めませんが、例えば圧縮率が40%であれば、メモリー容量が実質2,5倍(=1/0.4)まで大きくなったことと同じ効果とも表現しても許してもらえますかね。ということで、今回もありがとうございました。次回も頑張りますので、どうぞよろしくお願いします。