津島博士のパフォーマンス講座 
第28回「表圧縮とLOBデータ型について」

■津島博士による解説が動画でも! セミナー動画公開中です。
津島博士のパフォーマンス講座「パフォーマンス問題はなぜ起きるのか」  【WMV】 【MP4】 【PDF

皆さんこんにちは、今年は雨が少ない変な梅雨ですね。気温の変化も大きいので体調は大丈夫でしょうか。私は、軽い風邪気味になってしまいましたので、皆さんも気を付けてください。
今回は、第13回で説明した表圧縮の続きとLOB(Large OBject)データ型について説明しようと思います。これまでいろいろと圧縮機能について説明してきましたが、非常に便利な機能ですが万能ではないことを認識して欲しいと思い、少しまとめてみましたので、参考にしてください。

1. 表圧縮について
まずは、表圧縮についてもう少し説明しましょう。
Oracleデータベースには、表圧縮機能といってもいくつか存在しますので、少し整理してみました。最初に、Oracle9iR2からダイレクト・ロード操作だけで圧縮することができるデータ・セグメント圧縮が提供されました。次に、Oracle Database 11g(Oracle11g)ですべてのSQLで圧縮することが可能なOLTP表圧縮が提供されました(このときデータ・セグメント圧縮から基本表圧縮に変更になっています)。同時に、LOBデータ型に対して圧縮することが可能なSecureFiles機能も提供されています。これをまとめると以下のようになります。

  • 基本表圧縮(旧データ・セグメント圧縮)
  • OLTP表圧縮
  • LOB圧縮(SecureFiles)
  • ウェアハウス圧縮
  • アーカイブ圧縮

OLTP表圧縮とLOB圧縮は、Advanced Compressionオプションが必要です。ウェアハウス圧縮とアーカイブ圧縮は、EHCC(Exadata Hybrid Columnar Compression)機能ですので、Exadataだけで使用することが可能ですが、表圧縮機能の比較として載せています。

(1)基本表圧縮とOLTP表圧縮の違い
基本表圧縮は、ダイレクト・インサート操作(第15回で説明したダイレクト・パス・インサート、テーブル/パーティションのMOVE操作など)だけで圧縮することができる機能です。それに対してOLTP表圧縮は、ダイレクト・インサート操作以外のSQLでも圧縮するようになっています(ただし、列数の最大が255個までの制限があります)。OLTP表圧縮は、通常のSQLでも圧縮を行いますが、SQL文ごとにデータ圧縮が動作するのではなく、Oracleブロックの領域使用率が閾値(PCTFREEを基に計算された閾値)を超えたとき、Oracleブロック内のデータを圧縮します。指定はOracle11gR2から以下のように変更されています(デフォルトは基本表圧縮”BASIC”です)。

SQL> CREATE TABLE tab01 ( … ) COMPRESS [BASIC | FOR OLTP] ;

ILMなどで必要なときに圧縮する場合は、基本表圧縮(MOVE操作など)で問題ありません。また、初期ロード(ダイレクト・ロードなど)で圧縮する場合も基本表圧縮で問題ありません。ただし、通常のINSERT文で大量に挿入するような場合は、OLTP圧縮を使用する必要があります。ETLツールなどでは、ダイレクト操作を行わない(PL/SQLなどで行う)ものがありますので、その場合もOLTP圧縮が必要になりますので注意してください。

(2)ウェアハウス圧縮とアーカイブ圧縮
Exadataだけで使用できるHybrid Columnar Compression(HCC)機能で、基本表圧縮より圧縮効率の高い圧縮機能になります(Oracleブロック内に異なる方法で格納します)。これもダイレクト・インサート操作だけで圧縮されますが、これを使用できるのもExadataを使用するメリットになります。ウェアハウス圧縮は、検索性能を重視した圧縮タイプで、DWHアプリケーション向けです。アーカイブ圧縮は、記憶域の削減を重視した圧縮タイプで、履歴データなどの変更されないデータ向けです。どちらのタイプも”HIGH”の方が圧縮率が高いですが、圧縮および検索時のCPUオーバーヘッドも高くなります。検索するようなデータでは、基本は”QUERY HIGH”で問題ないと思います。UPDATE文などの非ダイレクト操作を行うと、対象のOracleブロックがOLTP表圧縮になります(このとき多少オーバーヘッドが発生します)。指定するには以下のように行います。

SQL> CREATE tab01 ( … ) COMPRESS FOR QUERY [LOW | HIGH] ;  -- ウェアハウス圧縮
SQL> CREATE tab01 ( … ) COMPRESS FOR ARCHIVE [LOW | HIGH] ;  -- アーカイブ圧縮

