LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@ch0c0bana0

【Oracle Database】SQL*LOADER検証②

Last updated at Posted at 2019-04-02

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

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

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

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

準備

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

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
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

・制御ファイル作成

load_10000000.ctl
LOAD DATA
INFILE '/tmp/load_data_10000000.csv'
INTO TABLE LOAD_TBL
APPEND
FIELDS TERMINATED BY ','
(ID,NAME)
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系は出ないので表ロックによる競合はしてなさそう

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

従来型パス・ロード

ダイレクト・パス・ロード(パラレル・ロードは除く)を使用するには、SQL*Loaderに、表への排他的書込み権限と、すべての索引への排他的読取り権限および書込み権限が必要です。

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

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

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?