シバタツ先生の記事の記載で勘違いしていた点があるので、勉強も兼ねて検証
シバタツ流！ DWHチューニングの極意 第2回 データ・ローディング
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で取り込む外部表作成
load_data.csv
1,YOSHI01
2,YOSHI02
3,YOSHI03
4,YOSHI04
5,YOSHI05
︙
・制御ファイル作成
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
load.ctl
LOAD DATA
INFILE '/tmp/load_data_5000_1.csv'
INTO TABLE LOAD_TBL
APPEND
FIELDS TERMINATED BY ','
(ID,NAME)
・ノード2
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
load_1.ctl
LOAD DATA
INFILE '/tmp/load_data_2500_1.csv'
INTO TABLE LOAD_TBL
APPEND
FIELDS TERMINATED BY ','
(ID,NAME)
load_2.ctl
LOAD DATA
INFILE '/tmp/load_data_2500_2.csv'
INTO TABLE LOAD_TBL
APPEND
FIELDS TERMINATED BY ','
(ID,NAME)
・ノード2
load_1.ctl
LOAD DATA
INFILE '/tmp/load_data_2500_3.csv'
INTO TABLE LOAD_TBL
APPEND
FIELDS TERMINATED BY ','
(ID,NAME)
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) パラレル・ダイレクト・パス・ロードの制限
パラレル・ダイレクト・パス・ロードには次の制限があります。
ローカル索引もグローバル索引もロードによってメンテナンスできません。
行は追加(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
(以下、更新予定)
そもそも同一表に対してノード間パラレルを実行して意味があるのか
表ロックを獲得すると書いている
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を定期実行する
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を見た結果を記載する