(3)LOB圧縮(SecureFiles)
Oracle11gからのパフォーマンス向上(圧縮など)のために、再設計された新しいLOBデータ型です。詳細は「2.LOBデータ型」を参照してください。

(4)圧縮アドバイザ
Oracle 11g R2より自動セグメント・アドバイザ機能の一部として、圧縮アドバイザ機能が追加され、OLTP圧縮を使用した場合に、節約できる領域のサイズをアドバイスします(サイズが 10Mバイト以上で、索引が3つ以上存在するテーブルが評価対象となります)。以下のようにdbms_space.asa_recommendations(またはOracle Enterprise Manager)を使用して行います。これにより、どのテーブルに効果があるかを確認することが可能です。

SQL> SELECT tablespace_name, segment_name, segment_type, recommendations, c1
  2    FROM table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE')) ;
TABLESPACE_NAME                SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------------------ --------------------
RECOMMENDATIONS
----------------------------------------------------------------------------------
C1
----------------------------------------------------------------------------------
USERS                          TAB01                          TABLE
オブジェクトTEST.TAB01を圧縮してください。11534336バイトの節約が予測されるためです。
alter table "TEST"."TAB01"  compress for oltp

(5)表圧縮の問題点
Oracleブロックは、UPDATEによって既存データのサイズが拡張されると、PCTFREE領域(将来のUPDATEのために用意する領域の割合)の空きを使用しても足りない場合に、第8回で説明した行移行が発生します。これは、圧縮されたデータでも同じようになります(圧縮の方がよりサイズが拡張し易くなります)。
UPDATEが多いテーブルでは、PCTFREEパラメータを増やして行移行が発生しないように調整する必要があります。ただし、PCTFREE領域を大きくすることは、Oracleブロック数が増加してしまうので、Oracleブロックの利用効率や圧縮するメリットが薄れてしまいます。また、PCTFREEパラメータを調整するとしても、すべてのOracleブロックで同じようにUPDATEされる訳ではないため、PCTFREEパラメータの調整は簡単ではありません。そのため、殆どがデフォルト(10)で使用しているのではないでしょうか。これは、ASSM(自動セグメント領域管理)を使用しても自動的に調整はしません。
行移行が発生したテーブルに対してアクセス効率を向上させるには、テーブル(またはパーティション)の再構築が必要になります。そのため、UPDATEが多いテーブル(またはパーティション)は圧縮しないようにすることが最適になります。ただし、そうすると圧縮できるテーブルが少なくなってしまいますので、ILMなどのテーブル内でのアクセス頻度の違いを利用して、パーティション化することを検討してください。パーティション化することで、UPDATEが多いテーブルのOracleブロック利用効率を向上することが可能です(圧縮を使用しない場合でも効果があります)。これは、パーティション単位にPCTFREEを指定することにより、効果的に使用できるからです(以下が指定例です)。

