【Oracle Database】 SQL*LOADER検証①

シバタツ先生の記事の記載で勘違いしていた点があるので、勉強も兼ねて検証

[シバタツ流！ DWHチューニングの極意 第2回　データ・ローディング](https://www.oracle.com/technetwork/jp/database/articles/pickup/index-1622018-ja.html)
>Oracle Real Application Clusters (Oracle RAC) を使用していても、SQL*Loaderは１つのノード上でしかパラレルに実行できません。

この記載を当初は「RAC環境において、LOADファイル分割を行ったとしても、SQLLDRコマンドは1ノードでしか実行できない」と解釈してしまった（拡大解釈しすぎた）
1ノードで複数LOADERプロセスを起動できるけど、それはインターノード・パラレルにはならないよ、と記載しているだけと理解した


##1.とりあえずSQL*LOADERを試す（従来型）
###準備

・検証表作成

```
SQL> create table load_tbl(id number primary key, name varchar(200));

Table created.
```

・LOADERで取り込む外部表作成

```csv:load_data.csv
1,YOSHI01
2,YOSHI02
3,YOSHI03
4,YOSHI04
5,YOSHI05
　︙
```

・制御ファイル作成

```ctl:load.ctl
LOAD DATA
INFILE '/tmp/load_data.csv'
INTO TABLE LOAD_TBL
APPEND
FIELDS TERMINATED BY ','
(ID,NAME)
```

###実行

```
[10:29:24 oracle@test1 work]$ sqlldr userid=unoki/unoki, control=load.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 10:29:45 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
Commit point reached - logical record count 320
Commit point reached - logical record count 384
Commit point reached - logical record count 448
Commit point reached - logical record count 512
Commit point reached - logical record count 576
Commit point reached - logical record count 640
Commit point reached - logical record count 704
Commit point reached - logical record count 768
Commit point reached - logical record count 832
Commit point reached - logical record count 896
Commit point reached - logical record count 960
Commit point reached - logical record count 1024
Commit point reached - logical record count 1088
Commit point reached - logical record count 1152
Commit point reached - logical record count 1216
Commit point reached - logical record count 1280
Commit point reached - logical record count 1344
Commit point reached - logical record count 1408
Commit point reached - logical record count 1472
Commit point reached - logical record count 1536
Commit point reached - logical record count 1600
Commit point reached - logical record count 1664
Commit point reached - logical record count 1728
Commit point reached - logical record count 1792
Commit point reached - logical record count 1856
Commit point reached - logical record count 1920
Commit point reached - logical record count 1984
Commit point reached - logical record count 2048
Commit point reached - logical record count 2112
Commit point reached - logical record count 2176
Commit point reached - logical record count 2240
Commit point reached - logical record count 2304
Commit point reached - logical record count 2368
Commit point reached - logical record count 2432
Commit point reached - logical record count 2496
Commit point reached - logical record count 2560
Commit point reached - logical record count 2624
Commit point reached - logical record count 2688
Commit point reached - logical record count 2752
Commit point reached - logical record count 2816
Commit point reached - logical record count 2880
Commit point reached - logical record count 2944
Commit point reached - logical record count 3008
Commit point reached - logical record count 3072
Commit point reached - logical record count 3136
Commit point reached - logical record count 3200
Commit point reached - logical record count 3264
Commit point reached - logical record count 3328
Commit point reached - logical record count 3392
Commit point reached - logical record count 3456
Commit point reached - logical record count 3520
Commit point reached - logical record count 3584
Commit point reached - logical record count 3648
Commit point reached - logical record count 3712
Commit point reached - logical record count 3776
Commit point reached - logical record count 3840
Commit point reached - logical record count 3904
Commit point reached - logical record count 3968
Commit point reached - logical record count 4032
Commit point reached - logical record count 4096
Commit point reached - logical record count 4160
Commit point reached - logical record count 4224
Commit point reached - logical record count 4288
Commit point reached - logical record count 4352
Commit point reached - logical record count 4416
Commit point reached - logical record count 4480
Commit point reached - logical record count 4544
Commit point reached - logical record count 4608
Commit point reached - logical record count 4672
Commit point reached - logical record count 4736
Commit point reached - logical record count 4800
Commit point reached - logical record count 4864
Commit point reached - logical record count 4928
Commit point reached - logical record count 4992
Commit point reached - logical record count 5056
Commit point reached - logical record count 5120
Commit point reached - logical record count 5184
Commit point reached - logical record count 5248
Commit point reached - logical record count 5312
Commit point reached - logical record count 5376
Commit point reached - logical record count 5440
Commit point reached - logical record count 5504
Commit point reached - logical record count 5568
Commit point reached - logical record count 5632
Commit point reached - logical record count 5696
Commit point reached - logical record count 5760
Commit point reached - logical record count 5824
Commit point reached - logical record count 5888
Commit point reached - logical record count 5952
Commit point reached - logical record count 6016
Commit point reached - logical record count 6080
Commit point reached - logical record count 6144
Commit point reached - logical record count 6208
Commit point reached - logical record count 6272
Commit point reached - logical record count 6336
Commit point reached - logical record count 6400
Commit point reached - logical record count 6464
Commit point reached - logical record count 6528
Commit point reached - logical record count 6592
Commit point reached - logical record count 6656
Commit point reached - logical record count 6720
Commit point reached - logical record count 6784
Commit point reached - logical record count 6848
Commit point reached - logical record count 6912
Commit point reached - logical record count 6976
Commit point reached - logical record count 7040
Commit point reached - logical record count 7104
Commit point reached - logical record count 7168
Commit point reached - logical record count 7232
Commit point reached - logical record count 7296
Commit point reached - logical record count 7360
Commit point reached - logical record count 7424
Commit point reached - logical record count 7488
Commit point reached - logical record count 7552
Commit point reached - logical record count 7616
Commit point reached - logical record count 7680
Commit point reached - logical record count 7744
Commit point reached - logical record count 7808
Commit point reached - logical record count 7872
Commit point reached - logical record count 7936
Commit point reached - logical record count 8000
Commit point reached - logical record count 8064
Commit point reached - logical record count 8128
Commit point reached - logical record count 8192
Commit point reached - logical record count 8256
Commit point reached - logical record count 8320
Commit point reached - logical record count 8384
Commit point reached - logical record count 8448
Commit point reached - logical record count 8512
Commit point reached - logical record count 8576
Commit point reached - logical record count 8640
Commit point reached - logical record count 8704
Commit point reached - logical record count 8768
Commit point reached - logical record count 8832
Commit point reached - logical record count 8896
Commit point reached - logical record count 8960
Commit point reached - logical record count 9024
Commit point reached - logical record count 9088
Commit point reached - logical record count 9152
Commit point reached - logical record count 9216
Commit point reached - logical record count 9280
Commit point reached - logical record count 9344
Commit point reached - logical record count 9408
Commit point reached - logical record count 9472
Commit point reached - logical record count 9536
Commit point reached - logical record count 9600
Commit point reached - logical record count 9664
Commit point reached - logical record count 9728
Commit point reached - logical record count 9792
Commit point reached - logical record count 9856
Commit point reached - logical record count 9920
Commit point reached - logical record count 9984
Commit point reached - logical record count 10000

Table LOAD_TBL:
  9999 Rows successfully loaded.

Check the log file:
  load.log
for more information about the load.
```

なんかエラーでて1行だけはいらなかった

ログを確認

```
SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 10:29:45 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Control File:   load.ctl
Data File:      /tmp/load_data.csv
  Bad File:     load_data.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table LOAD_TBL, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID                                  FIRST     *   ,       CHARACTER
NAME                                 NEXT     *   ,       CHARACTER

Record 1: Rejected - Error on table LOAD_TBL, column ID.
ORA-01722: invalid number


Table LOAD_TBL:
  9999 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  33024 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:         10000
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Tue Mar 26 10:29:45 2019
Run ended on Tue Mar 26 10:29:48 2019

Elapsed time was:     00:00:02.69
CPU time was:         00:00:00.08
```

Total logical records rejected:1 になっている

BADファイルを確認したら
>1,YOSHI01

が入っていなかった
先頭に改行を入れたら正常に行けた、WHY

```
[11:30:10 oracle@test1 work]$ sqlldr userid=unoki/unoki, control=load.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 10:46:44 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 65
Commit point reached - logical record count 129
Commit point reached - logical record count 193
Commit point reached - logical record count 257
Commit point reached - logical record count 321
Commit point reached - logical record count 385
Commit point reached - logical record count 449
Commit point reached - logical record count 513
Commit point reached - logical record count 577
Commit point reached - logical record count 641
Commit point reached - logical record count 705
Commit point reached - logical record count 769
Commit point reached - logical record count 833
Commit point reached - logical record count 897
Commit point reached - logical record count 961
Commit point reached - logical record count 1025
Commit point reached - logical record count 1089
Commit point reached - logical record count 1153
Commit point reached - logical record count 1217
Commit point reached - logical record count 1281
Commit point reached - logical record count 1345
Commit point reached - logical record count 1409
Commit point reached - logical record count 1473
Commit point reached - logical record count 1537
Commit point reached - logical record count 1601
Commit point reached - logical record count 1665
Commit point reached - logical record count 1729
Commit point reached - logical record count 1793
Commit point reached - logical record count 1857
Commit point reached - logical record count 1921
Commit point reached - logical record count 1985
Commit point reached - logical record count 2049
Commit point reached - logical record count 2113
Commit point reached - logical record count 2177
Commit point reached - logical record count 2241
Commit point reached - logical record count 2305
Commit point reached - logical record count 2369
Commit point reached - logical record count 2433
Commit point reached - logical record count 2497
Commit point reached - logical record count 2561
Commit point reached - logical record count 2625
Commit point reached - logical record count 2689
Commit point reached - logical record count 2753
Commit point reached - logical record count 2817
Commit point reached - logical record count 2881
Commit point reached - logical record count 2945
Commit point reached - logical record count 3009
Commit point reached - logical record count 3073
Commit point reached - logical record count 3137
Commit point reached - logical record count 3201
Commit point reached - logical record count 3265
Commit point reached - logical record count 3329
Commit point reached - logical record count 3393
Commit point reached - logical record count 3457
Commit point reached - logical record count 3521
Commit point reached - logical record count 3585
Commit point reached - logical record count 3649
Commit point reached - logical record count 3713
Commit point reached - logical record count 3777
Commit point reached - logical record count 3841
Commit point reached - logical record count 3905
Commit point reached - logical record count 3969
Commit point reached - logical record count 4033
Commit point reached - logical record count 4097
Commit point reached - logical record count 4161
Commit point reached - logical record count 4225
Commit point reached - logical record count 4289
Commit point reached - logical record count 4353
Commit point reached - logical record count 4417
Commit point reached - logical record count 4481
Commit point reached - logical record count 4545
Commit point reached - logical record count 4609
Commit point reached - logical record count 4673
Commit point reached - logical record count 4737
Commit point reached - logical record count 4801
Commit point reached - logical record count 4865
Commit point reached - logical record count 4929
Commit point reached - logical record count 4993
Commit point reached - logical record count 5057
Commit point reached - logical record count 5121
Commit point reached - logical record count 5185
Commit point reached - logical record count 5249
Commit point reached - logical record count 5313
Commit point reached - logical record count 5377
Commit point reached - logical record count 5441
Commit point reached - logical record count 5505
Commit point reached - logical record count 5569
Commit point reached - logical record count 5633
Commit point reached - logical record count 5697
Commit point reached - logical record count 5761
Commit point reached - logical record count 5825
Commit point reached - logical record count 5889
Commit point reached - logical record count 5953
Commit point reached - logical record count 6017
Commit point reached - logical record count 6081
Commit point reached - logical record count 6145
Commit point reached - logical record count 6209
Commit point reached - logical record count 6273
Commit point reached - logical record count 6337
Commit point reached - logical record count 6401
Commit point reached - logical record count 6465
Commit point reached - logical record count 6529
Commit point reached - logical record count 6593
Commit point reached - logical record count 6657
Commit point reached - logical record count 6721
Commit point reached - logical record count 6785
Commit point reached - logical record count 6849
Commit point reached - logical record count 6913
Commit point reached - logical record count 6977
Commit point reached - logical record count 7041
Commit point reached - logical record count 7105
Commit point reached - logical record count 7169
Commit point reached - logical record count 7233
Commit point reached - logical record count 7297
Commit point reached - logical record count 7361
Commit point reached - logical record count 7425
Commit point reached - logical record count 7489
Commit point reached - logical record count 7553
Commit point reached - logical record count 7617
Commit point reached - logical record count 7681
Commit point reached - logical record count 7745
Commit point reached - logical record count 7809
Commit point reached - logical record count 7873
Commit point reached - logical record count 7937
Commit point reached - logical record count 8001
Commit point reached - logical record count 8065
Commit point reached - logical record count 8129
Commit point reached - logical record count 8193
Commit point reached - logical record count 8257
Commit point reached - logical record count 8321
Commit point reached - logical record count 8385
Commit point reached - logical record count 8449
Commit point reached - logical record count 8513
Commit point reached - logical record count 8577
Commit point reached - logical record count 8641
Commit point reached - logical record count 8705
Commit point reached - logical record count 8769
Commit point reached - logical record count 8833
Commit point reached - logical record count 8897
Commit point reached - logical record count 8961
Commit point reached - logical record count 9025
Commit point reached - logical record count 9089
Commit point reached - logical record count 9153
Commit point reached - logical record count 9217
Commit point reached - logical record count 9281
Commit point reached - logical record count 9345
Commit point reached - logical record count 9409
Commit point reached - logical record count 9473
Commit point reached - logical record count 9537
Commit point reached - logical record count 9601
Commit point reached - logical record count 9665
Commit point reached - logical record count 9729
Commit point reached - logical record count 9793
Commit point reached - logical record count 9857
Commit point reached - logical record count 9921
Commit point reached - logical record count 9985
Commit point reached - logical record count 10001

Table LOAD_TBL:
  10000 Rows successfully loaded.

Check the log file:
  load.log
for more information about the load.
```

###確認

```
SQL> select count(*) from load_tbl;

  COUNT(*)
----------
     10000
```

##2.ファイル分割して2ノードから1テーブルに対してLOAD(parallelオプション無し、従来型)

###準備
ファイルを5000件で2分割する
・load_data_5000_1.csv
・load_data_5000_2.csv

制御ファイルはこんな感じ

・ノード1

```ctl:load.ctl
LOAD DATA
INFILE '/tmp/load_data_5000_1.csv'
INTO TABLE LOAD_TBL
APPEND
FIELDS TERMINATED BY ','
(ID,NAME)
```

・ノード2

```ctl:load.ctl
LOAD DATA
INFILE '/tmp/load_data_5000_2.csv'
INTO TABLE LOAD_TBL
APPEND
FIELDS TERMINATED BY ','
(ID,NAME)
```

###実行
正常終了した(5000件で分割ミスした 恥)

・ノード1

```
[11:39:27 oracle@test1 work]$ sqlldr userid=unoki/unoki, control=load.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 11:09:53 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 65
Commit point reached - logical record count 129
Commit point reached - logical record count 193
Commit point reached - logical record count 257
Commit point reached - logical record count 321
Commit point reached - logical record count 385
Commit point reached - logical record count 449
Commit point reached - logical record count 513
Commit point reached - logical record count 577
Commit point reached - logical record count 641
Commit point reached - logical record count 705
Commit point reached - logical record count 769
Commit point reached - logical record count 833
Commit point reached - logical record count 897
Commit point reached - logical record count 961
Commit point reached - logical record count 1025
Commit point reached - logical record count 1089
Commit point reached - logical record count 1153
Commit point reached - logical record count 1217
Commit point reached - logical record count 1281
Commit point reached - logical record count 1345
Commit point reached - logical record count 1409
Commit point reached - logical record count 1473
Commit point reached - logical record count 1537
Commit point reached - logical record count 1601
Commit point reached - logical record count 1665
Commit point reached - logical record count 1729
Commit point reached - logical record count 1793
Commit point reached - logical record count 1857
Commit point reached - logical record count 1921
Commit point reached - logical record count 1985
Commit point reached - logical record count 2049
Commit point reached - logical record count 2113
Commit point reached - logical record count 2177
Commit point reached - logical record count 2241
Commit point reached - logical record count 2305
Commit point reached - logical record count 2369
Commit point reached - logical record count 2433
Commit point reached - logical record count 2497
Commit point reached - logical record count 2561
Commit point reached - logical record count 2625
Commit point reached - logical record count 2689
Commit point reached - logical record count 2753
Commit point reached - logical record count 2817
Commit point reached - logical record count 2881
Commit point reached - logical record count 2945
Commit point reached - logical record count 3009
Commit point reached - logical record count 3073
Commit point reached - logical record count 3137
Commit point reached - logical record count 3201
Commit point reached - logical record count 3265
Commit point reached - logical record count 3329
Commit point reached - logical record count 3393
Commit point reached - logical record count 3457
Commit point reached - logical record count 3521
Commit point reached - logical record count 3585
Commit point reached - logical record count 3649
Commit point reached - logical record count 3713
Commit point reached - logical record count 3777
Commit point reached - logical record count 3841
Commit point reached - logical record count 3905
Commit point reached - logical record count 3969
Commit point reached - logical record count 4033
Commit point reached - logical record count 4097
Commit point reached - logical record count 4161
Commit point reached - logical record count 4225
Commit point reached - logical record count 4289
Commit point reached - logical record count 4353
Commit point reached - logical record count 4417
Commit point reached - logical record count 4481
Commit point reached - logical record count 4545
Commit point reached - logical record count 4609
Commit point reached - logical record count 4673
Commit point reached - logical record count 4737
Commit point reached - logical record count 4801
Commit point reached - logical record count 4865
Commit point reached - logical record count 4929
Commit point reached - logical record count 4993
Commit point reached - logical record count 5000

Table LOAD_TBL:
  4999 Rows successfully loaded.

Check the log file:
  load.log
for more information about the load.
```

・ノード2

```
[11:39:27 oracle@test2 work]$ sqlldr userid=unoki/unoki, control=load.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 11:35:28 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 65
Commit point reached - logical record count 129
Commit point reached - logical record count 193
Commit point reached - logical record count 257
Commit point reached - logical record count 321
Commit point reached - logical record count 385
Commit point reached - logical record count 449
Commit point reached - logical record count 513
Commit point reached - logical record count 577
Commit point reached - logical record count 641
Commit point reached - logical record count 705
Commit point reached - logical record count 769
Commit point reached - logical record count 833
Commit point reached - logical record count 897
Commit point reached - logical record count 961
Commit point reached - logical record count 1025
Commit point reached - logical record count 1089
Commit point reached - logical record count 1153
Commit point reached - logical record count 1217
Commit point reached - logical record count 1281
Commit point reached - logical record count 1345
Commit point reached - logical record count 1409
Commit point reached - logical record count 1473
Commit point reached - logical record count 1537
Commit point reached - logical record count 1601
Commit point reached - logical record count 1665
Commit point reached - logical record count 1729
Commit point reached - logical record count 1793
Commit point reached - logical record count 1857
Commit point reached - logical record count 1921
Commit point reached - logical record count 1985
Commit point reached - logical record count 2049
Commit point reached - logical record count 2113
Commit point reached - logical record count 2177
Commit point reached - logical record count 2241
Commit point reached - logical record count 2305
Commit point reached - logical record count 2369
Commit point reached - logical record count 2433
Commit point reached - logical record count 2497
Commit point reached - logical record count 2561
Commit point reached - logical record count 2625
Commit point reached - logical record count 2689
Commit point reached - logical record count 2753
Commit point reached - logical record count 2817
Commit point reached - logical record count 2881
Commit point reached - logical record count 2945
Commit point reached - logical record count 3009
Commit point reached - logical record count 3073
Commit point reached - logical record count 3137
Commit point reached - logical record count 3201
Commit point reached - logical record count 3265
Commit point reached - logical record count 3329
Commit point reached - logical record count 3393
Commit point reached - logical record count 3457
Commit point reached - logical record count 3521
Commit point reached - logical record count 3585
Commit point reached - logical record count 3649
Commit point reached - logical record count 3713
Commit point reached - logical record count 3777
Commit point reached - logical record count 3841
Commit point reached - logical record count 3905
Commit point reached - logical record count 3969
Commit point reached - logical record count 4033
Commit point reached - logical record count 4097
Commit point reached - logical record count 4161
Commit point reached - logical record count 4225
Commit point reached - logical record count 4289
Commit point reached - logical record count 4353
Commit point reached - logical record count 4417
Commit point reached - logical record count 4481
Commit point reached - logical record count 4545
Commit point reached - logical record count 4609
Commit point reached - logical record count 4673
Commit point reached - logical record count 4737
Commit point reached - logical record count 4801
Commit point reached - logical record count 4865
Commit point reached - logical record count 4929
Commit point reached - logical record count 4993
Commit point reached - logical record count 5002

Table LOAD_TBL:
  5001 Rows successfully loaded.

Check the log file:
  load.log
for more information about the load.
```

###確認
```
SQL> select count(*) from load_tbl;

  COUNT(*)
----------
     10000
```

##3.ファイル分割して2ノードから1テーブルに対してLOAD(parallelオプション無し、ダイレクト)
ではダイレクトではどうか
ダイレクトオプションを付ける

###実行
・ノード1

```
[11:09:55 oracle@test1 work]$ sqlldr userid=unoki/unoki, control=load.ctl direct=true

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 11:15:50 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 5000.

Table LOAD_TBL:
  4999 Rows successfully loaded.

Check the log file:
  load.log
for more information about the load.
```

・ノード2

```
[11:39:27 oracle@test2 work]$ sqlldr userid=unoki/unoki, control=load.ctl direct=true

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 11:41:24 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 5002.

Table LOAD_TBL:
  5001 Rows successfully loaded.

Check the log file:
  load.log
for more information about the load.
```

正常終了した

###確認
```
SQL> select count(*) from load_tbl;

  COUNT(*)
----------
     10000
```

正しく入っている

##4.ファイル分割して2ノードから1テーブルに対してLOAD(parallelオプションあり、ダイレクト)
最後に4ファイルに分割してparallelオプションつけて2ノード×2プロセスで実行してみる

###準備
ファイルを2500件で4分割する
・load_data_2500_1.csv
・load_data_2500_2.csv
・load_data_2500_3.csv
・load_data_2500_4.csv

制御ファイルはこんな感じ

・ノード1

```ctl:load_1.ctl
LOAD DATA
INFILE '/tmp/load_data_2500_1.csv'
INTO TABLE LOAD_TBL
APPEND
FIELDS TERMINATED BY ','
(ID,NAME)
```
```ctl:load_2.ctl
LOAD DATA
INFILE '/tmp/load_data_2500_2.csv'
INTO TABLE LOAD_TBL
APPEND
FIELDS TERMINATED BY ','
(ID,NAME)
```

・ノード2

```ctl:load_1.ctl
LOAD DATA
INFILE '/tmp/load_data_2500_3.csv'
INTO TABLE LOAD_TBL
APPEND
FIELDS TERMINATED BY ','
(ID,NAME)
```
```ctl:load_2.ctl
LOAD DATA
INFILE '/tmp/load_data_2500_4.csv'
INTO TABLE LOAD_TBL
APPEND
FIELDS TERMINATED BY ','
(ID,NAME)
```

###実行
実行しようとしたらエラーが出た

・ノード１

```
[11:26:29 oracle@test1 work]$ sqlldr userid=unoki/unoki, control=load_1.ct
l direct=true parallel=true & sqlldr userid=unoki/unoki, control=load_2.ctl dire
ct=true parallel=true
[1] 9981


SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 11:28:53 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 11:28:53 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct
Path used:      Direct
SQL*Loader-951: Error calling once/load initialization
SQL*Loader-951: ORA-26002: Table UNOKI.LOAD_TBL has index defined upon it.
Error calling once/load initialization
ORA-26002: Table UNOKI.LOAD_TBL has index defined upon it.
```

・ノード2

```
[11:52:35 oracle@test2 work]$ sqlldr userid=unoki/unoki, control=load_1.ctl direct=true parallel=true & sqlldr userid=unoki/unoki, control=load_2.ctl direct=true parallel=true
[1] 9650

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 11:54:28 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.


SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 11:54:28 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct
Path used:      Direct
SQL*Loader-951: SQL*Loader-951: Error calling once/load initialization
Error calling once/load initialization
ORA-26002: Table UNOKI.LOAD_TBL has index defined upon it.
ORA-26002: Table UNOKI.LOAD_TBL has index defined upon it.
[1]+  Exit 1                  sqlldr userid=unoki/unoki, control=load_1.ctl direct=true parallel=true
```

索引使えないから無効化しないといけない
[OracleR Databaseユーティリティ 12cリリース1 (12.1.0.2) パラレル・ダイレクト・パス・ロードの制限](https://docs.oracle.com/cd/E57425_01/121/SUTIL/GUID-E017C707-5C79-4B4D-A5BA-1FCF403BC24B.htm)
>パラレル・ダイレクト・パス・ロードには次の制限があります。
    ローカル索引もグローバル索引もロードによってメンテナンスできません。
    行は追加(APPEND)のみできます。REPLACE、TRUNCATEおよびINSERTは使用できません(これは、個別のロードの間には整合性がないためです)。パラレル・ロードの前に表を切り捨てる必要がある場合は、手動で行ってください。

今回はプライマリキーをDROPした

```
SQL> alter table load_tbl drop constraint SYS_C0012217;

Table altered.
```

いざ

・ノード1

```
[14:04:25 oracle@tscserver12 work]$ sqlldr userid=unoki/unoki, control=load_1.ctl direct=true parallel=true & sqlldr userid=unoki/unoki, control=load_2.ctl direct=true parallel=true
[1] 6200


SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 14:04:30 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 14:04:30 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct
Path used:      Direct

Load completed - logical record count 2501.

Load completed - logical record count 2501.

Table LOAD_TBL:
  2500 Rows successfully loaded.

Check the log file:
  load_2.log
for more information about the load.
[14:04:31 oracle@tscserver12 work]$
Table LOAD_TBL:
  2500 Rows successfully loaded.

Check the log file:
  load_1.log
for more information about the load.

[1]+  Exit 2                  sqlldr userid=unoki/unoki, control=load_1.ctl direct=true parallel=true
```

・ノード2

```
[14:29:49 oracle@tscserver13 work]$ sqlldr userid=unoki/unoki, control=load_1.ctl direct=true parallel=true & sqlldr userid=unoki/unoki, control=load_2.ctl direct=true parallel=true
[1] 21224


SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 14:30:04 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 14:30:04 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct
Path used:      Direct

Load completed - logical record count 2501.

Load completed - logical record count 2501.

Table LOAD_TBL:
  2500 Rows successfully loaded.

Check the log file:
  load_1.log
for more information about the load.

Table LOAD_TBL:
  2500 Rows successfully loaded.

Check the log file:
  load_2.log
for more information about the load.
[1]+  Exit 2                  sqlldr userid=unoki/unoki, control=load_1.ctl direct=true parallel=true
```

(以下、更新予定)

##そもそも同一表に対してノード間パラレルを実行して意味があるのか
[意外と知らない？！データローディングの基礎](https://www.oracle.com/technetwork/jp/ondemand/database/db-basic/20110720-loader-439860-ja.pdf)
![WS000000.png](https://qiita-image-store.s3.amazonaws.com/0/60220/e7f3d658-5759-93ac-43e5-0b8fe687f46e.png)

表ロックを獲得すると書いている

100万件ロードで検証してみる

###準備
CSV作成

```
[15:38:01 oracle@test1 tmp]$ for i in `seq 1 1000000`
> do
> echo "$i,UNOKI" >> load_data_1000000.csv
> done
```
※このあと先頭に改行を入れた

```
[15:40:18 oracle@test1 tmp]$ wc load_data_100000.csv
 100001  100000 1188896 load_data_100000.csv
```

下記のSQLを定期実行する

```sql:lock.sql
select s.sid, s.serial#,s.username,o.object_name from v$session s, v$locked_object l, dba_objects o where s.sid = l.session_id and l.object_id = o.object_id;
exit;
```

定期実行
・セッションB

```
[15:45:09 oracle@test1 tmp]$ watch -n 1 sqlplus -s unoki/unoki @lock.sql
```

テーブルロックがきちんと検知できるか確認

・セッションA

```
SQL> lock table load_tbl in exclusive mode;

Table(s) Locked.
```

・セッションB

```
       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
        88      52189 UNOKI
LOAD_TBL
```

できてそう
KILLする

・セッションC

```
SQL> alter system kill session '88,2205';

System altered.

SQL> select s.sid, s.serial#,s.username,o.object_name from v$session s, v$locked_object l, dba_objects o where s.sid = l.session_id and l.object_id = o.object_id;

       SID    SERIAL# USERNAME             OBJECT_NAME
---------- ---------- -------------------- --------------------
        88      52189 UNOKI                LOAD_TBL

SQL> alter system kill session '88,52189';

System altered.
```

・セッションB

```
no rows selected
```


###実行/確認
では実行
・セッションA

```
[15:56:37 oracle@test1 work]$ sqlldr userid=unoki/unoki, control=load_1000000.ctl direct=true

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 15:57:44 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 1000001.

Table LOAD_TBL:
  1000000 Rows successfully loaded.

Check the log file:
  load_1000000.log
for more information about the load.
```

・セッションB

```
       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
       173      10425 UNOKI
LOAD_TBL
```

一瞬だったが表ロックを取った

parallelオプションを付けたらどうなるか

・セッションA

```
[16:02:29 oracle@test1 work]$ sqlldr userid=unoki/unoki, control=load_1000000.ctl direct=true parallel=true

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Mar 26 16:02:37 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 1000001.

Table LOAD_TBL:
  1000000 Rows successfully loaded.

Check the log file:
  load_1000000.log
for more information about the load.
```

・セッションB

```
       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
       173      10425 UNOKI
LOAD_TBL
```

ファイル分割してノード分けても表ロックであまり意味はないんだろうなあ

今回は力尽きたのでここまでにするけど、次回は200万件を100万件分割して2ノードからローディングして、ASHを見た結果を記載する