しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。 “しばちょう”こと柴田長（しばた つかさ）です。
Oracle Databaseで管理するデータの最小単位は？という問いかけがあった場合、皆さんは何と答えるでしょうかね。「データ・ブロック」と回答される方が多いと思いますが、データ・ブロックはオペレーティング・システムが読み取り書き込みできるデータの最小単位（これは私の大好きな概要マニュアルにもきちんと記述されています）であり、データ管理の最小単位ではないのです。確実にデータ・ブロックよりも小さな管理単位、それが「行断片」です。
今回から2回の連載に渡り、基本中の基本である行移行と行連鎖の原因のおさらいと、この「行断片」の動きを知らないが為に陥りがちなケースを体験して頂きたいと思います。そのケースは私が若かりし頃に経験した性能トラブルなのですが、Oracle Databaseをもっと勉強したいと思うキッカケとなった事象でもあります。もしかしたら、そのようにデータベースに興味を持って頂けるかもしれないということで記事化させて頂きました。
一般的な行連鎖と行移行は1つのレコードが1ブロックではなく、複数のブロックにまたがって格納されることを言います。通常、対象の1ブロックを読み込めばレコードを認識できますが、行連鎖や行移行が発生している場合は複数ブロックを読み込む必要が出てくるのはイメージし易いと思います。つまり、単純に言い換えれば、同じ処理であっても行連鎖、行移行が発生している場合はI/O要求の回数が増加することを意味しています。これは非常に性能へのインパクトが大きいはずなのです。
しかし、近年、行連鎖や行移行があまり取り上げられなくなってきている傾向に感じています。これにはいくつかの理由があると思いますが、ここでは2つの理由を挙げてみます。一つはSSDの登場を代表とする記憶デバイスの性能の進化。もう一つは、CPUのマルチコア化に伴うサーバーの処理能力の向上があげられると思います。前者はデバイスのI/O性能（スループット、IOPS）が向上したので、複数ブロックを読み込むことになってもその影響が小さくなったのかもしれませんね。後者はいかがでしょうか。読み込むブロック数が増加することと、CPU消費量の関係は難しいと感じがしますが、ブロックをデバイスから読み込んだ後、つまりバッファ・キャッシュ上にキャッシュした後は、結局サーバープロセスがCPUを使用して処理を行いますよね。この処理対象のブロック数が増えれば、CPU消費量は増加するでしょう。そして、近年のCPU性能の向上が、この影響を小さくしているのかもしれません。
前置きが長くなりましたが、行連鎖と行移行はI/OリソースだけではなくCPUリソースの消費量を増加させています。多くのデータベースにおいて性能への影響は顕在化していないかもしれませんが、潜在的な課題と言えると思っています。その行連鎖と行移行について次のポイントに注目しつつ、これまでの演習で使用したOracle Database 11g Release 2 Enterprise Editionのデータベースで試してみてください。
- 行移行とブロック外行連鎖の違い
- 行連鎖にはブロック内行連鎖も存在する
- 各行連鎖時のCPU消費量
- 表作成DDL文で、アプリケーションから良く使用する列（カラム）を先頭へ寄せるメリットとデメリット
■0. 次のSQLで今回の演習で使用するスキーマを作成してください。
sqlplus / as sysdba
SQL>
create bigfile tablespace TBS12 datafile '' size 1g ;
create user TRY identified by TRY default tablespace TBS12 ;
grant CONNECT, RESOURCE, DBA to TRY ;
connect TRY/TRY
create table TAB12_1 (COL1 number NOT NULL, COL2 VARCHAR2(4000)) ;
alter table TAB12_1 add constraint PK_TBL12_1 primary key(COL1) ;
insert into TAB12_1 select LEVEL, 'initial Record' from DUAL connect by LEVEL <=1000 ;
commit;
create table TAB12_2 (COL1 number NOT NULL, COL2 VARCHAR2(4000), COL3 VARCHAR2(4000)) ;
alter table TAB12_2 add constraint PK_TBL12_2 primary key(COL1) ;
insert into TAB12_2 values(1, rpad('4KB_', 4000, '*'), null) ;
insert into TAB12_2 values(2, rpad('4KB_', 4000, '*'), rpad('4KB_', 4000, '*')) ;
commit;
set serveroutput on
SET SQLPROMPT '--'
set termout off
set trimspool on
set feedback off
spool createTBL12_3.sql
declare
strSQL varchar2(2000) ;
begin
strSQL := 'create table TAB12_3 (' ;
for i in 1..256 loop
strSQL := strSQL || 'COL' || lpad(to_char(i), 3, '0') || ' char(8),' ;
if (mod(i, 10) = 0) then
DBMS_OUTPUT.PUT_LINE(strSQL) ;
strSQL := '' ;
end if ;
end loop ;
strSQL := substr(strSQL, 0, length(strSQL) - 1) || ') ;' ;
DBMS_OUTPUT.PUT_LINE(strSQL) ;
end ;
/
spool off
set SQLPROMPT 'SQL>'
@createTBL12_3.sql
alter table TAB12_3 add constraint PK_TBL12_3 primary key(COL001) ;
insert into TAB12_3(COL001) values(0);
insert into TAB12_3(COL001, COL002) values(1, 'c2') ;
insert into TAB12_3(COL001, COL255) values(2, 'c255') ;
insert into TAB12_3(COL001, COL256) values(3, 'c256') ;
commit ;
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'TRY') ;
いつもの演習通り、今回の体験で使用して頂くベースの表を作成して頂きます。ここでは各表の使用目的を紹介しておきます。
TAB12_1表は行移行の動作確認用で、TAB12_2表はブロック外行連鎖の動作確認用です。最後のTAB12_3は256列で定義される表であり、行断片という管理単位を理解する為に使用します。このように列数が多い表を作成する際に、上記のPL/SQLのループ処理とSPOOLコマンドを上手に使いこなせるとカッコイイですよね。
さて、準備が整いましたね。では、いよいよ演習の本番に入っていきましょう。
■ 1. TAB12_1表において行移行の存在有無を確認してください。
sqlplus TRY/TRY SQL> set lines 150 pages 500 col TABLE_NAME for a12 select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, CHAIN_CNT, AVG_ROW_LEN from USER_TABLES where TABLE_NAME = 'TAB12_1' ; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT AVG_ROW_LEN ------------ ---------- ---------- ------------ ---------- ----------- TAB12_1 1000 5 0 0 19 @?/rdbms/admin/utlchain.sql desc CHAINED_ROWS 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- OWNER_NAME VARCHAR2(30) TABLE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(30) PARTITION_NAME VARCHAR2(30) SUBPARTITION_NAME VARCHAR2(30) HEAD_ROWID ROWID ANALYZE_TIMESTAMP DATE analyze table TAB12_1 list chained rows ; select TABLE_NAME, HEAD_ROWID from CHAINED_ROWS ; レコードが選択されませんでした。
USER_TABLESディクショナリ・ビューには、CHAIN_CNT列が存在しています。よってこの列で行移行や行連鎖の存在有無を確認できそうだ。というのは正しいですが間違いなのです。この列に値が格納されるタイミングはANALYZE文で表の統計情報を収集した場合のみであり、今回は演習問題0においてDBMS_STATS.GATHER_SCHEMA_STATSプロシージャを使用して各表のオプティマイザ統計情報を収集しています。よって、改めてANALYZE文で表の統計情報を収集するかというと、それもあまり実行したくはないです。その理由は統計情報が上書きされてしまうからですね。Oracle Database 11gではオプティマイザ統計情報の収集は、DBMS_STATSパケージの使用を推奨しているので、統計情報を上書きせずに行移行、行連鎖を確認したいのが本音です。
※ DBMS_STATSパッケージとANALYZE文の違いについては、KROWN# 45856をご参照ください。
そしてこのような私のわがままを満たす、便利な方法が用意されていることがOracle Databaseの素晴らしさです。それが、上記の解答例の後半で実行しているCHAINED_ROWS表の作成（@?/rdbms/admin/utlchain.sql）と、その表へ行連鎖または行移行が発生しているレコードのROWIDを格納するコマンド（analyze table list chained rows）になります。
結果的には、TAB12_1表内で行連鎖/行移行が発生しているレコードは存在していないようですね。
■ 2. TAB12_1表のある一つのレコードを検索する際に、読み込まれるブロック数を確認してください。
sqlplus TRY/TRY
SQL>
set lines 150 pages 500
set autot on
select ROWID, COL1, substr(COL2, 1, 10) from TAB12_1 where COL1=500 ;
ROWID COL1 SUBSTR(COL2,1,10)
------------------ ---------- ----------------------------------------
AAAEsfAAAAAAACGADB 500 initial Re
実行計画
----------------------------------------------------------
Plan hash value: 2784469625
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB12_1 | 1 | 19 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_TBL12_1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL1"=500)
統計
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
719 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
慣れていれば難しい作業ではないですね。答えは3ブロック（= consistent gets）となります。
以前の演習の復習になりますが、TAB12_1表の1レコードを検索する場合は、索引PK_TBL12_1にまずアクセスすることでCOL1=500のレコードのROWIDを特定し、そのROWIDからレコードが格納されているブロック番号がわかるので、TAB12_1表の対象ブロックへアクセスするという処理流れですね。さあ、このレコードを行移行させてみましょう。
■ 3. 演習2で検索したレコードのCOL2列の値を「rpad(‘updated’, 4000, ‘*’」で更新後、行移行の有無と対象レコード検索時にアクセスするブロック数を確認してください。
sqlplus TRY/TRY
SQL>
update TAB12_1 set COL2=rpad('updated', 4000, '*') where COL1=500 ;
commit ;
analyze table TAB12_1 list chained rows ;
select TABLE_NAME, HEAD_ROWID from CHAINED_ROWS ;
TABLE_NAME HEAD_ROWID
------------------------------ ------------------
TAB12_1 AAAEsfAAAAAAACGADB
set lines 150 pages 500
set autot on
select ROWID, COL1, substr(COL2, 1, 10) from TAB12_1 where COL1=500 ;
ROWID COL1 SUBSTR(COL2,1,10)
------------------ ---------- ----------------------------------------
AAAEsfAAAAAAACGADB 500 updated***
実行計画
----------------------------------------------------------
Plan hash value: 2784469625
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB12_1 | 1 | 19 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_TBL12_1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL1"=500)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
719 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
さて、いかがでしょうか。行移行を確認することができましたね。
TAB12_1表が格納されている表領域のブロックサイズは8KBであり、TAB12_1のPCTFREE（更新時に使用するブロック内の空き領域の比率）は10%であることから、対象レコードが元々格納されているブロックの空き領域は800バイト前後（= 8KB * 10%）です。その状態で、「initial Record」という数バイトのCOL2列のデータを4000バイトの列データに更新したということは、ブロック内の空き領域では満たすことが不可能です。このような場合、行ヘッダーを除く行全体を別のブロックへ移動することで対処するのですが、この現象を行移行が発生したと言います。CHAINS_ROWS表を検索すると出力されるTAB12_1表のROWIDは、まさに更新したレコードのROWIDと一致していることが確認できると思います。
何故、行ヘッダーも一緒に移動しないのか？という疑問を持たれると思いますが、答えは簡単です。行ヘッダーにはROWIDを含んでいます。そして、ROWIDはブロック番号と紐づいていて、ブロック番号が変わればROWIDも変わります。ROWIDはその表の全ての索引で使用されています。と説明すれば、お分かりですね。行ヘッダーも一緒に移動するということは、索引のメンテナンスが必要になるので、データベースとしては非常にコストが大きな処理なのですね。このコストを最小限に抑えるため、行ヘッダーは残し、全列データの固まり（行断片）だけを空きブロックへ移動する動作を採用しているのです。
また、行移行することにより、行移行していない場合に3ブロックしかアクセスする必要のなかった検索処理が、4ブロックのアクセスを必要とするように、アクセス対象のブロック数が増加する現象も確認できたかと思います。もちろん実行計画は同じですが、少し補足しておくと、まず索引へアクセスして対象レコードのROWIDを特定する処理は同じですし、そのROWIDを元にTAB12_1表の対象ブロックへアクセスする処理も同じです。しかし、行移行してしまった為に、TAB12_1表のブロックにアクセスしてみたが、そのブロック内には行ヘッダーしか存在しておらず、列データの固まり（行断片）は別ブロックに連鎖しているという情報が書き込まれています。よって、その連鎖先のブロックに追加でアクセスする必要が生まれるので、検索処理でアクセスを必要とする全ブロック数が3ブロックから4ブロックへ増加することになるのですね。
■ 4. TAB12_2表の行連鎖の有無を確認し、行連鎖しているレコードを特定してください。
sqlplus / as sysdba SQL> analyze table TAB12_2 list chained rows ; select HEAD_ROWID, COL1, substr(COL2, 1, 8), substr(COL3, 1, 8) from TAB12_2 a, CHAINED_ROWS b where a.ROWID = b.HEAD_ROWID ; HEAD_ROWID COL1 SUBSTR(COL2,1,8) SUBSTR(COL3,1,8) ------------------ ---------- --------------------------- --------------------------- AAAEshAAAAAAACXAAA 2 4KB_**** 4KB_****
オペレーションとしては、ここまでの復習となります。
今回は特に更新処理（update）を実行していないにも関わらず、行連鎖/行移行が発生していることが確認できます。でも良く考えてみればそれほど難しくありません。TAB12_2表で行連鎖しているレコード長はどの程度ありますか？そうですね。確実に8KBはあるでしょう。今回のブロックサイズは8KBでPCTFREEはデフォルト10%ですから、INSERT時に使用できる空のブロック内の空き領域は7KB+αですね。つまり、このレコードは一つのブロックに収まりません。なので、INSERT時に複数のブロックに列データを分割して格納することで、行連鎖が発生しているのですね。
ちなみに、TAB12_2表にはもう一つのレコードが格納されていますが、そちらは行連鎖していません。COL3列が「NULL」ですから領域を確保しておらず、レコード長としては4KB+αで1つのブロックに収まるからですね。この「NULL」の扱いについてはもう少し語る部分がありますが、それは次回に残しておきましょう。
さて、行移行と行連鎖という言葉の定義をここまで行ってこなかったのですが、改めて、これまで使用した言葉でまとめてみます。
- 行移行とは、元のブロックに収まらないUPDATEを実行した際に、行ヘッダーは元のブロックに残したまま、全列データの固まり（行断片）を他の空きブロックへ移動することを言います。
- 行連鎖とは、レコードをINSERTする際に、一つのブロックで収まらない場合に複数のブロックに分割して格納されることを言います。
行移行はUPDATE、行連鎖はINSERTと覚えるのが一般的かと思います。しかし、あまり知られてはいませんが、行連鎖はUPDATEでも発生します。さらに、行連鎖が起きていないように見えて、実は一つのブロック内で連鎖していることもありえます。この事象について解説しようとすると、ボリュームが大きくなると思われるので次回の連載でご紹介させて頂きたいと思います。今回は最後に、UPDATEで行連鎖する例を体験して終わりにしたいと思います。
■ 5. TAB12_3表において、COL001=0のレコードのCOL255列を更新した際の行連鎖の有無を確認してください。その後、COL256列を更新した際の行連鎖の有無も確認してください。
sqlpus TRY/TRY SQL> update TAB12_3 set COL255='hoge' where COL001=0 ; commit; analyze table TAB12_3 list chained rows ; select TABLE_NAME, HEAD_ROWID from CHAINED_ROWS where TABLE_NAME='TAB12_3' ; レコードが選択されませんでした。 update TAB12_3 set COL256='hoge' where COL001=0 ; commit; analyze table TAB12_3 list chained rows ; select TABLE_NAME, HEAD_ROWID from CHAINED_ROWS where TABLE_NAME='TAB12_3' ; TABLE_NAME HEAD_ROWID ------------------------------ ------------------ TAB12_3 AAAEsjAAAAAAACmAAA
非常に疑問が残る結果になったかと思います。COL001=0のレコードは元々第1カラムのCOL001にしか値が格納されていません。そのレコードの第255番目のカラムに値を格納するupdateを行った際には特に行連鎖/行移行は発生していません。しかし、第256番目のカラムに値を格納するupdateを行った際には行連鎖（上記の結果からは区別ができませんが、行移行ではない）しています。
さて、行連鎖と行移行といった少し今時では無い内容でしたが、いかがでしたでしょうか。
行連鎖は仕方ないにしても、行移行は確実にH/Wリソースを無駄に消費する要因です。行移行の解消方法を解説できませんでしたが、一昔前はexport & TRUNCATE & importで良く対処していたことを覚えています。今では、alter table move文、表のオンライン再定義、演習内で使用したCHAINED_ROWS表を元にDELETE + INESRTでも対処できると思いますので、試してみてください。
また、最後の絵因習5の現象が、私自身が経験した性能トラブルの根本原因となります。どうも255列というのが怪しいですよね。冒頭でご紹介した「行断片」というキーワードを元に既に概要マニュアルを読まれた方であれば、これが行断片の動作と関わりがありそうだということが予想できていると思います。さらには、NULLの扱いですね。この辺りを次回の連載で体験して頂きたいと考えています。
今回もありがとうございました。次回も頑張りますので、よろしくお願いします。