実験：意外とデータ入らない件
今回はオラクルにおけるデータに関する実験。 データ格納の最小単位であるブロックに入るデータが予想外に小さかったことを実験により体感した。
概要
オラクルではデータの最小単位をブロックで管理する。 そしてこのブロックをいくつかまとめたものをエクステントと呼び データ領域を確保する際はエクステント単位で確保する。 これらエクステントの集合をセグメントと呼ぶ。表はセグメントの１つである。正確な説明はオラクルの公式ドキュメント に譲るとして今回は、行データが格納されるブロック、これが実際にどのくらいのデータサイズを格納できるのか実験した。
環境
oracle 12c ( Single Instance )
表領域の作成
前回の記事で書いた通り、最小の表領域は81KB(ブロックサイズを8KBとした際に11ブロックなので実際には88KB)であることが分かっている。 簡単のため今回も最小サイズで作成する。
SQL> create tablespace test_tblsp datafile '/tmp/test_tblsp.dbf' size 81k;
表領域が作成されました。
-- データファイルサイズの確認。ブロックサイズの倍数で確保するため88KBになっている。
SQL>
select tablespace_name, bytes/1024 KB from dba_data_files
2 where tablespace_name='TEST_TBLSP';
TABLESPACE_NAME
--------------------------------------------------------------------------------
KB
----------
TEST_TBLSP
88
この状態で表領域の使用率を確認する。
SQL> select * from DBA_TABLESPACE_USAGE_METRICS
2 where tablespace_name='TEST_TBLSP';
TABLESPACE_NAME
--------------------------------------------------------------------------------
USED_SPACE TABLESPACE_SIZE USED_PERCENT
---------- --------------- ------------
TEST_TBLSP
3 11 27.2727273
SQL>
88KB/8KB=11ブロック確保されていることがTABLESPACE_SIZE列の値から見て取れるが、すでにそのうち3ブロックが使用済みになっている。 このことから、この表領域に表を作成する際に確保されるエクステントは 11-3=8なので、最大でもせいぜい8ブロックである。
続いて、この表領域のデフォルト初期エクステンとサイズを確認する。
SQL> select block_size/1024 "block size (KB)",initial_extent/1024 "initial extent size (KB)" from dba_tablespaces
2 where tablespace_name='TEST_TBLSP';
block size (KB) initial extent size (KB)
--------------- ------------------------
8 64
SQL>
上記の結果から、デフォルトの初期エクステントサイズは64KB,すなわち8ブロックであることが分かる。
表の作成
さて、この状態で表を作成する。
SQL> show parameter nls_length
NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
nls_length_semantics string
BYTE
SQL>
SQL> create table testuser.testtbl1 ( c1 varchar2(4000) ) tablespace test_tblsp;
表が作成されました。
select * from dba_extents
2 where segment_name='TESTTBL1';
レコードが選択されませんでした。
SQL>
SQL>
説明を補足すると、nls_length_semantics=BYTEであるため、
varchar2(4000)は、最大4000バイトまでのデータを格納できるデータ型である。
また、表を作成しただけではエクステントもセグメントも確保されない。
これは仕様の動作で、最初のレコードがINSERTされたタイミングで領域が確保される。
これはrollbackしたからといって領域が解放されるわけではない。
実験
ちょいと説明しすぎたところでこれからの実験の流れを説明する。
- 表の行データは、カラムc1のデータ型最大の4000バイトのデータをINSERTする。
- データをINSERTすることによって8KBのブロックが8個で構成されるエクステントが確保される。
- 8x8=64KBのエクステントに4000バイトの行データは何行入るか繰り返しINSERTする。
8KBx8=64KBが確保されるのであれば4000バイトの行データは 仮に4KBと仮定したとしたら16行入る計算になるが ブロックあたりにPCTFREEが10%で設定されているため、16x0.9=14行ほどか。ただ他にもブロックには色々なヘッダー情報があるため、なんとなく10行程度と予想。
では始める。
-- pct_freeの値の確認
SQL> select pct_free from dba_tables
2 where table_name='TESTTBL1';
PCT_FREE
----------
10
SQL>
SQL> insert into testuser.testtbl1 values (rpad('a',4000,'a'));
1行が作成されました。
--エクステントが確保されたことを確認
SQL> select block_id, bytes/1024 KB, blocks from dba_extents
2 where segment_name='TESTTBL1';
BLOCK_ID KB BLOCKS
---------- ---------- ----------
4 64 8
-- レコードのcommit
SQL> commit;
コミットが完了しました。
表領域の使用率を確認するとエクステントが確保され使用率が100%になったことが分かる。
SQL> select * from DBA_TABLESPACE_USAGE_METRICS
2 where tablespace_name='TEST_TBLSP';
TABLESPACE_NAME
--------------------------------------------------------------------------------
USED_SPACE TABLESPACE_SIZE USED_PERCENT
---------- --------------- ------------
TEST_TBLSP
11 11 100
では続いてINSERTを可能な限り実行していく。
--2行目
SQL> insert into testuser.testtbl1 values (rpad('a',4000,'a'));
1行が作成されました。
--3行目
SQL> insert into testuser.testtbl1 values (rpad('a',4000,'a'));
1行が作成されました。
--4行目
SQL> insert into testuser.testtbl1 values (rpad('a',4000,'a'));
1行が作成されました。
--5行目
SQL> insert into testuser.testtbl1 values (rpad('a',4000,'a'));
1行が作成されました。
--6行目
SQL> insert into testuser.testtbl1 values (rpad('a',4000,'a'));
insert into testuser.testtbl1 values (rpad('a',4000,'a'))
*
行1でエラーが発生しました。:
ORA-01653: 表TESTUSER.TESTTBL1を拡張できません(8分、表領域TEST_TBLSP)。
SQL> commit;
コミットが完了しました。
SQL> select count(*) from testuser.testtbl1;
COUNT(*)
----------
5
実験結果
実験から5行が限界であった。
8ブロックで5行。予想外に少ない。というわけで各行のrowidを見てみる。
SQL> select rowid from testuser.testtbl1;
ROWID
------------------
AAAWlkAAFAAAAAHAAA
AAAWlkAAFAAAAAIAAA
AAAWlkAAFAAAAAJAAA
AAAWlkAAFAAAAAKAAA
AAAWlkAAFAAAAALAAA
SQL>
オラクルの公式ドキュメント を参照し解読すると
- データオブジェクト番号：AAAWlk
- データファイル番号：AAF
- データブロック番号：AAAAA[H-L]
- 行番号：AAA
1行あたり、1ブロックに収まっている模様。 にしても8ブロックでエクステント作ってるんだから1行1ブロックとして8行はデータ入って欲しい気もする。
試しにこのあと、表をvarchar2(2000)で作成し直し試したところ 行データはブロックあたり3行が5ブロックの合計15行INSERTできたので表領域作成時に3ブロックがすでに使用済みになっていたように、エクステントも確保した際に3ブロックを使用するのかもしれない。 それだと納得がいく。
また、1ブロックあたり8KBで行データ4000バイトが1行しか入らないということは その他は諸々別の領域で使用されていると推測される。
念のため、行連鎖が発生していないことを確認しておく。
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'TESTUSER') ;
PL/SQLプロシージャが正常に完了しました。
select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, CHAIN_CNT, AVG_ROW_LEN
from DBA_TABLES
3 where TABLE_NAME = 'TESTTBL1' ;
TABLE_NAME
--------------------------------------------------------------------------------
NUM_ROWS BLOCKS EMPTY_BLOCKS CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- -----------
TESTTBL1
5 5 0 0 4001
SQL>
SQL> @?/rdbms/admin/utlchain.sql
表が作成されました。
SQL> desc CHAINED_ROWS
名前 NULL? 型
----------------------------------------- -------- ----------------------------
OWNER_NAME VARCHAR2(128)
TABLE_NAME VARCHAR2(128)
CLUSTER_NAME VARCHAR2(128)
PARTITION_NAME VARCHAR2(128)
SUBPARTITION_NAME VARCHAR2(128)
HEAD_ROWID ROWID
ANALYZE_TIMESTAMP DATE
SQL> analyze table testuser.testtbl1 list chained rows ;
表が分析されました。
SQL>
SQL> select TABLE_NAME, HEAD_ROWID from CHAINED_ROWS ;
レコードが選択されませんでした。
SQL>
上記結果から、行連鎖は発生していない。
まとめ
8KBのブロックの表領域において、表を作成した際に8x8=64KBのエクステントを確保できたとしても、4000バイトのレコードの場合は5行しか入らないことが分かった。サイズにして19KB。実に少ない。 この実験から、表領域を確保した際には3ブロックがすでに使用済みの状態になっており、またエクステントを確保した場合も3ブロックがすでに使用済みの状態になっているように予想される。なお、この認識が間違っている場合は指摘いただければ幸いである。