JPOUG Advent Calendar 2019の13日目のエントリーです。
負荷テスト用データは件数が重要
最近、負荷テストの支援をすることが多いのですが、負荷テスト用のデータ準備にはいつも悩まされます。
経験から述べると、アプリケーション開発者にデータの準備をお願いすると大抵はうまく行かないことが多いです。
開発者はプログラムが仕様通り動くかどうかを確認する必要最小限のデータを準備することに関心はありますが、実際の業務を想定した量のデータを準備することはあまり考えていません。
また、本番環境の実データを使うにはセキュリティ上いろいろな制約があるため、機微な情報をマスク化する等の加工に時間がかかりただでさえ厳しいテスト工程を圧迫します。
結論から言うと、負荷テストでは値のリアルさよりも件数の妥当性の方が重要だと思います。
何年か前に参画したプロジェクトでは、アプリケーションの仕様を十分ヒアリングした上で、想定件数のテストデータをほぼゼロから作りそれなりの成果を得ました。しかも、アプリケーション開発工程の影響に左右されず先行的にデータの準備を行ったため、試験工程も余裕を持って実施することができました。
その経験から今回は「カーディナリティを考慮したデータを作成する」というテーマで、当時得たノウハウを共有したいと思います。
カーディナリティとは
「カーディナリティ」という用語をマニュアルでひもとくと「表の行数に対する個別値の数の比率。100万行を持つ表内で個別値が2つしかない列の場合、カーディナリティは低くなります。」とあります。
Oracle® Database データベース概要 19c 用語集
世の中的には「カーディナリティ」を異なる意味で使う場合もあるようですが、今回はOracleのマニュアルにある定義を使います。
参考：「カーディナリティて何ですの」
さきほど、負荷テストデータは値のリアルさは重要でないと書きましたが、検索カラムのカーディナリティは実データから値の割合を調査しそれを基にテストデータを作成するのがよいと思います。
例えば、HR.EMPLYEES表から職種別構成比を表示させると以下のようになります。
SQL> set pages 50
SQL> col COMP_RATIO for 990.9
SQL> select
2 JOB_TITLE
3 ,COMP_RATIO
4 from
5 (
6 select
7 JOB_ID
8 ,count(*) / TOTAL * 100 COMP_RATIO
9 ,TOTAL
10 from
11 (
12 select
13 JOB_ID
14 ,count(*) OVER() TOTAL
15 from
16 EMPLOYEES
17 )
18 group by
19 JOB_ID
20 ,TOTAL
21 ) R
22 inner join
23 JOBS J
24 on R.JOB_ID = J.JOB_ID
25 order by
26 2 desc
27 ,1
28 ;
JOB_TITLE COMP_RATIO
----------------------------------- ----------
Sales Representative 28.0
Shipping Clerk 18.7
Stock Clerk 18.7
Accountant 4.7
Programmer 4.7
Purchasing Clerk 4.7
Sales Manager 4.7
Stock Manager 4.7
Administration Vice President 1.9
Accounting Manager 0.9
Administration Assistant 0.9
Finance Manager 0.9
Human Resources Representative 0.9
Marketing Manager 0.9
Marketing Representative 0.9
President 0.9
Public Accountant 0.9
Public Relations Representative 0.9
Purchasing Manager 0.9
19行が選択されました。「販売代理人は全社員の28.0%」、「出荷担当者は18.7%」というような実際の構成比を把握しておけば、EMPLYEES表にどんな大きな件数のテストデータを作成したとしても現実的な比率のJOB_IDを作成することができます。
負荷テスト用データ作成要領の概要
負荷テストデータを作成するには、ExcelでCSVを作成してSQL*Loaderでロードする等いろいろな方法が考えられますが、今回はテーブルを作成した後に、PL/SQLによってPK項目を生成しながら、PK項目以外のカラムに初期値（0やnull）をセットして、必要な行数のデータを作成するものとします。
その後、カラムごとに指定した割合で値を更新していくこととします。
検証用テーブルと初期データの作成
それでは、検証用のテーブルを作成し初期データを作成してみます。
PK項目のID列と任意の値が入るVAL1列、VAL2列を定義し、初期データとして100万件のデータをバルク・インサートで作成します。（話を簡単にするためすべてのカラムはNUMBER型とします。）
件数はループ変数の定義により任意に設定できるのであまり悩むことはないと思います。
SQL> create table TEST (
2 ID number
3 ,VAL1 number
4 ,VAL2 number
5 );
表が作成されました。
SQL> declare
2 type TBL_INS is table of TEST%rowtype index by pls_integer;
3 W_INS TBL_INS;
4 begin
5 for i in 1..1000000 loop
6 W_INS(i).ID := i;
7 W_INS(i).VAL1 := 0;
8 W_INS(i).VAL2 := 0;
9 end loop;
10 forall i in 1..1000000 insert into TEST values W_INS(i);
11 commit;
12 end;
13 /
PL/SQLプロシージャが正常に完了しました。
SQL> select count(*) from TEST;
COUNT(*)
----------
1000000ROWID順に100行取り出してみる
念のため、ROWID順に先頭の100件を取り出してみます。ID=1から始まらないのがおもしろいところですが、ループ処理で配列へデータを順に作成しているので、同じブロックの中ではROWIDとIDの並び順は同じです。
VAL1とVAL2列は0で初期化されています。
SQL> select * from (
2 select ROWID,ID,VAL1,VAL2 from TEST order by ROWID)
3 where rownum <= 100;
ROWID ID VAL1 VAL2
------------------ -------- ---------- ----------
AAAXhJAAGAAAADjAAA 567 0 0
AAAXhJAAGAAAADjAAB 568 0 0
AAAXhJAAGAAAADjAAC 569 0 0
AAAXhJAAGAAAADjAAD 570 0 0
AAAXhJAAGAAAADjAAE 571 0 0
AAAXhJAAGAAAADjAAF 572 0 0
AAAXhJAAGAAAADjAAG 573 0 0
AAAXhJAAGAAAADjAAH 574 0 0
AAAXhJAAGAAAADjAAI 575 0 0
AAAXhJAAGAAAADjAAJ 576 0 0
.................................................
AAAXhJAAGAAAADjABa 657 0 0
AAAXhJAAGAAAADjABb 658 0 0
AAAXhJAAGAAAADjABc 659 0 0
AAAXhJAAGAAAADjABd 660 0 0
AAAXhJAAGAAAADjABe 661 0 0
AAAXhJAAGAAAADjABf 662 0 0
AAAXhJAAGAAAADjABg 663 0 0
AAAXhJAAGAAAADjABh 664 0 0
AAAXhJAAGAAAADjABi 665 0 0
AAAXhJAAGAAAADjABj 666 0 0
100行が選択されました。このTEST表のVAL1、VAL2に対し、全体（100万件）のうち指定した割合で値を更新していきます。
SAMPLE句で指定した%の行を取り出す
割合を指定して行を取り出すにはSAMPLE句の使用が考えられます。
以下は、SAMPLE句の説明の抜粋です。
sample_percent サンプルの選択: 例 参照
「sample_percentには、全体の行またはブロック数のうち、サンプルに入れる割合(%)を指定します。
0.000001以上100未満の範囲の値を指定します。この割合は、各行(ブロック・サンプリングの場合は行の各クラスタ)が、サンプルの一部として選択される可能性を示します。
sample_percentに指定した割合の行がtableから正確に取り出されるわけではありません。」
とりあえず0.1%（1000行になるはず）の行を取り出してみます。
SQL> select ROWID,ID from TEST sample(0.1) order by ROWID;
ROWID ID
------------------ ----------
AAAXhJAAGAAAADjAAa 593
AAAXhJAAGAAAADmADd 2465
AAAXhJAAGAAAADoAEb 4204
AAAXhJAAGAAAADpABw 4592
AAAXhJAAGAAAADpAGs 4908
AAAXhJAAGAAAADpAIm 5030
AAAXhJAAGAAAADqAC7 5226
AAAXhJAAGAAAADrABB 5663
AAAXhJAAGAAAADrAEv 5901
AAAXhJAAGAAAADsADX 6372
.............................
AAAXhJAAGAAAAirAGU 988784
AAAXhJAAGAAAAiuABe 980689
AAAXhJAAGAAAAiyACM 981254
AAAXhJAAGAAAAi0AEa 998004
AAAXhJAAGAAAAi0AFQ 998058
AAAXhJAAGAAAAi6AAS 982170
AAAXhJAAGAAAAi8AEQ 999032
AAAXhJAAGAAAAi+ABq 982777
AAAXhJAAGAAAAi+AGR 983072
AAAXhJAAGAAAAi/AEd 991260
982行が選択されました。SAMPLE句は正確な行数を取り出さない
SAMPLE句では内部的に乱数を使ってサンプルを取得しているようなので、以下のように取得の度に行数が異なります。指定する%の精度を高くしても（小数点以下の桁数を大きくしても）この傾向は変わりません。しかも、けっこう誤差が大きいです。
SQL> select count(*) from (
2 select ROWID,ID from TEST sample(0.100000) order by ROWID
3 );
COUNT(*)
----------
994
経過: 00:00:00.01
SQL> /
COUNT(*)
----------
996
経過: 00:00:00.01
SQL> /
COUNT(*)
----------
898
経過: 00:00:00.01
SQL> /
COUNT(*)
----------
1006SAMPLE句はSEEDを指定すれば同じ結果を返す（ただし行数は不正確）
ただし、乱数を発生させている（と思われる）ので、以下のようにシードを指定すると同じ結果になります。ただし、行数は所望の1000行とはなりません。
SQL> select count(*) from (
2 select ROWID,ID from TEST sample(0.100000) seed(1) order by ROWID
3 );
COUNT(*)
----------
980
経過: 00:00:00.01
SQL> /
COUNT(*)
----------
980
経過: 00:00:00.00
SQL> select count(*) from (
2 select ROWID,ID from TEST sample(0.100000) seed(10) order by ROWID
3 );
COUNT(*)
----------
1014
経過: 00:00:00.00
SQL> /
COUNT(*)
----------
1014
経過: 00:00:00.00
SQL> select count(*) from (
2 select ROWID,ID from TEST sample(0.100000) seed(100) order by ROWID
3 );
COUNT(*)
----------
1005
経過: 00:00:00.01
SQL> /
COUNT(*)
----------
1005SAMPLE句で取り出した行（カラム）をUpdateしてみる
正確に1000行ではないですが、取り出したIDを使ってVAL1列を0→1に更新してみます。
ところがどっこい、ORA-30560エラーが発生してしまいます。
SQL> update TEST set VAL1 = 1 where ID in (select ID from TEST sample(0.100000));
update TEST set VAL1 = 1 where ID in (select ID from TEST sample(0.100000))
*
行1でエラーが発生しました。:
ORA-30560: SAMPLE句は使用できません
SQL> update TEST set VAL1 = 1 where ID in (select ID from TEST sample(0.100000) seed(1));
update TEST set VAL1 = 1 where ID in (select ID from TEST sample(0.100000) seed(1))
*
行1でエラーが発生しました。:
ORA-30560: SAMPLE句は使用できません乱数でランダムに行（ROWID）を指定しているので、更新行が特定できず、つまり何度やっても同じ結果にならないので、更新できないのでしょうか？
シードを指定してもエラーになりましたので、SAMPLE句が使えないのはOracleの仕様なんでしょう。
SAMPLE句の制限事項
実は、マニュアルをよく読むと「DMLの副問い合わせの中ではSAMPLE句を指定できない」という記述があります。
- SAMPLE句は、DML文の副問合せの中では指定できません。
- SAMPLE句を問合せで指定できるのは、問合せの対象が実表、マテリアライズド・ビューのコンテナ表、またはキー保存であるビューである場合です。
正攻法で考えてみる
SAMPLE句のような変化球ではなく、正攻法で考えてみます。（少し、前振りが長すぎました。）
結論から書きますが、全体行のうち更新したい行数の割合を「更新率」とすると（例えば100万行のうち1000行更新したければ「更新率=0.1%」）、以下のUPDATE文1により更新が可能です。
update 更新TBL set 更新列 = 更新値 where PK列 in ( select PK列 from ( select PK列,mod(PK列, (1 / 更新率)) from 更新TBL) where rownum ＜= 全体件数 * 更新率 );
つまり、このUPDATE文を実行するためには、予め全体件数を（SELECT COUNT(*) FROM ～）で調べておく必要があります。
それでは、TEST表のVAL1列を更新率0.1%で0→1に更新してみましょう。
SQL> update TEST set VAL1 = 1 where ID in (
2 select ID from (
3 select ID,mod(ID, (100 / 0.1)) from TEST) -- 更新率（0.1%）の逆数
4 where rownum <= 1000000 * (0.1 / 100) -- 全体件数 * 更新率 = 更新件数
5 );
1000行が更新されました。
SQL> commit;
コミットが完了しました。
SQL> select VAL1,count(*) from TEST group by VAL1 order by VAL1;
VAL1 COUNT(*)
---------- ----------
0 999000
1 1000別の値の更新も自由自在
次に同じVAL1列を更新率2%で０→2に更新してみましょう。
注意したいのは先ほど１に更新した行は更新してはいけないことです。
SQL> update TEST set VAL1 = 2 where ID in (
2 select ID from (
3 select ID,mod(ID, (100 / 2)) from TEST where VAL1 != 1 and VAL1 = 0) -- 更新率（2%）の逆数
4 where rownum <= 1000000 * (2 / 100) -- 全体件数 * 更新率 = 更新件数
5 );
20000行が更新されました。SQL> select VAL1,count(*) from TEST group by VAL1 order by VAL1;
VAL1 COUNT(*)
---------- ----------
0 979000
1 1000
2 20000所望の結果となりました。
別カラムの更新
次に、VAL2列も更新してみます。
以下のUPDATE文では、VAL1列の値とは無関係にVAL2を更新率18.7%で更新しています。
SQL> update TEST set VAL2 = 1 where ID in (
2 select ID from (
3 select ID,mod(ID, (100 / 18.7)) from TEST) -- 更新率（18.7%）の逆数
4 where rownum <= 1000000 * (18.7 / 100) -- 全体件数 * 更新率 = 更新件数
5 );
187000行が更新されました。
SQL> commit;
コミットが完了しました。
SQL> select VAL2,count(*) from TEST group by VAL2 order by VAL2;
VAL2 COUNT(*)
---------- ----------
0 813000
1 187000
SQL> select VAL1,VAL2,count(*) from TEST group by VAL1,VAL2 order by VAL1,VAL2;
VAL1 VAL2 COUNT(*)
---------- ---------- ----------
0 0 813000
0 1 166000
1 1 1000
2 1 20000PK列がNUMBER型でなくても更新できる
今までの例ではPK列（ID）がNUMBER型であったので、mod関数の引数としてそのまま指定できました。
ということで、PK列が暗黙的にでも数値に変換できない値の場合であっても使えるように工夫してみたのが以下のUPDATE2文です。
update 更新TBL set 更新列 = 更新値 where PK列 in ( select PK列 from ( select PK列,mod(row_number() over(order by PK列), (1 / 更新率)) from 更新TBL) where rownum ＜= 全体件数 * 更新率 );
それでは、別の100万行のテーブルTEST2に対して、指定した更新率（28%）でのUPDATEを行ってみます。
SQL> select count(*) from TEST2;
COUNT(*)
----------
1000000
1行が選択されました。
SQL> select * from TEST2 where rownum<11;
ID VAL1
---------- ----------
ABC0000404 0
ABC0000405 0
ABC0000406 0
ABC0000407 0
ABC0000408 0
ABC0000409 0
ABC0000410 0
ABC0000411 0
ABC0000412 0
ABC0000413 0
10行が選択されました。SQL> update TEST2 set VAL1 = 1 where ID in (
2 select ID from (
3 select ID,mod(row_number() over(order by ID), (100 / 28.0)) from TEST2) -- 更新率（28.0%）の逆数
4 where rownum <= 1000000 * (28.0 / 100) -- 全体件数 * 更新率 = 更新件数
5 );
280000行が更新されました。
SQL> commit;
コミットが完了しました。
SQL> select VAL1,count(*) from TEST2 group by VAL1 order by VAL1;
VAL1 COUNT(*)
---------- ----------
0 720000
1 280000
2行が選択されました。PK列がNUMBER型であっても、飛び番が発生していたりすると期待した結果とならない可能性があるので、どんな場合でもUPDATE文2の使用をおすすめします。
それでは