しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。 “しばちょう”こと柴田長（しばた つかさ）です。
今回は前回の続きとして、圧縮機能についてご紹介をしていきます。前回は「基本圧縮（基本表圧縮）」を扱いましたが、今回は同じように表データを圧縮する「OLTP表圧縮」を使用して、まずは非圧縮表から圧縮表への変換方法を通して圧縮の特徴を理解して頂き、その後、圧縮レコードを操作する上での注意点と回避策についても体験して頂きたいと思います。
OLTP表圧縮とは、Oracle Database 11g Release 1から登場したOracle Advanced Compression Optionに含まれる表データを圧縮する機能であり、サポートされるオペレーションが基本表圧縮よりも大幅に拡張されております。ただし、基本的な圧縮の仕組みは列データの重複排除であるため、圧縮率はほぼ同等と考えて良いと思います。大きく異なる箇所としては「データ挿入の仕方」であり、基本表圧縮がダイレクト・パス・ロードでのみ挿入されたデータのみ圧縮するという制限があるのに対し、OLTP表圧縮はこの制限が取り払われています。よって、前者はSELECTがメインであるData Warehouse系のデータベースに限定して活用されてきましたが、後者の登場によってOLTP系のデータベースでも表データの圧縮機能が使用できるように拡張されています。
ちなみに、OLTP表圧縮ではブロック内にある閾値が設定されていて、その閾値までレコードが格納された際にそのブロック内で圧縮を行うという動作となります。よって、その閾値に達する直前までのINSERT処理に要するコスト（CPU使用量等）は、非圧縮表へのINSERT処理に要するコストと同等であり、閾値に達するINSERT処理の際にのみ圧縮による追加コストが発生します。
この辺りの動作については、概要マニュアルの「データ・ブロック内の領域管理」の項の「データ・ブロック内の空き領域の最適化」において、次のように表現されています。「OLTP圧縮を使用する表に対するINSERT文 – 挿入によってブロックがデータでいっぱいになると、データベースによってブロック圧縮が開始され、ブロックにより多くの空き領域が生じる可能性があります。」また、Oracle Advanced Compression OptionのWhite Paperには図も描かれているので参考になるかと思います。
以下の演習をこれまでの演習で使用したOracle Database 11g Release 2 Enterprise Editionのデータベースで試してみてください。
【今回ご紹介するネタ一覧（逆引き）】
- OLTP表圧縮を使用可能にする設定（演習2）
- 既存レコードを圧縮する方法（演習4）
- 圧縮レコードの大量更新時の注意点（演習5-6）
- UPDATE文をCTAS（Create Table As SELECT）文への変換（演習7）
■1. 次のSQLを用いて、256MBの非圧縮表を作成してください。
sqlplus TRY/TRY
SQL>
create sequence SEQ15 start with 1 increment by 1 cache 10000;
create table TBL15 (COL1 number, COL2 date, COL3 varchar2(80), COL4 varchar2(80), COL5 varchar2(80));
insert /*+APPEND */ into TBL15
select SEQ15.nextval,
sysdate+SEQ15.currval/24,
rpad(to_char(mod(SEQ15.currval, 10)), 80, 'A'),
rpad(to_char(mod(SEQ15.currval, 20)), 80, 'B'),
rpad(to_char(mod(SEQ15.currval, 30)), 80, 'C')
from (select 0 from all_catalog where rownum <= 87),
(select 0 from all_catalog where rownum <= 100),
(select 0 from all_catalog where rownum <= 100);
commit;
select SEGMENT_NAME, BYTES/1024/1024 from USER_SEGMENTS where SEGMENT_NAME='TBL15' ;
SEGMENT_NAME BYTES/1024/1024
---------------- ---------------
TBL15 256
はい、非圧縮表「TBL15」に870,000件のレコードを格納し、セグメントサイズが256MBであることが確認できたかと思います。ご利用されている表領域のエクステントサイズの設定によっては256MBにならないケースもありますのでご注意ください。
ほとんど前回の演習と同じSQL文ですので、詳細な説明は前回の記事の演習1の解説をご参照くださいね。
■2. 表「TBL15」に対して、OLTP表圧縮を使用可能に変更してセグメントサイズを確認してください。
sqlplus TRY/TRY SQL> alter table TBL15 compress for oltp ; select TABLE_NAME, COMPRESSION, COMPRESS_FOR, PCT_FREE from USER_TABLES ; TABLE_NAME COMPRESS COMPRESS_FOR PCT_FREE ------------------------------ -------- ------------ ---------- TBL15 ENABLED OLTP 10 select SEGMENT_NAME, BYTES/1024/1024 from USER_SEGMENTS where SEGMENT_NAME='TBL15' ; SEGMENT_NAME BYTES/1024/1024 ---------------- --------------- TBL15 256
表に対するオペレーションですから、「ALTER TABLE」で始まるDDL文であることが推測できますよね。よって、マニュアル
「SQL言語リファレンス」の「ALTER TABLE」セクション部分を集中的に確認してみましょう。また、初めにポイントさせて頂いた概要マニュアルにも例としてSQL文が記載されています。
はい、非常に簡単なSQL文で一瞬にしてOLTP表圧縮を使用可能にすることができましたね。本当に圧縮属性が設定されたのか否かはUSER_TABLESディクショナリ・ビューのCOMPESS列やCOMPRESS_FOR列を参照してみましょう。
この演習で確認しておいて頂くポイントは、OLTP表圧縮を使用可能に変更した前後のセグメントサイズになります。いかがですか？セグメントサイズの値が変化していないことが確認できたと思います。「あれ？」と思って頂けた方、ありがとうございます。そうなのです。この結果は正しいです。と言うのは、この「alter table compress for oltp」文は表の圧縮属性を変更するだけであり、既に格納済みのレコード（演習1で挿入したレコード）は非圧縮のまま格納され続けることになります。「意味があるのか？」とさらに思って頂けた方、またまたありがとうございます。もちろん意味はあって、今後、このTBL15表へ挿入されるレコードは圧縮されて格納されていくことになるのです。ということで、次の演習で確認してみましょう。
■3. 表「TBL15」に対して、演習1で挿入した870,000レコードを（ダイレクト・パス・ロードではない）通常INSERTで追加挿入し、セグメントサイズを確認してください。
sqlplus TRY/TRY
SQL>
insert into TBL15
select SEQ15.nextval,
sysdate+SEQ15.currval/24,
rpad(to_char(mod(SEQ15.currval, 10)), 80, 'A'),
rpad(to_char(mod(SEQ15.currval, 20)), 80, 'B'),
rpad(to_char(mod(SEQ15.currval, 30)), 80, 'C')
from (select 0 from all_catalog where rownum <= 87),
(select 0 from all_catalog where rownum <= 100),
(select 0 from all_catalog where rownum <= 100);
commit;
select SEGMENT_NAME, BYTES/1024/1024 from USER_SEGMENTS where SEGMENT_NAME='TBL15' ;
SEGMENT_NAME BYTES/1024/1024
---------------- ---------------
TBL15 392
いかがでしょうか。期待通りに新規にINSERTしたレコードは圧縮されていますよね？
元々TBL15表のセグメントサイズは256MBであり、この演習では全く同じレコード数を追加挿入しています。と言う事は、非圧縮状態であれば、256MB + 256MB=512MBまでセグメントサイズが大きくなると推測されますが、今回は演習2でOLTP表圧縮を使用可能な状態に変更しているので、136MBの拡張（=392MB – 256MB）で済んでいます。よって、演習2の解説部分で説明させて頂いた通り、「alter table compress for oltp」文は表の圧縮属性を変更するだけであり、既に格納済みのレコードは圧縮されず、その後、挿入されるレコードが圧縮されて格納されていくことが確認できたかと思います。
では、既に格納済みのレコードも圧縮したい場合にはどうすれば良いのでしょうか。
■4. alter table文を使用して、表「TBL15」の既存レコードを圧縮してください。
sqlplus TRY/TRY SQL> alter table TBL15 move compress for oltp ; select TABLE_NAME, COMPRESSION, COMPRESS_FOR, PCT_FREE from USER_TABLES ; TABLE_NAME COMPRESS COMPRESS_FOR PCT_FREE ------------------------------ -------- ------------ ---------- TBL15 ENABLED OLTP 10 select SEGMENT_NAME, BYTES/1024/1024 from USER_SEGMENTS where SEGMENT_NAME='TBL15' ; SEGMENT_NAME BYTES/1024/1024 ---------------- --------------- TBL15 160
「alter table <表名> compress for oltp;」で既存レコードが圧縮されないという演習の後なので、少し意地悪な演習問題で申し訳なかったですが、表の圧縮属性の変更 + 既存レコードも圧縮する場合にも「alter table」文を使用するのです。とは言え、じっくりとSQL文を見比べて頂くと解りましたかね？「MOVE」オプションが追記されていますね。そうです、それが答えです。
「MOVE」は一般的に対象表が格納されている表領域を別の表領域上へ移動する際に使用されるケースが多く、そういった目的で使用されたことがある方はいらっしゃるのではないでしょうか。その場合をイメージして頂くと理解し易いのですが、表領域間を移動するということは内部的には別のセグメントへレコードを移し替えている（INSERT + SELECT）ことになりますよね。よって、「alter table <表名> MOVE compress for oltp;」では、既存レコードを圧縮表へINSERT + SELECTし直すような動作となるため、既存レコードも圧縮されて格納されることになります。
注意すべき点としては、索引のRebuildが必要となります。「レコードを入れ直す」=「各レコードのROWIDが変更される」のでROWIDを保持しているB*Tree索引が無効化する為です。また、「alter table <表名> MOVE compress for oltp;」の実行には表の排他ロックを取得する必要があるため、この操作が完了するまでDML文の実行は待機させられます。よって、基本的にはオンライン処理が流れていないメンテナンス時間帯での実行が望ましいですね。もし、どうしてもメンテナンス時間が確保できない場合は表のオンライン再定義を活用することで、DML文へ影響なく既存レコードを圧縮することが可能です。
表のオンライン再定義については別の機会にご紹介するとして、今回は圧縮表における注意点を見ていきましょう。
■5. 表「TBL15」の列「COL3」が「rpad(‘0’, 80, ‘A’)」であるレコードの列「COL3」を「rpad(’00’, 80, ‘A’)」へ更新してください。
sqlplus TRY/TRY
SQL> set timing on
update TBL15 set COL3=rpad('1', 80, 'A') where COL3=rpad('0', 80, 'A') ;
commit;
はい、基本中の基本なので特に問題無く実行できたかと思います。でも、いざ久しぶりに実行しようとしたらSQL構文を忘れてしまっている事ってありますよね。私はそんなことが結構ある人間で非常に悲しくなります。。。なので、なるべくSQL文をコピペではなくタイピングして直打ちすることで指に覚えさせるようにしていたりします。
はい、すいません。話が脱線してしまいましたが、ここでなぜUPDATEを実行したのかの答えは次の演習で明らかになります。
■6. 表「TBL15」のセグメントサイズ、行移行/行連鎖しているレコード数を確認してください。
sqlplus TRY/TRY SQL> select SEGMENT_NAME, BYTES/1024/1024 from USER_SEGMENTS where SEGMENT_NAME='TBL15' ; SEGMENT_NAME BYTES/1024/1024 ---------------- --------------- TBL15 160 drop table CHAINED_ROWS purge ; @?/rdbms/admin/utlchain.sql analyze table TBL15 list chained rows ; select TABLE_NAME, count(HEAD_ROWID) from CHAINED_ROWS where TABLE_NAME='TBL15' group by TABLE_NAME; TABLE_NAME COUNT(HEAD_ROWID) ------------------------------ ----------------- TBL15 95764
少し驚きの結果がでましたかね？
まずは、セグメントサイズの確認ですが160MBであり、UPDATE前後で変化していないことが確認できます。この結果から圧縮レコードを更新しても、きちんと圧縮された状態で格納されているはずと推測できます。しかしながら、もう一つの行移行/行連鎖しているレコード数には注意しなければならない臭いがしますね。
まずおさらいなのですが、通常の表（非圧縮の表）では、元々格納されているレコード長よりも大幅に長いレコードにUPDATEした場合、そのブロック内のPCTFREEの領域を使用して何とか格納しようと頑張りますよね。しかし、その領域が不足してしまえば、別のブロックへレコードを移行する必要が出てきます。この現象を行移行と呼びましたね。実は、圧縮レコードをUPDATEした場合にはこれと同じ現象が発生する可能性があります。という話です。圧縮レコードをUPDATEすると展開されて格納されるため、前述の例と同じようにレコード長が大幅に長くなるようなUPDATEをした場合と類似する現象が発生します。結果的に大量のレコードをUPDATEすればPCTFREE領域が不足して行移行が発生する確率が高くなるということになります。
しかしながら、セグメントサイズは大きく成長していなかった点は優秀じゃないですかね？この理由は行移行先のブロックで正常に圧縮が行われたことを意味しています。大量レコードのUPDATEにより多くのレコードが行移行した場合、その行移行したレコードが連続的に移行先ブロックに格納され続けることで移行先ブロックの圧縮の閾値に達してブロック圧縮が実行されるからです。
では、最後にUPDATE処理をCREATE TABLE AS SELECT CASE文に書き換えることで、行移行を発生させずにレコードを更新する方法をご紹介したいと思います。
7. 表「TBL15」の列「COL3」が「rpad(‘1’, 80, ‘A’)」であるレコードの列「COL3」を「rpad(’11’, 80, ‘A’)」へ更新してください。 ただし、UPDATE文ではなく、CREATE TABLE AS SELECT CASE文を使用してください。
sqlplus TRY/TRY
SQL> set timing on
create table TBL15_NEW compress for oltp
as select COL1, COL2,
case COL3 when rpad('1', 80, 'A') then rpad('11', 80, 'A') else COL3 end COL3,
COL4, COL5
from TBL15 ;
-- ここで必要であればTBL15_NEW表に対して、索引や制約の作成（DBMS_META_DATA.GET_DDL等を活用）
alter table TBL15 rename to TBL15_OLD ;
alter table TBL15_NEW rename to TBL15 ;
select SEGMENT_NAME, BYTES/1024/1024 from USER_SEGMENTS where SEGMENT_NAME='TBL15' ;
SEGMENT_NAME BYTES/1024/1024
---------------- ---------------
TBL15 144
drop table CHAINED_ROWS ;
@?/rdbms/admin/utlchain.sql
analyze table TBL15 list chained rows ;
select TABLE_NAME, count(HEAD_ROWID)
from CHAINED_ROWS where TABLE_NAME='TBL15' group by TABLE_NAME;
レコードが選択されませんでした。
ちょっと興味深いテクニックですよね？初めに仮表TBL15_NEWを作成し、元表の全レコードをSELECTしつつCASE文によってUPDATEのWHERE句の条件にヒットするレコードのみを更新したデータをコピーしています。次にセグメントの名前を置き換えれば完了です。意外とシンプルではないですかね？処理時間に関しては解答例には含めていませんが、皆さんの環境で、演習5のUPDATE文との性能差については確認してみてください。その差は環境依存な部分がありますが、高い確率で演習7のCTAS文の方が高速で完了していると思います。
そして、一番の目的であった行移行が発生していないことも確認できましたね？と言う事で、”大量の圧縮レコードを更新”するような場合には、このようなCTAS文を使用する方法をお勧めしています。CTAS文で実行できない場合には、通常のUPDATE後に「ALTER TABLE <表名> MOVE COMPRESS FOR OLTP;」コマンドで再圧縮を実行することで行移行/行連鎖を解消することが可能です。
さて、いかがでしたでしょうか？いざ、非圧縮表から圧縮表への変更する場面になった場合に、自信を持ってオペレーションできるようになって頂けましたでしょうか。既存レコードを圧縮の対象とするのか否か、圧縮の対象とした場合の注意点、この辺りの知識を持ち合わせておくことでトラブルを未然に防ぐことが可能になってくると思います。また、”大量の圧縮レコードを更新”する場合の注意すべき点も盲点だったと思います。少量レコードであれば全く問題は有りませんが、この「大量」や「少量」という言葉の定義は非常に難しい部分です。表の構成や格納レコードの特徴に依存する部分ですので、影響の有無については是非データベース毎でテストして頂くことをお勧めします。これらのポイントはマニュアルを読んだだけでは想像/推測しかできない部分であり、今回のように実機によって試すことで明確になってくることを実感して頂けたかと思っています。
是非とも継続的に手を動かし続けていきたいものですね。今回もありがとうございました。次回も頑張りますので、どうぞよろしくお願いします。