SAP R/3 および SAP BW システムに対応する索引圧縮 Jan Klokkers SAP Development Server Technologies
以下の事項は 弊社の一般的な製品の方向性に関する概要を説明するものです また 情報提供を唯一の目的とするものであり いかなる契約にも組み込むことはできません 以下の事項は マテリアルやコード 機能を提供することをコミットメント ( 確約 ) するものではないため 購買決定を行う際の判断材料になさらないで下さい オラクル製品に関して記載されている機能の開発 リリースおよび時期については 弊社の裁量により決定されます Oracle PeopleSoft JD Edwards 及び Siebel は 米国オラクル コーポレーション及びその子会社 関連会社の登録商標です その他の名称はそれぞれの会社の商標の可能性があります
内容 Oracle のストレージ領域の削減技術の概要 データ型の優位性 ビットマップ索引ップ索引 Unicode SAP に対応する Oracle 10g 索引圧縮 次バージョン展望 SAP に対応する Oracle 11g Advanced Compression 高度なサポートの提供 まとめ
Oracle のストレージ領域の削減技術 < ここに画像を挿入 >
内部データの表現例 CREATE TABLE XYZ ( COL1 NUMBER ( 10), COL2 NUMBER ( 10), COL3 NUMBER ( 10), COL4 VARCHAR2(100) ) <storage_desc> ; データ表現方式 : 固定長 可変長 Oracle: NUMBER: 可変長 VARCHAR: 可変長 DB2 SQL*Server: NUMBER: 固定長 VARCHAR: 可変長 ( 非効率的 )
内部データの表現数値 INSERT INTO XYZ (COL1, COL2, COL3) VALUES (12, 1234, 123456); 固定長の表現 ( 合計 15 バイト ): V 1 V 2 V 2 V 3 V 3 V 3 可変長の表現 ( 合計 9 バイト ): L 1 V 1 L 2 V 2 V 2 L 3 V 3 V 3 V 3
内部データの表現文字値 INSERT INTO XYZ (COL4) VALUES ('ABC'); DB2 の表現 : L 1 L 2 L 3 L 4 A B C Oracle の表現 : L 1 A B C
ビットマップ索引 < ここに画像を挿入 >
ビットマップ索引 各値にビットマップ ( ビット ベクトル ) を使用 高圧縮データ 標準のB*Tree BTree 索引より少なくとも 25% 小さい Oracleは ディスクにビットマップ索引を格納する唯一のデータベース SAP BW は ビットマップ索引を広範囲に活用 BW システムのディスク領域を大幅に節約 スター クエリーを超高速で実行
永続ビットマップ索引 表 ビットマップ索引 キービットマップ < 青 : 10001001 0001 0010 100> < 緑 : 0100 0000 1100 0001 001> < 赤 : 0001 0100 0010 0100 000> < 黄 : 0010 0010 0000 1000 010>
Unicode データ < ここに画像を挿入 >
Unicode データの表現 データベースで異なるエンコード スキーマ UTF-8 可変長 ASCII 文字は1バイトのみ きわめて省スペース 大多数のデータがASCII 形式であれば ディスク領域を追加する必要はほとんどない UTF-16 固定 2 バイト表現 Oracle は SAP データベースに UTF-8 を使用 SAP の Unicode 移行後は 標準の Oracle データベースが 20% 小さくなる
ストレージ領域の削減技術 まとめ Oracle は 圧縮技術を使用せずに どのデータベースよりも効率的にデータを格納 SAP BW システムの例 : 同じ量のデータの場合 Oracle が使用するディスク領域は 圧縮なしの状態で DB2 より 50% 少ない ビットマップ索引の使用 数値の可変長ストレージ BWデータの80% は数値で格納
SAP に対応する Oracle 10g 索引圧縮 < ここに画像を挿入 >
索引圧縮検討する動機 SAP システムの大量のディスク領域を索引が使用 SAP ERP(R/3): データベースのディスク領域の30~50% さらに多いケースも存在 SAP BI(BW): データベースのディスク領域のスク領域の 25~40% ビットマップ索引を多用すると 比率が低下 SAP 索引は 索引圧縮にとって理想的索引圧縮にとって理想的 通常は 索引ごとに平均 6 列からなる複数列索引 索引の先頭列が最も選択的でないデータ
索引キーの圧縮技術 B*Tree BTree のリーフ ブロック内のデータ圧縮 各リーフ ブロックは 個別に圧縮 ( グローバルなシンボル表はない ) 索引キーは 各ブロックに 2 個 1 組で格納 接頭辞部 接尾辞部 同一の接頭辞は 1 回のみ格納 SAP アプリケーションに対して完全に透過的 大幅なデータ変更により 圧縮効果に影響がない SAP BR*SPACE による完全なサポート Index Reorgs は圧縮属性を維持
例 6 列の索引 1 A X 1 A 1 ROWID1 1 A X 1 B 2 ROWID2 1 A X 2 A 3 ROWID3 1 A Y 1 B 4 ROWID4 1 A Y 3 C 5 ROWID5 1 A Y 3 C 6 ROWID6 1 A Y 3 D 7 ROWID7 1 B X 1 A 1 ROWID8 1 B X 1 A 2 ROWID9 1 B X 1 C 3 ROWID10 1 B X 3 A 4 ROWID11 1 B X 3 C 5 ROWID12 1 B X 3 C 6 ROWID13
例 接頭辞 1 12 少ない値 1 A X 1 A 1 ROWID1 A X 1 B 2 ROWID2 A X 2 A 3 ROWID3 A Y 1 B 4 ROWID4 A Y 3 C 5 ROWID5 A Y 3 C 6 ROWID6 A Y 3 D 7 ROWID7 B X 1 A 1 ROWID8 B X 1 A 2 ROWID9 B X 1 C 3 ROWID10 B X 3 A 4 ROWID11 B X 3 C 5 ROWID12 B X 3 C 6 ROWID13
例 接頭辞 2 22 少ない値 1 A X 1 A 1 ROWID1 X 1 B 2 ROWID2 X 2 A 3 ROWID3 Y 1 B 4 ROWID4 Y 3 C 5 ROWID5 Y 3 C 6 ROWID6 Y 3 D 7 ROWID7 1 B X 1 A 1 ROWID8 X 1 A 2 ROWID9 X 1 C 3 ROWID10 X 3 A 4 ROWID11 X 3 C 5 ROWID12 X 3 C 6 ROWID13
例 接頭辞 3 30 少ない値 1 A X 1 A 1 ROWID1 1 B 2 ROWID2 2 A 3 ROWID3 1 A Y 1 B 4 ROWID4 3 C 5 ROWID5 3 C 6 ROWID6 3 D 7 ROWID7 1 B X 1 A 1 ROWID8 1 A 2 ROWID9 1 C 3 ROWID10 3 A 4 ROWID11 3 C 5 ROWID12 3 C 6 ROWID13
例 接頭辞 4 28 少ない値 1 A X 1 A 1 ROWID1 B 2 ROWID2 1 A X 2 A 3 ROWID3 1 A Y 1 B 4 ROWID4 1 A Y 3 C 5 ROWID5 C 6 ROWID6 D 7 ROWID7 1 B X 1 A 1 ROWID8 A 2 ROWID9 C 3 ROWID10 1 B X 3 A 4 ROWID11 C 5 ROWID12 C 6 ROWID13
例 接頭辞 5 15 少ない値 1 A X 1 A 1 ROWID1 1 A X 1 B 2 ROWID2 1 A X 2 A 3 ROWID3 1 A Y 1 B 4 ROWID4 1 A Y 3 C 5 ROWID5 6 ROWID6 1 A Y 3 D 7 ROWID7 1 B X 1 A 1 ROWID8 2 ROWID9 1 B X 1 C 3 ROWID10 1 B X 3 A 4 ROWID11 1 B X 3 C 5 ROWID12 6 ROWID13
例 接頭辞 6 節約しない 1 A X 1 A 1 ROWID1 1 A X 1 B 2 ROWID2 1 A X 2 A 3 ROWID3 1 A Y 1 B 4 ROWID4 1 A Y 3 C 5 ROWID5 1 A Y 3 C 6 ROWID6 1 A Y 3 D 7 ROWID7 1 B X 1 A 1 ROWID8 1 B X 1 A 2 ROWID9 1 B X 1 C 3 ROWID10 1 B X 3 A 4 ROWID11 1 B X 3 C 5 ROWID12 1 B X 3 C 6 ROWID13
例 Total Number of Leaf Blocks blocks 14000 12000 10000 8000 6000 4000 2000 0 0 1 2 3 4 5 6 7 8 9 10 prefix length
構文 CREATE INDEX <xxxx> ON <TABLE> (<COLUMNLIST>) COMPRESS <n> <n> = 接頭辞の長さ 一意の索引の接頭辞の最大長 : N-1 他の接頭辞の最大長 :N
最適な接頭辞の長さの計算 (1) 非効率的な方法 : ANALYZE INDEX <xxx> VALIDATE STRUCTURE SELECT opt_cmpr_count, opt_cmpr_pctsave FROM index_ stats オンライン操作なし 索引をロック きわめて低速 並行スキャンなし 一度に1つの索引のみ分析 DBAが索引の作成 / 再構築の文を記述することが必要 時間がかかる 誤りが発生しやすい
最適な接頭辞の長さの計算 (2) 最適な方法 : Package IND_COMP from SAP Note 1109743 exec ind_comp.get_column(... ) 完全なオンライン操作 きわめて高速 並行スキャン 既存の統計を使用 SAPの索引設計と索引使用方法の知識 きわめて簡潔 最大 N 個の索引を分析可能 別個のSQLファイルで 索引の作成 / 再構築の完全な構文を生成 領域節約統計を提供
Parameters of ind_comp Ind_comp ( table spec, logging, opmode) パラメータ : 表固有の値 : 表名 たとえばRFBLG 表の数 たとえば100 ロギングの値 : True 領域統計問合せの生成 False デフォルト Opmode 値 : オンライン デフォルト : オンラインで再構築するための文を生成し パーティション化された索引を無視する オフライン パーティション化されていない索引にはオンラインで再構築された文を生成し パーティション化された索引には削除 / 作成の文を生成する パーティション化された索引を圧縮する必要がある場合は BWシステムに使用することが必要
ind_comp のインストール方法 手順 1: SAP Note 1109743を参照 手順 2: SAP Note 1109743から添付文書をダウンロード 手順 3: ファイルの名前を変更する Ind_comp.txt から ind_comp.sql Ind_comp_part.txt t t から ind_comp_part.sql 手順 4: データベース アカウント <sapsid> を準備 sqlplus connect / as sysdba <sapsid> に任意のディレクトリを作成する権限を付与する <sapsid> に任意のディレクトリを削除する権限を付与する 手順 5: PL/SQLパッケージIND_COMPをデータベースにロード sqlplus connect <sapsid>/<password> start ind_comp
ind_comp の使用方法 (1) 単一の特定表のすべての索引を分析 sqlplus set serveroutput on size 1000000 connect <sapsid>/<passwd> exec ind_comp.get_column ( GLPCA ); exec ind_comp.get_column ( /BIC/ECUSTOMER001 ); すべての索引で N 個の最大数の表を分析 sqlplus set serveroutput t on size 1000000 connect <sapsid>/<passwd> exec ind_ comp.get pg _ column (100);
ind_comp の使用方法 (2) すべての索引で 追加の領域統計を持つ N 個の最大数の表を分析 sqlplus set serveroutput on size 1000000 connect <sapsid>/<passwd> pass d exec ind_comp.get_column (50, true);
ind_comp の使用方法 (3) すべての索引で 追加の領域統計とオンライン モードを持つN 個の最大数の表を分析 ( パーティション化された索引は無視 ) sqlplus set serveroutput e on size 1000000 connect <sapsid>/<passwd> exec ind_comp.get_column (50, true, online );
ind_comp の使用方法 (4) すべての索引で 追加の領域統計を持つ N 個の最大数の表を分析し パーティション化された索引を含める sqlplus set serveroutput on size 1000000 connect <sapsid>/<passwd> pass d exec ind_comp.get_column (50, true, offline );
Ind_comp 出力 /tmpまたはc: temp ディレクトリに.sqlファイルを生成 各表に 1 つのファイル 例 : GLPCA_IX_COMP.sql すべてのSQL 文が含まれる1 個のサマリー ファイル IX_COMP.sql
Ind_comp の出力例とロギング IX_COMP.sql spool IX_COMP.log col index_name format a30 col partition_name format a30 select segment_name index_name, partition_name, round (bytes/1024/1024) MB from user_segments where segment_name name ='GLPCA~7' ; alter index "SAPR3"."GLPCA~7" rebuild online compress 5 parallel 4 pctfree 1; alter index "SAPR3"."GLPCA~7" noparallel; select segment_name index_name, partition_name, round (bytes/1024/1024) MB from user_segments where segment_name name = 'GLPCA~7' ; spool off
索引の再構築 表固有の SQLファイルの実行 sqlplus connect <sapsid>/<passwd> @glpca_ix_comp サマリー ファイル IX_COMP.sql の実行 sqlplus connect <sapsid>/<passwd> @start ix_comp
最適化 システム コピーで ind_comp を実行 最適な圧縮係数を計算するために 本番システムに負荷を追加する必要はない 索引表領域の領域パラメータをチェック 大きい値は領域の節約効果を低下させる DMTS: 初期エクステント サイズまたはネクスト エクステント サイズ pctincrease 値 LMTS: Uniform 設定 UNIFORM のかわりに AUTOALLOCATE を使用 大きい索引には UNIFORM サイズに 10MB を選択
注意および警告 分析フェーズはオンライン操作 本番システムのCPUおよびIOの負荷が10~20% 増加する SAPシステムのピーク時間には実行しないこと オンライン モードの実行フェーズは ダウンタイムが不要であるため SAP システムのアップタイム中に実行可能 REBUILD ONLINE 機能を使用する SAPシステムのピーク時間には実行しないこと 必要なCPU メモリーおよびIOリソースが増加する オフライン モードの実行フェーズは SAPシステムのダウンタイムが必要 パーティション化された索引の圧縮は 索引の削除 / 作成を実行することによってのみ有効化できる
結果 領域 索引 GLPCA~1 のサイズが 18GB から45GB 4.5GB に縮小される 領域が 75% 節約 全般 : R/3における最小ケース : 索引が完全に再編成された後で 索引圧縮が有効になり データベース全体で15~20% の領域が節約 BWにおける最小ケース : 索引が完全に再編成された後で 索引圧縮が有効になり データ索引圧縮が有効になりベース全体で10~15% の領域が節約 R/3およびBWの標準的なケース : データベース全体で 20~30% の領域が節約
結果 パフォーマンス 圧縮された索引を使用する場合 追加のCPUサイクルが必要 索引のフル スキャンおよび範囲スキャンの場合スキャンおよび範囲スキャンの場合 物理的および論理的 I/Oは減少 顧客の経験による情報 索引圧縮の導入はスループットに悪影響を及ぼさないか または SAP システムのスループットがわずかに上昇
索引圧縮のその他の利点 追加ライセンスの費用が不用 Oracle サーバーの標準機能 索引圧縮は Oracle8i から導入されているため 完成された機能 すぐにディスク領域を節約 Oracle11g Advanced Compressionオプションを待つ必要がない Oracle11gでは 索引圧縮機能への機能拡張はない 索引圧縮は Advanced Compression オプションの一部ではない
< ここに画像を挿入 > 次バージョン展望 : Oracle Database 11g Advanced Compression オプション
Advanced Compressionオプション 機能 OLTP 表の圧縮 従来の DML( 挿入 アップデアップデート ) をサポート <= 修正 INSERTおよびUPDATEは 再圧縮を行わない ブロック レベルの圧縮 ( グローバルなシンボル表はない ) SecureFilesの非構造化データ圧縮 (LOBの代替) 表内のSecurefiles 列は 表とは別に圧縮することが必要 Data Pump 圧縮 RMANバックアップ圧縮 Data Guardネットワーク転送圧縮