SQL> CREATE TABLE tab01 (c1 DATE, ・・・ )
  2   PARTITION BY RANGE (c1)
  3   (PARTITION p01 VALUES LESS THAN (TO_DATE('2012/02/01','YYYY/MM/DD') PCTFREE 10,
  4    PARTITION p02 VALUES LESS THAN (TO_DATE('2012/03/01','YYYY/MM/DD') PCTFREE 0 COMPRESS BASIC,
  5    ・・・ ) ;

(6)ILM(Information Lifecycle Management)について
ここで、何度か出てきているILMについて少し説明します。
性能ダウンしないようにするには、リソース不足などで性能ダウンしてからチューニングするのではなく、効果的にリソースを使用するようにすることと、そのときの性能ダウンを最小限にすることが重要です。その一つのソリューションが、ILMという考え方です。
ILMという機能がOracleデータベースにある訳ではありません。これは、情報のライフサイクル(生成されてから必要なくなるまでの流れを表すもの)に対する一般的なソリューションです。すべてのデータのアクセス頻度が同じではないことを利用して、リソースや機能を効果的に管理しましょうというものです。特に大容量データを効果的に管理するには、ディスク容量や性能については大きな問題になります。このILMソリューションをOracleデータベースで実現すると、以下のように時系列のパーティションや表圧縮機能などを使用して実現することが一般的です(パーティションを使用するのは、1つのテーブル内でも管理する必要があるからです)。




このようにパーティション化して、UPDATEしなくなったら(またはUPDATEが少なくなったら)圧縮するためにMOVEパーティションを行います(このときに、性能の異なるディスクに移動なども行う)。現在はこれを自動で行うことはできませんので、運用が複雑になるからと嫌う方もいると思いますが、ディスク容量や性能が不足してくると、ディスク追加などを行っているのと同じことです(圧縮することによって、追加するディスクを削減することも可能です)。

2. LOBデータ型
ここからは、これまで説明していなかったLOBデータ型の内部LOB(CLOB, BLOB, NCLOB)について説明しましょう(外部LOBのBFILEは、トランザクション管理外のためUNDO情報などは生成されませんので省略します)。
最近は、非構造化データを使用するようなシステムが増えてきましたので、そのようなデータを扱うLOBデータ型(LOB)についても重要なってきています(LOBを使用することで、構造化データと同じように扱うことができます)。LOBは、第25回で説明したSQLオブジェクト型やXMLDBなどでも内部的に使用しています。LOBは、通常のセグメントとは異なる管理を行いますので、同じように使用しても性能が出ませんので注意が必要です。データサイズが4000(正確には3964、後から説明するSECUREFILEは3968)バイトを超えると(または”disable storage in row”を指定すると)、以下のように行外データとして別セグメント(LOBセグメント)に格納します(デフォルトは表セグメントと同じ表領域に格納します)。ただし、行内データは表セグメントのOracleブロック数を増加させますので、LOBデータ以外へのアクセス性能が低下します。つまり、LOBデータに頻繁にアクセスしないシステムでは、効率良くないので注意してください。




LOBセグメントを使用する場合は、UNDOセグメントを使用しなくなりますので、ロールバックするためのBeforeイメージ(UNDOデータ)をLOBセグメントに格納します。このとき、NOCACHEモードの場合、INSERT/UPDATE文はダイレクト操作になります(NOLOGGINGモードの場合はREDOログも生成されません)。そのときのUNDOデータの上書きの制御を以下の二つの方法(領域サイズベース、保持期間ベース)で行います。

  • PCTVERSION(Oracle 10gまでのデフォルト)
    旧バージョンのLOBデータ(UNDOデータ)の保持に使用できる領域の割合(%)を指定します(デフォルト値は10%です)。その割合を超えると古いLOBデータから上書きされます。
  • RETENTION(Oracle 10g R2からのデフォルト)
    自動UNDO管理モードの場合だけ指定可能です。オプション指定なしの場合は、旧バージョンのLOBデータを保持する期間を、初期化パラメータUNDO_RETENTIONの期間で行います(ただし、UNDO_RETENTIONの自動チューニングは行いません。自動チューニングはオプションを”AUTO”にすると行われます)。それ以外のオプションには、”MAX”(LOBセグメントの最大サイズまで上書きしない)、”MIN”(保証したい最小の時間)、”NONE”(UNDOデータを保存しない)があります。Oracle 10g R2からは自動UNDO管理モードと同様に、こちらを使用することをお薦めします(自動UNDO管理モードについては第19回の「UNDOデータについて」を参照してください)。

Oracle 11gからは、BASICFILE LOB(BASICFILE)とSECUREFILE LOB(SECUREFILE)があります(BASICFILEは以前からのLOBです)。SECUREFILEの方がより多くの機能がありますので(性能も改善されていますので)、できるだけSECEREFILEを使用してください。どちらを使用するかは、以下のようにLOB STORAGE句で指定します(初期化パラメータDB_SERCUREFILEで、何も指定しないときのデフォルト値を指定できます。LOB STORAGE句に何も指定しない場合のデフォルトはBASICFILEです)。



LOBは、データサイズが大きくなるため、通常のデータと比べると性能は遅くなります。性能は遅いことを前提に設計していれば良いのですが、そうでないと、LOBの性能に全体の性能が影響されてしまいますので、注意が必要です(例えば、トランザクションに含めるとトランザクション性能が低下します)。LOBも通常のデータ型と同じように、パーティション化することが可能ですので、効果的に使用してください(以下のようにLOB列以外でパーティション化できます)。

SQL> CREATE TABLE tab01 (c1 NUMBER, c2 NUMBER, c3 CLOB)
  2   PARTITION BY RANGE(c1)
  3   (PARTITION p01 VALUES LESS THAN (100) TABLESPACE users1 LOB (c3) STORE AS SECUREFILE lobseg1,
  4    PARTITION p02 VALUES LESS THAN (200) TABLESPACE users2 LOB (c3) STORE AS SECUREFILE lobseg2,
  5    ・・・ ) ;

(1)BASICFILE LOB
まずは、BASICFILEについて説明します。
BASICFILEのLOBデータは、以下のように固定長のチャンク単位にLOBセグメントに格納して、LOB索引で管理されます(Beforeイメージも同じように管理されます)。このLOB索引にLOBセグメントへのポインタが格納されていますので、LOBデータの検索時にLOB索引を使用して行います。




これに、LOBデータの挿入を行うと以下のように未使用領域の検索を行います(上記図のように行います)。
① LOBセグメントの管理領域から未使用領域を検索します。
② LOB索引を使用してBeforeイメージの検索を行います(上書き可能なUNDOデータを探すための処理です)。
③ LOBセグメントを拡張します。

このLOB索引は、チャンク・サイズが適切でないと増加してしまうため、未使用領域の検索時にアクセス数も増えてしまいます(チャンク・サイズのデフォルトは、Oracleブロックの1ブロック・サイズです)。そのため、LOBデータのサイズとチャンク・サイズを同じにするように指定します(Oracleブロック・サイズの整数倍で指定する必要があります)。ただし、LOBデータのサイズが常に同じとは限りませんので、設定するのが簡単ではありません。LOB索引名やチャンク・サイズなどは以下のようにして確認できます。

SQL> SELECT column_name,segment_name,index_name,chunk FROM user_lobs where table_name = 'TAB01';

COLUMN_NAME  SEGMENT_NAME  INDEX_NAME                          CHUNK
------------ ------------- ------------------------------ ----------
C3           LOBSEG1       SYS_IL0000089047C00003$$             8192

(2)SECUREFILE LOB
SECUREFILEは、Oracle 11gからパフォーマンスの向上や管理(またはアプリケーション開発)の容易性のために、再設計された新しいLOBデータ型です。機能拡張として圧縮、暗号化、重複除外などの機能を提供します。ただし、自動セグメント領域管理(ASSM)の表領域を使用する必要があり、作成可能なのは内部LOBだけです。
SECUREFILEでは、BASICFILEから以下のように変更されています。また、UNDOデータはすべてRETENTIONになります。つまり、自動UNDO管理モードでないと使用できません。

  • チャンク・サイズが可変長になります。チャンク・サイズは、LOBデータ・サイズと領域の使用状況から自動チューニングされますので、CHUNKパラメータは無視されます(サイズが異なるLOBデータでも効果的に管理することが可能です)。
  • 基本はLOB索引を使用しません(行データに格納されているLOBセグメントへのポインタを使用します)。未使用領域の検索は、LOBセグメント内の管理情報(領域の使用状況)を共有メモリ上の”in-memory dispenser”にキャッシュされますので、それを使用して行われます(つまり、メモリ上のアクセスのため高速です)。”in-memory dispenser”に存在しない場合には、管理情報を参照して”in-memory dispenser”の更新を行います(このときRETENTION期間を過ぎているチャンクも更新されます)。
  • LOGGING句にFILESYSTEM_LIKE_LOGGING(メタデータ変更だけをREDOログに生成する)を指定することで、障害の平均リカバリ時間を削減できます。



圧縮機能と重複除外機能は、以下のようになります(ただし、Advanced Compressionオプションが必要です)。暗号化については別の機会に説明しようと思います。

  • 圧縮機能
    圧縮機能は、以下のようにLOB STORAGE句にCOMPRESSを指定することで可能です。圧縮率は、”HIGH”、”MEDIUM”、”LOW”の3種類が提供されていて、”HIGH”が最も圧縮率が高くなりますが圧縮処理時間も長くなります(”LOW”はOracle11gR2からの高パフォーマンスに最適化された圧縮です)。指定しない場合のデフォルトは”MEDIUM”、何も指定しない場合のデフォルトは圧縮なし”NOCOMPRESS”です。圧縮のメリットが小さい場合(圧縮率が約20%以下の場合、行外データで挿入するLOBデータがOracleブロック・サイズより小さい場合)には圧縮されませんので、使いやすくなっています。
SQL> CREATE TABLE tab01 (c1 CLOB) LOB (c1) STORE AS SECUREFILE (COMPRESS [HIGH | MEDIUM | LOW]) ;
  • 重複除外機能
    重複除外機能は、以下のようにLOB STORAGE句に”DEDUPULICATE”を指定することで、内容が既存のデータと同一かの判断を行う動作になります。同一と判断された場合には、既存のLOBデータ・ブロックへのポインタ情報だけを格納しますので、重複LOBデータが多い場合には効果的です(デフォルトは重複除外機能なし”KEEP_DUPLICATES”です)。このときINSERT/UPDATE文が行われると、同一LOBデータを検索するためにLOB索引を使用します(SECUREFILEは、このときだけLOB索引を使用します)。
SQL> CREATE TABLE tab01 (c1 CLOB) LOB (c1) STORE AS SECUREFILE [(DEDUPLICATE | KEEP_DUPLICATES)] ;

3. おわりに
今回は表圧縮とLOBデータ型について説明しました。また機会があれば他のことについても説明しようと思います。これからも頑張りますのでよろしくお願いします。質問をお待ちしています。 それでは、次回まで、ごきげんよう。