しばちょう先生の試して納得！DBAへの道 indexページ▶▶
みなさん、こんにちは。 “しばちょう”こと柴田長（しばた つかさ）です。
前回から引き続き、基本中の基本である行移行、行連鎖について解説させて頂きます。前回は行移行と行連鎖が発生する原因と仕組みを理解して頂きましたが、今回はNull値のデータ格納のされ方と表定義上での列の順序の関係を理解して頂いた後、実際に私が性能問題に陥ったケースを体験して頂きたいと思います。
あるチューニング方法のメリットのみに注目して実装した結果、結果的にはデメリットを引き当ててしまい性能劣化を引き起こしたしまった経験をされたことはありませんか？チューニングのメリットとそれに伴うデメリットをきちんと理解しなければ、適用、実装することにはリスクがあります。そのような体験を練習しておきたくても、実際に問題が発生するまで学べないのが現状だとも思っていますので、少し解説が難しい箇所がありますがOracle Database 11g Release 2 Enterprise Editionのデータベースで次の演習を試してみてください。
- 表定義の列順序を気にする必要あるの？
- 行断片とは？
- ブロック内行連鎖とブロック外行連鎖の違いは？
■0. 次のSQLで今回の演習で使用するセグメントを作成してください。使用するスキーマや表領域は適宜変更してください。
sqlplus / as sysdba
SQL>
connect TRY/TRY
set serveroutput on
set sqlprompt '--'
set termout off
set trimspool on
set feedback off
set linesize 150
spool createTBL13.sql
declare
strSQL varchar2(2000) ;
begin
-- create table TAB13_1
strSQL := 'create table TAB13_1 (COL001 number,' ;
for i in 2..900 loop
strSQL := strSQL || 'COL' || lpad(to_char(i), 3, '0') || ' char(8),' ;
if (mod(i, 10) = 0) and (i != 900) then
DBMS_OUTPUT.PUT_LINE(strSQL) ;
strSQL := '' ;
end if ;
end loop ;
strSQL := substr(strSQL, 0, length(strSQL) - 1) || ') ;' ;
DBMS_OUTPUT.PUT_LINE(strSQL) ;
-- create table TAB13_2
strSQL := 'create table TAB13_2 (COL001 number, COL100 char(8), COL200 char(8), COL300 char(8), COL400 char(8), COL500 char(8), COL600 char(8), COL700 char(8), COL800 char(8), COL900 char(8), ' ;
for i in 2..900 loop
if (mod(i, 100) != 0) then
strSQL := strSQL || 'COL' || lpad(to_char(i), 3, '0') || ' char(8),' ;
end if ;
if (mod(i, 10) = 0) and (i != 900)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> '
set echo on
@createTBL13.sql
alter table TAB13_1 add constraint PK_TBL13_1 primary key(COL001) ;
alter table TAB13_2 add constraint PK_TBL13_2 primary key(COL001) ;
はい、いつもの演習通り今回の体験で使用して頂くベースの表を作成して頂きました。
上記SQLを実行することで、二つの表（TAB13_1表とTAB13_2表）が作成されます。どちらもCOL001～COL900の合計900個の列で構成される表ですが、それぞれの表定義の列の順序が異なります。USER_TAB_COLUMNSディクショナリ・ビューを参照して二つの表の列定義を確認してみましょう。
set lines 120 pages 5000
col COLUMN_NAME for a8
col TAB13_1 for a8
col TAB13_2 for a8
select T1.COLUMN_ID, T1.COLUMN_NAME "TAB13_1", T2.COLUMN_NAME "TAB13_2"
from (select COLUMN_ID, COLUMN_NAME from USER_TAB_COLUMNS where TABLE_NAME='TAB13_1') T1,
(select COLUMN_ID, COLUMN_NAME from USER_TAB_COLUMNS where TABLE_NAME='TAB13_2') T2
where T1.COLUMN_ID = T2.COLUMN_ID order by 1 ;
COLUMN_ID TAB13_1 TAB13_2
---------- -------- --------
1 COL001 COL001
2 COL002 COL100
3 COL003 COL200
4 COL004 COL300
5 COL005 COL400
6 COL006 COL500
7 COL007 COL600
8 COL008 COL700
9 COL009 COL800
10 COL010 COL900
11 COL011 COL002
12 COL012 COL003
13 COL013 COL004
14 COL014 COL005
15 COL015 COL006
.. ...... ......
888 COL888 COL887
889 COL889 COL888
890 COL890 COL889
891 COL891 COL890
892 COL892 COL891
893 COL893 COL892
894 COL894 COL893
895 COL895 COL894
896 COL896 COL895
897 COL897 COL896
898 COL898 COL897
899 COL899 COL898
900 COL900 COL899
USER_TAB_COLUMNSディクショナリ・ビューにはCOLUMN_ID列が存在し、この列が対象表の列順序を示しています。どちらの表も900個の列が存在しますが、TAB13_1表はCOL001～CL900まで順番に定義されているのに対して、TAB13_2表は先頭から10個の列がCOL001.、COL100、COL200、…、COL900のおよそ100刻みで11番目以降はそれらを抜いた数字の列が順番に定義されています（表現が難しい…）。つまり、今回の演習では、TAB13_1表はチューニング前、TAB13_2表はチューニング後の表と認識してくださいね。どのようなチューニングなのか？については、早速、次の演習1で体験してみてください。
■1. 二つの表の10個の列 (COL001,COL100,COL200,COL300,COL400,COL500,COL600,COL700,COL800,COL900) にだけ値がセットされるレコードを10万行INSERTした後、各セグメントのサイズを確認してください。
sqlplus TRY/TRY
SQL>
insert into TAB13_1(COL001,COL100,COL200,COL300,COL400,COL500,COL600,COL700,COL800,COL900)
select LEVEL, 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'
from DUAL connect by LEVEL <= 100000 ;
commit ;
insert into TAB13_2(COL001,COL100,COL200,COL300,COL400,COL500,COL600,COL700,COL800,COL900)
select LEVEL, 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'
from DUAL connect by LEVEL <= 100000 ;
commit ;
col SEGMENT_NAME for a16
select SEGMENT_NAME, BYTES/1024/1024 from USER_SEGMENTS ;
SEGMENT_NAME BYTES/1024/1024
---------------- ---------------
TAB13_1 120
TAB13_2 11
PK_TBL13_1 2
PK_TBL13_2 2
いかがですか？ちょっと衝撃的な結果が出たのではないでしょうか？まったく同じINSERT文で二つの表にレコードを格納したにも関わらず、結果的にセグメントサイズに10倍以上の開きが生じています。わかりやすい結果とするため少し極端なケースで演習し頂きましたが、これが良く知られたチューニング方法ですね。
二つの表の違い表定義上での列順序しかありません。この列の順序と演習問題で指定された列名を比較してみてください。TAB13_1表では値がセットされた列が均等に分散していますが、TAB13_2表では先頭の10個に集中していることが理解できると思います。とは言え、注目すべきは値がセットされていない（Null値となる）列の方です。概要マニュアル内の「Null値の格納」では、次のように解説されています。
データ値を持つ2つの列の間にはさまれたNULLはデータベースに格納されます。このような場合、NULLには列の長さ（ゼロ）を格納する1バイトのみが必要となります。行の末尾にあるNULLには、記憶域は不要です。これは、新しい行のヘッダーが、前行の残りの列がNULLであることを知らせるためです。たとえば、表の最後の3列がNULLであれば、その3列にはデータは格納されません。
という理由で、二つの表のセグメントサイズの違いが生まれたのです。概要マニュアルは本当に素晴らしいと実感する瞬間ですよね。少し補足説明しておきます。この動作を二つの表に当てはめて考えてみましょう。
赤文字で示した部分がTAB13_1表、青文字で示した部分がTAB13_2表を意味していることをご理解頂けると思います。この演習のINSERTではどちらの表においても全900列中の890列がNull値ですが、TAB13_1表はそのNull値の890列の全てがデータ値を持つ2つの列に挟まれています。つまり、Null値の全890列には列の長さがゼロであることを示す1バイト（全890バイト）が1レコード毎に格納されています。一方、TAB13_2表のNull値の890列は、第11番目の列から第900番目の列の最後までNullですから、この1バイト（合計890バイト）が全レコードで格納されていません。
だからと言って、私はTAB13_2表のように全ての表の定義を見直した方が良いとは言いません。表の特性により、このチューニングが適切か否かは変化してきます。これを理解する為に、もう少し演習を進めてみましょう。
■ 2. 二つの表の全レコードのCOL899列に値を格納する更新を実行した後、セグメントサイズと行連鎖の数を確認してください。
sqlplus TRY/TRY SQL> set timing on update TAB13_1 set COL899='hoge' ; commit ; 経過: 00:00:03.16 update TAB13_2 set COL899='hoge' ; commit ; 経過: 00:00:21.60 col SEGMENT_NAME for a16 select SEGMENT_NAME, BYTES/1024/1024 from USER_SEGMENTS ; SEGMENT_NAME BYTES/1024/1024 ---------------- --------------- TAB13_1 120 TAB13_2 120 PK_TBL13_1 2 PK_TBL13_2 2 analyze table TAB13_1 list chained rows ; analyze table TAB13_2 list chained rows ; select TABLE_NAME, count(HEAD_ROWID) from CHAINED_ROWS group by TABLE_NAME ; TABLE_NAME COUNT(HEAD_ROWID) ------------------------------ ----------------- TAB13_2 100000
またまた驚きの結果が出たのではないでしょうか？
まず、同じUPDATE文を実行した際の実行時間の差を見てください。大きく異なることが解ると思います。ここまでの演習では列順序をチューニングしたTAB_13_2表が全ての点で良い結果を出していましたが、ここでは逆転していますよね。この実行時間の差を理解する為に、UPDATE前後のセグメントサイズを確認してみましょう。TAB13_1表は120MB のままUPDATE前後で変化はないですが、TAB13_2表は11MBから120MBへ増加しています。このサイズ増加の分、UPDATEの実行時間が長くなりそうだとイメージできると思います。
しかし、何故このサイズ増加が何故発生したのか？同じ値で更新しているTAB13_1表でも同じサイズが増加するはず？と悩みどころだと思います。そのヒントが行連鎖の数を確認した結果に表れています。TAB13_1表には行連鎖（後に解説しますが、ここではブロック外行連鎖の意味）は発生していませんが、TAB13_2表では行連鎖が全レコードで発生しています。つまり、TAB13_2表では新たな行断片が別ブロックに生成されたことを意味しています。これについて少し難しいですが頑張って解説してみます。
演習2の解説において、TAB13_2表は第11番目の列から最後の第900番目の列までNull値の長さのゼロ示す1バイトが省略されたと説明させて頂きました。UPDATEによりCOL899列に「hoge」という文字列を格納しましたが、COL899列はTAB13_2表では最後の第900番目の列でなので、第11番目から第899番目の列はデータ値を持つ第10番目の列と第900番目の列で挟まれることになるため、各列において省略していた1バイト（合計890バイト）を格納する必要が生まれることになります。結果としてTAB13_1表とほぼ同じようなデータの格納状態になるわけです。よって、セグメントサイズが同じ120MBまで拡張されたことになります。
さらに、これまで各列の1バイトを省略して全レコードをデータブロックに無駄なく”カツカツに”格納していたわけですから、UPDATEによって必要となった各レコード890バイト分を同じデータブロックに格納することが難しい状況になっています。よって、必然的に行連鎖が発生することになるのです。
初めからCOL899列がUPDATEされてデータが格納されることを見越していれば、TAB13_2表のようにCOL899列を最後の列に定義することは無かったでしょうし、COL899列を第11番目に定義してデータ値を持つ列に挟まれる列数を減らすことも可能だと思います。しかし、全ての表の全ての列に対し、どのようなタイミングでデータ値が格納されるのかを事細かに把握することは非常に難しいところです。演習1で体験して頂いた列順序を並び替えるチューニング方法には、このようなデメリットも存在すること、そのデメリットの発生を極小化する為には、より細かな情報収集が必要となることを少しだけでもご理解して頂けたと思います。
最後に、これまでの解説の中で登場してきた、ブロック内行連鎖とブロック外行連鎖の違いについて理解する為の演習を用意しました。TAB13_1表ではブロック外行連鎖は発生していませんが、実はブロック内行連鎖は発生しています。あまり知られてはいない部分だと思っていますので、是非、「無視して良い行連鎖が存在する」って周囲のDBAへ自慢してみてくださいね。
■3. 各表において、ある一つのレコードのCOL001列とCOL899列の値を参照するSELECT文を実行した際の、物理読み込みブロック数と論理読み込みブロック数を計測してください。
sqlplus / nolog
SQL> alter system flush buffer_cache;
connect TRY/TRY
set pages 5000 lines 150
set autotrace on
select COL001, COL899 from TAB13_1 where COL001=50000 ;
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
3 physical reads
0 redo size
624 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select COL001, COL899 from TAB13_2 where COL001=50000 ;
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
4 physical reads
0 redo size
624 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
予想通りの結果を確認できたかと思います。TAB13_2表を検索した場合、ディスクから読み込んだブロック数（物理読み込みブロック数 = physical reads）が1つ多い（= 4 – 1）ことが解ります。これがブロック外行連鎖の発生しているレコードだったことを意味しています。
もう一点、ここで気にある点があります。それは読み込み元（ディスク or バッファ・キャッシュ）を問わず読み込んだ全ブロック数（論理読み込みブロック数 = consistent gets）の値がディスクから読み込んだブロック数と異なる点です。今回の演習ではSELEECT文を実行する前に、バッファ・キャッシュのクリアを行う「alter system flush buffer_cache ;」を実行しているので、SELECT文を実行する為に必要となったデータブロックは全てディスクから読み込まれるはずにも関わらず、例えばTAB13_1表では3ブロック分（= consistent gets – physical reads = 6 – 3）をバッファ・キャッシュから読み込んだと記録されています。これはphysical readsで示されるディスクから読み込んだブロックがバッファ・キャッシュ上にキャッシュされ、そのキャッシュされたブロックを再読み込みしたと解釈することができます。つまり、同じブロックに複数回アクセスしたということになります。
もう少し正確に、このconsistent getsとphysical readsの差を理解する為、次の演習を試してみてください。
■ 4.演習3と同じレコードのCOL001列だけを参照するSELECT文を実行し、索引部分の論理読み込みブロック数を確認してください。
sqlplus / nolog
SQL> alter system flush buffer_cache;
select COL001 from TAB13_1 where COL001=50000 ;
実行計画
----------------------------------------------------------
Plan hash value: 93688524
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_TBL13_1 | 1 | 13 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
2 physical reads
0 redo size
547 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select COL001 from TAB13_2 where COL001=50000 ;
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_TBL13_2 | 1 | 13 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
統計
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
2 physical reads
0 redo size
547 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
はい、いかがでしょうか。少し演習問題の意味がわかりづらくて申し訳なかったのですが、索引だけでCOL001列の値を参照することで、演習3の統計値から索引アクセスに使用したブロック数を引き算する数字を確認することが目的の演習です。
実行計画を確認してみると、どちらの表に対するSELECT文も索引しか使用しておらず、表にはアクセスしていませんよね。さらに、この時に読み込まれたブロック数はconsistent getsとphysical readsともに「2」という結果が出ています。ということで、演習3の結果と合わせて解説します。
まず、TAB13_1表について考えてみます。演習3のSELECT文では「Consistent Gets=6、Physical Reads=3」で、演習4において索引のみ使用するSELECT文では「Consistent Gets=2、Physical Reads=2」なので、その差である「Consistent Gets=4、Physical Reads=1」が表セグメント上のレコードにアクセスする為に読み込まれたブロック数になります。つまり、ディスクから1つのブロックを読み込んだ後、キャッシュされたそのブロックをさらに3回読み込んでいることが理解できるので、一つのデータブロックを合計4回繰り返し使用したと解釈できます。実はこの4回というのが行断片の数なのです。紹介が遅くなりましたが、概要マニュアルの「行形式」で解説されている通り、一つの行断片は255列までしか格納することができません。256列以上のレコードは複数の行断片に分割してデータブロック内に格納されています。今回扱ったTAB13_1表は合計900列で定義されているので、一つのレコードは4つの行断片で構成されている計算になり、まさに、上記で述べた「1つのブロックを4回使用」した理由に合致しますね。同じ一つのブロックに複数の行断片が連鎖して格納されているので、これを「ブロック内行連鎖」と呼びます。
一方、TAB13_2表について同じように計算してみます。演習3では「Consistent Gets=7、Physical Reads=4」、演習4では「Consistent Gets=2、Physical Reads=2」という結果から表セグメント上のレコードにアクセスする為に読み込まれたブロック数は「Consistent Gets=5、Physical Reads=2」となります。つまり、1つのレコードにアクセスする為に、ディスクから2つのブロックを読み込み、ディスクへのアクセス回数が2回発生したことになります。この状態を「ブロック外行連鎖」が発生しているということになります。
さて、いかがでしたでしょうか。ブロック内行連鎖、ブロック外行連鎖ともに、一つのレコードが複数の行断片で構成されていることに違いはありませんが、ブロック内行連鎖はディスクアクセス回数が1回でバッファ・キャッシュ上にキャッシュされたブロックを繰り返し使用するので、CPUの処理能力に依存します。一方、ブロック外行連鎖は複数のデータブロックをディスクから読み込む必要があるので、ディスク側の処理能力に依存します。前回の冒頭でも書かせて頂いたように、近年CPU性能は飛躍的に向上してきていますが、ハードディスクドライブの性能（IOPS）はそれに及びません。よって、DBAとして気をつけなければならないのは後者のブロック外行連鎖となることはご理解頂けると思います。
また、同じレコードを格納する表であっても、列順序の定義とデータ格納、更新の順序により、これらの行連鎖の発生状況が異なることも体験して頂けたと思います。チューニングにはメリットだけではなく、デメリットもありますので、それらを正確に理解し、適用するデータベースの特性も考慮した適用、実装が必要になります。でも、そこがDBAの腕の見せ所でもありますよね。
私自身も目が回るような解説文となってしまい申し訳なかったのですが、データベースの奥深さを実感して頂けていれば今回の演習の目的を達成できたと思っています。今回もありがとうございました。次回も頑張りますので、よろしくお願いします。