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(*)
----------
   1000000

ROWID順に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(*)
----------
      1006

SAMPLE句は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(*)
----------
      1005

SAMPLE句で取り出した行（カラム）を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文1
1
2
3
4
5
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      20000

PK列がNUMBER型でなくても更新できる

今までの例ではPK列（ID）がNUMBER型であったので、mod関数の引数としてそのまま指定できました。

ということで、PK列が暗黙的にでも数値に変換できない値の場合であっても使えるように工夫してみたのが以下のUPDATE2文です。

UPDATE文2
1
2
3
4
5
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の使用をおすすめします。

それでは