SQL*Loaderの性能見積もりの考え方 [アーキテクチャ]
はじめに
データ移行等でcsvをSQL*LoaderでOracleにロードする際、どの程度の性能を期待できるのかを見積もる必要がある。特定の表についてであれば、実機の基礎性能をベースに件数で比例させれば簡単に見積もりができる。しかし、実際は様々な表があり、それぞれに対して測定する訳にもいかない。ここでは、簡単なテストケースでLoaderの基礎性能を測定し、平均行長と行数からロード性能を見積もる方法について考察してみたい。
１．性能モデル
やりたいことは、特定の環境において、入力となるcsvの平均行長と行数から、SQL*Loaderの性能を見積もりすることである。
方法として考えたのは、平均行長の異なる3つの表、b10tbl, b50tbl, b100tblについて、一定の件数（10,000,000件）のロード性能を基礎数値として取得し、それをもとに性能を見積もる方法である。
モデルとした表は、idカラムをnumber型の主キーとし、1からの連番を振る。dtカラムはdate型で、更新日時を入れる。col1～colnはvarchar2(10)のカラムで、b10tblは1個、b50tblは5個、b100tblは10個とし、ランダムな文字列を10バイト挿入する。
Loaderの測定パタンとしては、一般的なにコンベンショナルとダイレクトパスロードを考える。前提として、データは主キー順に並んでいることは保証されず、主キーは作成しつつ挿入することとする。NOLOGGINGも使わない。表のPCTFREEはデフォルト10%、記憶域（初期エクステントサイズ）もデフォルト(64KB)とする。
２．表（データ）の準備
表を作成する。
drop table b10tbl cascade constraints; create table b10tbl( id number(9), dt date, col1 varchar2(10), constraint pk_b10tbl primary key (id) ); drop table b50tbl cascade constraints; create table b50tbl( id number(9), dt date, col1 varchar2(10), col2 varchar2(10), col3 varchar2(10), col4 varchar2(10), col5 varchar2(10), constraint pk_b50tbl primary key (id) ); drop table b100tbl cascade constraints; create table b100tbl( id number(9), dt date, col1 varchar2(10), col2 varchar2(10), col3 varchar2(10), col4 varchar2(10), col5 varchar2(10), col6 varchar2(10), col7 varchar2(10), col8 varchar2(10), col9 varchar2(10), col10 varchar2(10), constraint pk_b100tbl primary key (id) );
SQL*Loader用のcsvを生成するための初期データを挿入する。
begin
for i in 1..10 loop
insert into b10tbl select
1000000*(i-1)+rownum id,
sysdate dt,
dbms_random.string('x',10) col1
from dual connect by level <=1000000;
commit;
end loop;
end;
/
begin
for i in 1..10 loop
insert into b50tbl select
1000000*(i-1)+rownum id,
sysdate dt,
dbms_random.string('x',10) col1,
dbms_random.string('x',10) col2,
dbms_random.string('x',10) col3,
dbms_random.string('x',10) col4,
dbms_random.string('x',10) col5
from dual connect by level <=1000000;
commit;
end loop;
end;
/
begin
for i in 1..10 loop
insert into b100tbl select
1000000*(i-1)+rownum id,
sysdate dt,
dbms_random.string('x',10) col1,
dbms_random.string('x',10) col2,
dbms_random.string('x',10) col3,
dbms_random.string('x',10) col4,
dbms_random.string('x',10) col5,
dbms_random.string('x',10) col6,
dbms_random.string('x',10) col7,
dbms_random.string('x',10) col8,
dbms_random.string('x',10) col9,
dbms_random.string('x',10) col10
from dual connect by level <=1000000;
commit;
end loop;
end;
/
統計情報を取得しておく（_optimizer_gather_stats_on_loadがデフォルト（TRUE）のため、空だと自動で取得されてしまうかもしれないため）。
exec dbms_stats.gather_table_stats('scott','b10tbl');
exec dbms_stats.gather_table_stats('scott','b50tbl');
exec dbms_stats.gather_table_stats('scott','b100tbl');
この時点でセグメントサイズを確認しておく。
col segment_name for a20 select segment_name, sum(bytes)/1024/1024 mb from user_segments where segment_name like '%B%TBL' group by segment_name order by 1; SEGMENT_NAME MB -------------------- ---------- B100TBL 1415 B10TBL 327 B50TBL 804 PK_B100TBL 160 PK_B10TBL 164 PK_B50TBL 163 6 rows selected.
ここから、csvファイルを出力するためのSQLファイルを作成する。ここで主キーでのソートはしない。csvの出力先を/u01とする（OCIのBaseDBは/u01のファイルシステムに196Gが割り当てられている。/homeは1GBもないので、大きなファイルを置いてはいけない）。
vi b10tbl.sql spool /u01/app/oracle/dump/scott/b10tbl.csv select * from b10tbl; spool off vi b50tbl.sql spool /u01/app/oracle/dump/scott/b50tbl.csv select * from b50tbl; spool off vi b100tbl.sql spool /u01/app/oracle/dump/scott/b100tbl.csv select * from b100tbl; spool off
上記sqlファイルをsqlplusから実行して、csvファイルを作成する。
sqlplus scott/xxx@oradb alter session set nls_date_format='YYYYMMDD HH24:MI:SS'; set feedback off heading off termout off set markup csv on delimiter '|' @b10tbl.sql @b50tbl.sql @b100tbl.sql set markup csv off
csvのサイズは以下の通り、最大のB100TBLで1.47GBであった。
[oracle@oradbvm1 ~]$ cd /u01/app/oracle/dump/scott/ [oracle@oradbvm1 scott]$ ll *.csv -rw-r--r-- 1 oracle oinstall 1578888897 Feb 18 16:19 b100tbl.csv -rw-r--r-- 1 oracle oinstall 408888897 Feb 18 16:17 b10tbl.csv -rw-r--r-- 1 oracle oinstall 928888897 Feb 18 16:18 b50tbl.csv [oracle@oradbvm1 scott]$
３．SQL*Loaderの準備
SQL*Loaderの制御ファイルを用意する。トランケートモードとして、表にデータが入っていれば切り捨てられる。
vi b10tbl.ctl load data characterset UTF8 infile '/u01/app/oracle/dump/scott/b10tbl.csv' badfile 'b10tbl.bad' discardfile 'b10tbl.dsc' truncate into table b10tbl fields terminated by '|' optionally enclosed by '"' trailing nullcols ( id integer external, dt date 'YYYYMMDD HH24:MI:SS', col1 char(10) ) vi b50tbl.ctl load data characterset UTF8 infile '/u01/app/oracle/dump/scott/b50tbl.csv' badfile 'b50tbl.bad' discardfile 'b50tbl.dsc' truncate into table b50tbl fields terminated by '|' optionally enclosed by '"' trailing nullcols ( id integer external, dt date 'YYYYMMDD HH24:MI:SS', col1 char(10), col2 char(10), col3 char(10), col4 char(10), col5 char(10) ) vi b100tbl.ctl load data characterset UTF8 infile '/u01/app/oracle/dump/scott/b100tbl.csv' badfile 'b100tbl.bad' discardfile 'b100tbl.dsc' truncate into table b100tbl fields terminated by '|' optionally enclosed by '"' trailing nullcols ( id integer external, dt date 'YYYYMMDD HH24:MI:SS', col1 char(10), col2 char(10), col3 char(10), col4 char(10), col5 char(10), col6 char(10), col7 char(10), col8 char(10), col9 char(10), col10 char(10) )
４．性能測定
上記のモデルを実際にOCIのBaseDBを利用して測定した。環境はこちらに記載の環境と同じため割愛する。
SQL*Loaderでロードを行う。まずは、コンベンショナルモードを使う。1000件毎にコミットとなるよう、rowsとbindsizeを設定する。
sqlldr scott/xxx@oradb control=b10tbl.ctl log=b10tblc.log rows=1000,bindsize=1000000,silent=errors,feedback sqlldr scott/xxx@oradb control=b50tbl.ctl log=b50tblc.log rows=1000,bindsize=1000000,silent=errors,feedback sqlldr scott/xxx@oradb control=b100tbl.ctl log=b100tblc.log rows=1000,bindsize=1000000,silent=errors,feedback
続いて、ダイレクトパス(direct=true)でデータロードを行う。1000件毎の処理となるよう、columnarrayrowsを設定する。
sqlldr scott/xxx@oradb control=b10tbl.ctl log=b10tbld.log direct=true, columnarrayrows=1000, silent=errors,feedback sqlldr scott/xxx@oradb control=b50tbl.ctl log=b50tbld.log direct=true, columnarrayrows=1000, silent=errors,feedback sqlldr scott/xxx@oradb control=b100tbl.ctl log=b100tbld.log direct=true, columnarrayrows=1000, silent=errors,feedback
５．測定結果
測定結果のサマリは下表の通り。
上記結果から、平均行長とロード性能の関係をグラフにすると、下図の通りとなった。行長が大きくなると線形に処理時間が伸びているように見えるため、ここから任意の平均行長について10,000,000件をロードする近似式を作ることができる。
上記近似式で得られた結果から、単位行数あたりのロード時間が求まるので、下図のように任意の行数を時間を見積もる式が得られる。例えば、平均行長223バイト、5,000,000件のb200tblの場合は、コンベンショナルは57秒、ダイレクトパスロードは23秒と見積もることができる。
答え合わせのため、実際に平均行長223バイト、5,000,000件で測定したところ、コンベンショナルの見積もり57秒に対し54秒、ダイレクトパスの見積もり23秒に対し28秒となった。若干ブレはあるものの、まずまずの値が見積もれることがわかった。
SQL> !ls -l /u01/app/oracle/dump/scott/b200tbl.csv
-rw-r--r-- 1 oracle oinstall 1113888896 Feb 20 07:36 /u01/app/oracle/dump/scott/b200tbl.csv
SQL> select 1113888896/5000000 from dual;
222.777779 →平均カラム長223バイト
SQL> !wc -l /u01/app/oracle/dump/scott/b200tbl.csv
5000000 /u01/app/oracle/dump/scott/b200tbl.csv →5,000,000件
[oracle@oradbvm1 ldrtest]$ grep Elapsed b200tbld.log
Elapsed time was: 00:00:28.24 →ダイレクトパス
[oracle@oradbvm1 ldrtest]$ grep Elapsed b200tblc.log
Elapsed time was: 00:00:54.42 →コンベンショナル
[oracle@oradbvm1 ldrtest]$
７．まとめ
本稿では、SQL*Loaderの性能を、実機の基礎性能を用いて、任意の行長、行数のロード性能（コンベンショナル、ダイレクト）を見積もる方法について考察した。性能の結果はあくまで一例で、実際は入力ファイルの要件に応じてLoaderのパラメータは実際の利用シーンにより適宜変更が必要だろう。なお、平均行長が伸びると、線形に処理時間も伸びる、という仮定は一定の範囲を超えると誤差が大きくなるかもしれない。ブロックサイズ（今回の環境では8KB、PCTFREE10%）に対して、平均行長が極端に大きい場合は誤差が大きくなる可能性があるので、大きな行長を扱う場合は基礎性能モデルの作り方に注意が必要だろう。
◆参考
[1]測定結果詳細：20240218_sqlldrPerfTest_v2.xlsx
2024-02-20 06:47
DBのCPUサイジングについての一考察 [アーキテクチャ]
はじめに
DBサーバにどの程度のCPUが必要かを見積りしなければならない状況はよくある。
基盤更改の場合は、現行のシステムがあり、そのHWスペックもリソース状況もわかっているので、CPUの性能比から概ね精度の高い見積もりが可能である。しかし、全く新規業務の場合は、机上でどの程度のCPUが必要かを導くことは難しい。しかも、業務仕様や処理方式が決まっていない状況ではなおさらである。このような場合、想定する処理方式に対する性能の基礎数値が重要となる。
本稿では、DBの基礎的な処理（INSERT, SELECT, UPDATE, DELETE）の基礎数値を取得する簡単なモデルと処理（PL/SQL）を、OCIの環境で実測し、その結果を考察してみたい。なお、性能の結果は環境によって変わるので、あくまで参考程度と考えてほしい。
１．性能モデル
性能モデルとして、3つの表、b10tbl, b50tbl, b100tblを考える。
idカラムをnumber型の主キーとし、1からの連番を振る。dtカラムはdate型で、更新日時を入れる。col1～colnはvarchar2(10)のカラムで、b10tblは1個、b50tblは5個、b100tblは10個とし、ランダムな文字列を10バイト挿入する。初期データ件数は100万件とする。
この表に対して、以下のパタンで基礎性能値を確認する。1番目はselectのフルスキャン、2～5番目は索引アクセスのパタン（insert、select、update、delete）である。索引アクセスは10万回行う。
２．表（データ）の準備
表を作成するDDLは以下の通り。
drop table b10tbl cascade constraints; create table b10tbl( id number(7), dt date, col1 varchar2(10), constraint pk_b10tbl primary key (id) ); drop table b50tbl cascade constraints; create table b50tbl( id number(7), dt date, col1 varchar2(10), col2 varchar2(10), col3 varchar2(10), col4 varchar2(10), col5 varchar2(10), constraint pk_b50tbl primary key (id) ); drop table b100tbl cascade constraints; create table b100tbl( id number(7), dt date, col1 varchar2(10), col2 varchar2(10), col3 varchar2(10), col4 varchar2(10), col5 varchar2(10), col6 varchar2(10), col7 varchar2(10), col8 varchar2(10), col9 varchar2(10), col10 varchar2(10), constraint pk_b100tbl primary key (id) );
初期データを挿入する。
insert into b10tbl select
rownum id,
sysdate dt,
dbms_random.string('x',10) col1
from dual connect by level <=1000000;
commit;
insert into b50tbl select
rownum id,
sysdate dt,
dbms_random.string('x',10) col1,
dbms_random.string('x',10) col2,
dbms_random.string('x',10) col3,
dbms_random.string('x',10) col4,
dbms_random.string('x',10) col5
from dual connect by level <=1000000;
commit;
insert into b100tbl select
rownum id,
sysdate dt,
dbms_random.string('x',10) col1,
dbms_random.string('x',10) col2,
dbms_random.string('x',10) col3,
dbms_random.string('x',10) col4,
dbms_random.string('x',10) col5,
dbms_random.string('x',10) col6,
dbms_random.string('x',10) col7,
dbms_random.string('x',10) col8,
dbms_random.string('x',10) col9,
dbms_random.string('x',10) col10
from dual connect by level <=1000000;
commit;
統計情報を取得する。
exec dbms_stats.gather_table_stats('scott','b10tbl');
exec dbms_stats.gather_table_stats('scott','b50tbl');
exec dbms_stats.gather_table_stats('scott','b100tbl');
作成した表のサイズを確認する。
col segment_name for a20 select segment_name, sum(bytes)/1024/1024 mb from user_segments where segment_name like '%B%TBL' group by segment_name order by 1;
３．測定
フルスキャン性能については、シンプルにautotraceの処理時間を想定する。select *としたのは、フルスキャンに主キーの索引が使われないにするためである。以下はb10tblの例である。
set timi on set autotrace traceonly select * from b10tbl; set autotrace off
INSERT性能は、PL/SQLで100万件の状態から10万件を追加する。この際、dtには更新日時、colxにはdbms_randomでランダムな文字列を生成して設定する。以下の例はb10tblなので、col1カラムだけであるが、b50tblでは5カラム、b100tblでは10カラム分、ランダムな文字列を生成し設定する。また、コミットは1件毎に実施する。
declare
i number;
begin
for i in 1000001..1100000 loop
insert into b10tbl values(i,sysdate,dbms_random.string('x',10));
commit;
end loop;
end;
/
SELECT性能は、同様にPL/SQLで、INSERTで積まれた10万件に対して、主キー一本引きのSQLを発行する。以下の例はb10tblなので、col1カラムだけであるが、b50tblでは5カラム、b100tblでは10カラム分、カラムを取得する。
declare i number; l_col1 varchar2(10); begin for i in 1000001..1100000 loop select col1 into l_col1 from b10tbl where id=i; end loop; end; /
SELECT性能は、同様にPL/SQLで、INSERTで積まれた10万件に対して、主キー一本引きでUPDATEを発行する。この際、dtには更新日時、colxにはdbms_randomでランダムな文字列を生成して設定する。以下の例はb10tblなので、col1カラムだけであるが、b50tblでは5カラム、b100tblでは10カラム分、ランダムな文字列を生成し設定する。コミットは1件毎に実施する。
declare
i number;
begin
for i in 1..100000 loop
update b10tbl set dt=sysdate,col1=dbms_random.string('u',10) where id=1000000+i;
commit;
end loop;
end;
/
DELETE性能は、同様にPL/SQLで、INSERTで積まれた10万件に対して、主キー一本引きのDELETEを発行する。コミットは1件毎に実施する。
declare i number; begin for i in 1000001..1100000 loop delete from b10tbl where id=i; commit; end loop; end; /
４．測定環境
上記のモデルを実際にOCIのBaseDBを利用して測定する。環境は以下の通り。
Shape: VM.Standard.E4.Flex CPU core count: 2 Oracle Database software edition: Enterprise Edition High Performance Storage management software: Oracle Grid Infrastructure Available data storage: 256 GB Recovery area storage: 256 GB Total storage size: 712 GB Theoretical max IOPS: 19.2K DB system version: 19.20.0.0.0
メモリの状態は以下の通り。SGAは14.5GB、そのうちバッファキャッシュは12GBである。
Memory Statistics
~~~~~~~~~~~~~~~~~ Begin End
------------ ------------
Host Mem (MB): 31,893.9 31,893.9
SGA use (MB): 14,848.0 14,848.0
PGA use (MB): 1,084.4 1,106.1
% Host Mem used for SGA+PGA: 49.95 50.02
Database Resource Limits
~~~~~~~~~~~~~~~~~~~~~~~~ Begin End
---------------- ----------------
CPUs: 4 4
SGA Target: 15,569,256,448 15,569,256,448
PGA Target: 3,892,314,112 3,892,314,112
Memory Target: 0 0
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 12,352M 12,352M Std Block Size: 8K
Shared Pool Size: 1,405M 1,404M Log Buffer: 154,872K
In-Memory Area: 0M 0M
上記の方法でデータを作成したところ、以下のサイズ（表と索引）となった。合計でたかだか338MBなので、全てバッファキャッシュに十分収まる。
SEGMENT_NAME MB -------------------- ---------- B100TBL 157 B10TBL 36 B50TBL 94 PK_B100TBL 17 PK_B10TBL 17 PK_B50TBL 17
５．測定結果
下表に測定結果を示す。rowsは処理件数、elapsed(ms)は処理の経過時間を示す。1番目のsel_fullはautotraceの経過時間、その他2～5番目はSQLレポート上の経過時間を示す。ms/rowは、1件あたりの処理時間を示す。例えばsel_fullであれば100万件の処理時間を100万で割った数値、sel_idxであれば、10万件の処理時間を10万で割った数値を示す。tpsは1をms/rowで割った値で、単位時間（1秒）あたりの処理件数を示す。
なお、参考[1]に、測定結果とあわせて、AWRレポート、SQLレポートをまとめたexcelシートを載せているので、詳細に興味があればそちらを確認頂きたい。
６．考察
フルスキャンはカラム長が長くなるほどに処理時間が延びる。カラム長が長いと参照ブロック数が増えることになるので、当然だろう。表と索引はバッファキャッシュに乗っているため、処理時間にIOは含まれていない数字と理解する必要がある。
SELECTはカラム数が増えるとじわりと処理時間が伸びていることがわかる。SQLレポートのGetsを見る限り、SELECTは4ブロック/件の論理ブロック読み込みをしていた。索引に3ブロック(BLEVEL=2)、表に1ブロックの読み込みをしていると推測される。通常の索引はBLEVEL=3位になっていることが多いので、少し軽めな索引アクセスと理解する必要がある。
INSERTとUPDATEはほぼおなじ程度の処理単価となっている。カラム増加に対する処理時間の傾向もほぼ同じである。SQLレポートのGetsを見る限り、INSERTは5ブロック/件、UPDATEは6ブロック/件の論理ブロック読み込みをしていた。このうち4ブロックは更新ブロックの特定に使われているはずなので、残りが純粋な更新に必要なUNDO関連のブロック数と思われる。カラム増加による処理時間の伸びが顕著なのは、dbms_randomの呼び出し回数の影響が大きいかもしれない。純粋なDMLの処理時間を確認する意味では、関数を含めない方が良かったかもしれない。
DELETEはカラム数の増加の影響はかなり限定的に見える。SQLレポートのGetsを見る限り、8ブロック/件の論理ブロック読み込みをしていた。これも4ブロックは更新ブロックの特定に使われているはずで、残りがDELETEに必要なUNDO関連のブロックなのだろう。更新よりブロック数が多いのは、索引の更新に伴うものかもしれない（削除に伴い、索引のB*Treeおよびリーフブロックの更新が必要）。
単位時間あたりのトランザクション数（tps）については、総じてSQLの性能は、DMLより一桁は速い。INSERTやUPDATEで、b10tblで18倍、b100tblで40倍もの差がある。DELETEは比較的軽いが、それでも10倍程度の差がある。
実際のシステムにおいては、一定の比率で参照、更新トランザクションが発生するので、上記を基礎数値として1コアあたりにさばけるトランザクション数が導き出せる。例えば、b100tblのモデルでselectが80%、insertが10%、updateが7%、deleteが3%の比率とすると、196tps/コアとなる。このような考え方で、必要な単位時間あたりのトランザクションに応じて、コア数を見積もることができる。実際に使うコアのスペックが異なる場合は、ここからspec int等のベンチマーク結果から、コア性能を考慮した見積もりを作れば良いだろう。
７．まとめ
DBサーバのCPUの見積もりについて、モデル（データ、処理モデル）から、OCIで実測した結果をもとに基礎数値を求めて、コア数を見積もる考え方について述べた。また、測定結果について、SQL、DMLの性能の傾向、カラム数（平均行長）に対する性能の傾向について考察した。
性能測定結果は環境によって変わるので、あくまで参考程度にしかならないとしても、実際の業務処理ロジックに近いモデルを作ることで、見積もりの精度を上げることができるだろう。
ありがたいことに、OCIのようなパブリッククラウドが使えるようになり、実機による測定ができる環境を実に簡単に準備できるようになった。いくばくかの利用料は発生するかもしれないが、コア数を比較的精度高く見積もることは、Oracleライセンス費の適正化につながるので、サイジング用にOCI使ったとしても十分にその価値はあるのではないかと感じる。
◆参考
[1]測定結果詳細：20240211_性能テスト結果_v2.xlsx
2024-02-11 14:03
