Oracle Database の高度な行圧縮機能を覗いてみた / JPOUG Advent Calendar Day 25
この記事は JPOUG Advent Calendar 2021 25日目の記事です。
24日目はみやくる(@miyacle0R)さんの記事「Azure SQL Database Ledger （Ledger/台帳）と Oracle Blockchain Table を比較してみた話」でした。
25日目は Oracle Advanced Compression の機能のひとつである、高度な行圧縮機能について、実際にブロックレベルでどのように圧縮されているかを確認します。理由はやってみたかったからです。
高度な行圧縮とは
Oracle Advanced Compression の機能のひとつで、OracleDatabase 12c から導入されました。
データ・ブロック内に格納された行データの重複値を排除することで圧縮を実現します。
データ・ブロックの先頭にシンボル表というものがあり、そこにはブロック内の行および列の重複値のコピーが格納されます。そして、ブロック内に存在する重複値はシンボル表を参照する短い値に置き換えられます。
また、挿入や更新の際、即時に圧縮が実行されるわけではなく、ブロック内のデータがデータ・ブロック内部で制御された閾値に達したタイミングで圧縮が実施されます。
参考情報
https://www.oracle.com/technetwork/jp/database/performance/acofaqotn-3861046-ja.pdf https://docs.oracle.com/cd/F19136_01/cncpt/tables-and-table-clusters.html#GUID-54EE5325-0894-4869-B3AD-8912D9B4A329 https://docs.oracle.com/cd/F19136_01/cncpt/logical-storage-structures.html#GUID-B248DC2A-3F65-42CF-ACA5-0B096CFCFC46
非圧縮表のデータ・ブロックを確認してみる
検証用に圧縮設定が有効でない表を作成後、テストデータを挿入し
それが含まれるデータ・ブロック を Block Dump として出力します。
SQL> -- テストユーザー作成 create user test identified by oracle default tablespace users;SQL> 2 3 ユーザーが作成されました。 SQL> grant connect, resource to test; 権限付与が成功しました。 SQL> alter user test quota 256m on users; ユーザーが変更されました。 SQL> -- テストテーブル作成(非圧縮) create table test.disable_compress_tbl ( col1 char(4) , col2 varchar2(15) );SQL> 2 3 4 5 表が作成されました。 SQL> -- 圧縮が有効か確認 col owner format a10 col table_name format a20 col compression format a10 col compress_for format a10 select owner, table_name, compression, compress_for from dba_tables where owner = 'TEST' and table_name = 'DISABLE_COMPRESS_TBL'; SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 OWNER TABLE_NAME COMPRESSIO COMPRESS_F ---------- -------------------- ---------- ---------- TEST DISABLE_COMPRESS_TBL DISABLED SQL> -- データ挿入(1000行) begin for i in 1..1000 loop insert into test.disable_compress_tbl(col1, col2) values(i,'MerryChristmas'); end loop; end; / SQL> 2 3 4 5 6 PL/SQLプロシージャが正常に完了しました。 / SQL> -- 統計情報収集 execute DBMS_STATS.GATHER_TABLE_STATS('TEST','DISABLE_COMPRESS_TBL');SQL> PL/SQLプロシージャが正常に完了しました。 SQL> -- テーブルのブロック数を確認 select table_name, avg_space, blocks from dba_tables where owner = 'TEST' and table_name = 'DISABLE_COMPRESS_TBL'; SQL> 2 3 4 5 6 7 TABLE_NAME AVG_SPACE BLOCKS -------------------- ---------- ---------- DISABLE_COMPRESS_TBL 0 5 SQL> -- file_id, block_id を確認 select dbms_rowid.rowid_to_absolute_fno(rowid, 'TEST', 'DISABLE_COMPRESS_TBL') file_id, dbms_rowid.rowid_block_number(rowid) block_id from test.disable_compress_tbl where col1 = 1; SQL> 2 3 4 5 FILE_ID BLOCK_ID ---------- ---------- 4 134 SQL> -- block dump を出力 alter system dump datafile 4 block 134; SQL> システムが変更されました。 [oracle@oracledev trace]$ ls -lrt 合計 11588 : -rw-r-----. 1 oracle oinstall 555487 12月 25 11:06 2021 alert_orcl.log -rw-r-----. 1 oracle oinstall 1073 12月 25 11:07 2021 orcl_ora_3117.trm -rw-r-----. 1 oracle oinstall 83280 12月 25 11:07 2021 orcl_ora_3117.trc <--★Block Dump :
上記で出力した、非圧縮表の Block Dump の内容を確認します。 (1行目～3行目の格納データを抜粋)
▼orcl_ora_3117.trc : block_row_dump: tab 0, row 0, @0x1f81 ★(1) tl: 23 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 4] 31 20 20 20 col 1: [14] 4d 65 72 72 79 43 68 72 69 73 74 6d 61 73 ★(2) tab 0, row 1, @0x1f6a tl: 23 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 4] 32 20 20 20 col 1: [14] 4d 65 72 72 79 43 68 72 69 73 74 6d 61 73 tab 0, row 2, @0x1f53 tl: 23 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 4] 33 20 20 20 col 1: [14] 4d 65 72 72 79 43 68 72 69 73 74 6d 61 73 :
★(1)～★(2)の4行が、ひとつの行の情報です。
それぞれの項目は下記です。(確認できた項目のみ記載しています)
・tab 0 : テーブル番号
・row : 行番号
・tl : メタデータを含む行の長さ
・cc : カラムの数
たとえば★(2)行のデータであれば、
col 1(2番目のカラム)のカラム長は 14 byte で
格納されているデータはASCIIで "4d 65 72 72 79 43 68 72 69 73 74 6d 61 73" です。
変換すると "MerryChristmas" となります。
今回検証で使用するデータ・ブロック内の col 1 のデータはすべて同じのため
★(2)行と同値のデータが2行目、3行目...と続いています。
高度な行圧縮が有効な表のデータ・ブロックを確認してみる
非圧縮表と同様に、検証用の表を作成後 Block Dump を出力し、中身を確認します。
-- テストテーブル作成(高度な行圧縮が有効) SQL> create table test.enable_compress_tbl ( col1 char(4) , col2 varchar2(15) )row store compress advanced; 2 3 4 5 表が作成されました。 SQL> -- 圧縮が有効か確認 col owner format a10 col table_name format a20 col compression format a10 col compress_for format a10 select owner, table_name, compression, compress_for from dba_tables where owner = 'TEST' and table_name = 'ENABLE_COMPRESS_TBL';SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 OWNER TABLE_NAME COMPRESSIO COMPRESS_F ---------- -------------------- ---------- ---------- TEST ENABLE_COMPRESS_TBL ENABLED ADVANCED SQL> -- データ挿入(1000行) begin for i in 1..1000 loop insert into test.enable_compress_tbl(col1, col2) values(i,'MerryChristmas'); end loop; end; /SQL> 2 3 4 5 6 PL/SQLプロシージャが正常に完了しました。 SQL> -- 統計情報収集 execute DBMS_STATS.GATHER_TABLE_STATS('TEST','ENABLE_COMPRESS_TBL');SQL> PL/SQLプロシージャが正常に完了しました。 SQL> -- テーブルのブロック数を確認 select table_name, avg_space, blocks from dba_tables where owner = 'TEST' and table_name = 'ENABLE_COMPRESS_TBL';SQL> 2 3 4 5 6 7 TABLE_NAME AVG_SPACE BLOCKS -------------------- ---------- ---------- ENABLE_COMPRESS_TBL 0 5 SQL> -- file_id, block_id を確認 select dbms_rowid.rowid_to_absolute_fno(rowid, 'TEST', 'ENABLE_COMPRESS_TBL') file_id, dbms_rowid.rowid_block_number(rowid) block_id from test.enable_compress_tbl where col1 = 1; SQL> 2 3 4 5 FILE_ID BLOCK_ID ---------- ---------- 4 142 SQL> -- block dump を出力 alter system dump datafile 4 block 142;SQL> システムが変更されました。 [oracle@oracledev ~]$ ls -lrt /u01/app/oracle/diag/rdbms/orcl/orcl/trace/ : -rw-r-----. 1 oracle oinstall 1051 12月 25 15:47 2021 orcl_ora_3610.trm -rw-r-----. 1 oracle oinstall 163759 12月 25 15:47 2021 orcl_ora_3610.trc ★ -rw-r-----. 1 oracle oinstall 4692 12月 25 15:48 2021 orcl_mmon_3163.trm -rw-r-----. 1 oracle oinstall 26219 12月 25 15:48 2021 orcl_mmon_3163.trc -rw-r-----. 1 oracle oinstall 4668 12月 25 15:48 2021 orcl_gen0_3115.trm -rw-r-----. 1 oracle oinstall 26009 12月 25 15:48 2021 orcl_gen0_3115.trc
上記で出力した Block Dump の内容を確認します。(1行目～3行目の格納データを抜粋)
▼orcl_ora_3610.trc : block_row_dump: tab 0, row 0, @0x1f87 tl: 17 fb: --H-FL-- lb: 0x0 cc: 1 col 0: [14] 4d 65 72 72 79 43 68 72 69 73 74 6d 61 73 ★(3) bindmp: 02 4c d6 4d 65 72 72 79 43 68 72 69 73 74 6d 61 73 tab 1, row 0, @0x1f7e tl: 9 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 4] 31 20 20 20 col 1: [14] 4d 65 72 72 79 43 68 72 69 73 74 6d 61 73 ★(4) bindmp: 2c 01 02 cc 31 20 20 20 00 tab 1, row 1, @0x1f75 tl: 9 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 4] 32 20 20 20 col 1: [14] 4d 65 72 72 79 43 68 72 69 73 74 6d 61 73 bindmp: 2c 01 02 cc 32 20 20 20 00 :
※非圧縮表確認時に説明した項目については割愛します。
まず、★(3)の行で tab 0 のテーブルが、ひとつの列値(col 0)だけデータを持っていることが確認できます。 この tab 0 はシンボル表です。この中に、ブロック内の行および列の重複値のコピーが格納されます。 また、非圧縮表と異なり、bindmp という項目があります。これは圧縮された行データを示します。
なお、tab 1, row 0 以降から、シンボル表ではない、挿入した行データの情報となります。
下記に、今回出力された★(4)の bindmp の値と位置を記載します。
※4～8に格納された値の意味についてはわかりませんでした。
値の位置: 1 2 3 4 5 6 7 8 9 ──────────────────────────────────── bindmp: 2c 01 02 cc 32 20 20 20 00
- 1～3: 行のヘッダー情報
- 4～8: 不明(5番目の値は1ずつ増加しているので行番号？)
- 9 : シンボル表への参照を示します。(00 は tab 0, row 0 を参照している。)
なお、参照先 tab 0, row 0 は "4d 65 72 72 79 43 68 72 69 73 74 6d 61 73" となっており、 "MerryChristmas" が格納されています。
追加で確認
非圧縮表、高度な行圧縮が有効な表にて検証で挿入したデータが少なかったためか
使用ブロック数に差が出ませんでした。
なので、両方に9000行ほど追加して、使用するブロック数に差が出るか確認しました。
SQL> begin for i in 1001..9999 loop insert into test.disable_compress_tbl(col1, col2) values(i,'MerryChristmas'); end loop; end; / 2 3 4 5 6 PL/SQLプロシージャが正常に完了しました。 SQL> begin for i in 1001..9999 loop insert into test.enable_compress_tbl(col1, col2) values(i,'MerryChristmas'); end loop; end; / 2 3 4 5 6 PL/SQLプロシージャが正常に完了しました。 SQL> -- 統計情報収集 execute DBMS_STATS.GATHER_TABLE_STATS('TEST','DISABLE_COMPRESS_TBL');SQL> PL/SQLプロシージャが正常に完了しました。 SQL> -- 統計情報収集 execute DBMS_STATS.GATHER_TABLE_STATS('TEST','ENABLE_COMPRESS_TBL');SQL> PL/SQLプロシージャが正常に完了しました。 SQL> -- テーブルのブロック数を確認 select table_name, avg_space, blocks from dba_tables where owner = 'TEST' and table_name = 'DISABLE_COMPRESS_TBL';SQL> 2 3 4 5 6 7 TABLE_NAME AVG_SPACE BLOCKS ----------------------------------- ---------- ---------- DISABLE_COMPRESS_TBL 0 35 ★非圧縮表 SQL> -- テーブルのブロック数を確認 select table_name, avg_space, blocks from dba_tables where owner = 'TEST' and table_name = 'ENABLE_COMPRESS_TBL';SQL> 2 3 4 5 6 7 TABLE_NAME AVG_SPACE BLOCKS ----------------------------------- ---------- ---------- ENABLE_COMPRESS_TBL 0 20 ★高度な行圧縮が有効な表
高度な行圧縮が有効な表では、使用ブロック数が少ない事を確認できました。
まとめ
高度な行圧縮について、データ・ブロック内で行データの重複値のコピーをシンボル表に格納している事が確認できました。
また、データ・ブロック内に存在する圧縮された行データについて、シンボル表を参照する短い値に置き換わる事を確認できました。
高度な行圧縮を有効にした場合、使用するブロック数が少なく済む事が確認できました。
主な参考情報
Block Dump の出力や内容の確認方法については、下記エントリを参考にしました。
Oracle Database のデータ・ブロックのダンプとその解析 - ぱと隊長日誌
また、シンボル表や bindmp の確認については、下記エントリを参考にしました。
Oracle 11g検証 Advanced Compression その４ - InsightTechnology 旧ブログ
Oracle 11g検証 Advanced Compression その５ - InsightTechnology 旧ブログ
Oracle 11g検証 Advanced Compression その６ - InsightTechnology 旧ブログ
最後に
ノリと勢いで登録してしまった人生で初めての Advent Calendar でしたが
適度なプレッシャーがあり、書いていて楽しかったです。
もうクリスマスは終わりそうですが(現在 12/25 22:44)
みなさま、よいクリスマス＆よいお年を！
(また来年も書きたーい！)
