【Oracle Database】SQL*LOADER検証②

我ながらマークダウンが下手で見にくい

本日は同一表に対してSQL*LOADERを2ノードから並列実行するとActive Session Historyに表ロックによる待機が発生するのか検証してみる

別の検証をしたいので少しおおざっぱに記載する

##1ノードから1000万件実行した場合のActive Session History確認

###準備
・1000万件と200万件のロードデータを作る

```csv:load_data_10000000.csv
[14:09:42 oracle@test1 tmp]$ for i in `seq 1 10000000`
> do
> echo "$i,UNOKI" >> load_data_10000000.csv
> done
```


```csv:load_data_2000000.csv
[14:09:54 oracle@test2 tmp]$ for i in `seq 1 2000000`
> do
> echo "$i,UNOKI" >> load_data_2000000.csv
> done
```

・制御ファイル作成

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

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

###実行

```
[14:35:14 oracle@test1 work]$ sqlldr userid=unoki/unoki, control=load_10000000.ctl direct=true

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Apr 1 14:35:30 2019

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

Path used:      Direct

Load completed - logical record count 10000001.

Table LOAD_TBL:
  10000000 Rows successfully loaded.

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

```
[14:35:14 oracle@test2 work]$ sqlldr userid=unoki/unoki, control=load_2000000.ctl direct=true parallel=true

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Apr 2 13:53:30 2019

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

Path used:      Direct

Load completed - logical record count 2000000.

Table LOAD_TBL:
  2000000 Rows successfully loaded.

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

###確認
・ASHから待機確認

```
SQL> select INST_ID,SESSION_ID,SQL_ID,MODULE,EVENT from gv$active_session_history where SAMPLE_TIME between TO_DATE('2019/04/02 13:00:00','yyyy/mm/dd hh24:mi:ss') and TO_DATE('2019/04/02 14:00:00','yyyy/mm/dd hh24:mi:ss') and module='SQL Loader Direct Path Load';

   INST_ID SESSION_ID SQL_ID        MODULE                                                           EVENT
---------- ---------- ------------- ---------------------------------------------------------------- ----------------------------------------------------------------
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file switch (checkpoint incomplete)
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file switch (checkpoint incomplete)
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file switch (checkpoint incomplete)
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file switch (checkpoint incomplete)
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      gc current block busy
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      gc current block busy
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      gc current block busy
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      gc current block busy
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      gc current block busy
         1         90 34kqkysj49sx4 SQL Loader Direct Path Load                                      gc current block busy
         1         90 bjstq78pprd24 SQL Loader Direct Path Load
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      gc current block busy
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      reliable message
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      reliable message
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      gc current block busy
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      gc current block busy
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      log file sync
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write
         2        331 34kqkysj49sx4 SQL Loader Direct Path Load                                      direct path write

91 rows selected.
```

うーんTM系は出ないので表ロックによる競合はしてなさそう

一応マニュアルにも、それっぽいことは書いていた

[従来型パス・ロード](https://docs.oracle.com/cd/E57425_01/121/SUTIL/GUID-495C502D-4A1F-47B3-9E4D-F6F997EDAC4C.htm#GUID-A8911483-65C5-4348-886C-1C607CACA486)
>ダイレクト・パス・ロード(パラレル・ロードは除く)を使用するには、SQL*Loaderに、表への排他的書込み権限と、すべての索引への排他的読取り権限および書込み権限が必要です。

ダイレクト・パス・ロードは排他的読み取り・書込権限が必要
⇒ パラレル・ロードは除く
⇒ よってパラレル・ロードは排他的表ロックは取らない（で、いいのか）

ざっくり書きすぎたので後で補足する（かも）