SQL*Loader设置参数之ROWS、BINDSIZE和READSIZE

SQLplusDB

发布于 2020-03-26 10:15:32

1.2K0

发布于 2020-03-26 10:15:32

概述

本文将介绍SQL*Loader用户配置文件的参数中，传统常规路径（Conventional Path）情况下和性能有关的参数：ROWS、BINDSIZE和READSIZE。

首先，在传统常规路径（Conventional Path）过程中，SQL*Loader是利用SQL INSERT文和绑定数组缓冲区（bind array buffer）进行数据加载的。绑定数组缓冲区（bind array buffer）是SQL*Loader用于缓存加载数据的内存区域。SQL*Loader进行数据加载时，首先会同时读入多行并拷贝到bind array。当bind array满（或者没有其他数据需要加载）时，会进行一次插入工作。绑定数组（bind array ）的大小主要由参数BINDSIZE和 READSIZE进行控制。

Database Utilities >Conventional Path Loads

参数详细

对于ROWS、BINDSIZE和READSIZE参数的作用和意义如下：

ROWS

对于传统常规路径（Conventional Path）导入的情况，代表一次提交（Commit）的行数（默认：6 最大值：65534）。

Database Utilities >ROWS

（※直接路径（Direct Path）情况下有其他作用，这里暂不展开）

BINDSIZE

每次提交记录的缓冲区的最大值（仅适用于传统常规路径加载），默认256000 Bytes。

Database Utilities >BINDSIZE

通过BINDSIZE的设定，要比默认值和通过参数ROWS计算的缓冲区大小更优先。即BINDSIZE能够制约ROWS，如果ROWS提交的数据需要的缓冲区大于BINDSIZE的设定，会以BINDSIZE的设定为准。

例如：

例如下面的每行的大小为30 Bytes：

       COLUMN1  COLUMN2       COLUMN3
     +--------+-------------+-------------------+
[0]  |<10 Byte>| <10 Byte>  | <10 Byte>        |
     +--------+-------------+-------------------+
[1]  |        |             |                   |
     +--------+-------------+-------------------+
...
[10]  |        |             |                   |
     +--------+-------------+-------------------+

当指定参数OPTIONS (BINDSIZE=100, ROWS=10)时。虽然需要的提交记录的缓冲区大小为 3行*行长 = 0 Bytes*10=300 Bytes，但是 BINDSIZE仅为100 Bytes，所以每次会在读入3条（100/30=3.3）数据后就会进行提交。

相反，ROWS也会同样会反过来制约BINDSIZE，即SQL*Loader会在满足 ROWS和BINDSIZE的任意一个条件时，进行数据的提交。

※在设定参数时，一定要同时考虑ROWS和BINDSIZE的设定。

READSIZE

读取缓冲区的大小 (适用于传统常规路径和直接路径加载)，默认 1048576。

READSIZE负责读取的缓冲区大小，而BINDSIZE负责提交的缓冲区大小，如果READSIZE小于BINDSIZE，那么READSIZE会自动增加。

通过设置READSIZE为更大的值，可以在提交前读取更多的数据到Buffer中去。

Database Utilities >READSIZE

BINDSIZE和READSIZE的设定最大值：

Oracle8i < version <=11gR1(或PSR 10.2.0.3): 20M。
11gR1(或PSR 10.2.0.3) < version: 依存于OS平台（如Linux为2147473647 Bytes）

测试例

我们可以通过以下的例子来简单的查看一下ROWS、BINDSIZE、READSIZE的设置对于SQL*Loader执行的影响：

首先我们建立用于测试的测试表：

CREATE TABLE TEST_LDR
(
COL1                     VARCHAR2(10),
COL2                     VARCHAR2(10),
COL3                      VARCHAR2(10)
);

数据文件(test.csv)：

111111111,222222222,333333333
111111111,222222222,333333333
111111111,222222222,333333333
111111111,222222222,333333333
111111111,222222222,333333333
111111111,222222222,333333333
111111111,222222222,333333333
111111111,222222222,333333333
111111111,222222222,333333333
111111111,222222222,333333333

测试1：

设置ROWS=10,BINDSIZE=600,READSIZE=200

OPTIONS(ROWS=10,BINDSIZE=600,READSIZE=200)
LOAD DATA
INFILE 'test.csv'
BADFILE 'test.bad'
INSERT
INTO TABLE TEST_LDR
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
COL1,
COL2,
COL3
)

测试1执行：

SQL> truncate table TEST_LDR;
Table truncated.

SQL> !sqlldr scott/tiger control=test1.ctl log=test1.log

SQL*Loader: Release 12.1.0.2.0 - Production on Sun Jun 26 21:00:51 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 1
Commit point reached - logical record count 2
Commit point reached - logical record count 3
Commit point reached - logical record count 4
Commit point reached - logical record count 5
Commit point reached - logical record count 6
Commit point reached - logical record count 7
Commit point reached - logical record count 8
Commit point reached - logical record count 9
Commit point reached - logical record count 10　★一共做了10次提交（commit）

Table TEST_LDR:
  10 Rows successfully loaded.

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

测试1执行结果日志：

SQL*Loader: Release 12.1.0.2.0 - Production on Sun Jun 26 21:00:51 2016

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

Control File:   test1.ctl
Data File:      test.csv
  Bad File:     test.bad
  Discard File:  none specified

 (Allow all discards)

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

Table TEST_LDR, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL1                                FIRST     *   ,  O(") CHARACTER            
COL2                                 NEXT     *   ,  O(") CHARACTER            
COL3                                 NEXT     *   ,  O(") CHARACTER            

Bind size of 600 bytes increased to 774 bytes to hold 1 row.★①
value used for ROWS parameter changed from 10 to 1★②    
Table TEST_LDR:
  10 Rows successfully loaded.
  0 Rows 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:                    774 bytes(1 rows)★①
Read   buffer bytes:     200★③

Total logical records skipped:          0
Total logical records read:            10
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Sun Jun 26 21:00:51 2016
Run ended on Sun Jun 26 21:00:51 2016

Elapsed time was:     00:00:00.09
CPU time was:         00:00:00.02

动作内容： ①每行数据的大小为774 bytes，但是BINDSIZE设为600 ，所以为了至少容纳下一行数据，BINDSIZE自动调整为774。 ②虽然ROWS=10，每10行数据提交一次，但是由于BINDSIZE的影响，每行都进行了一次提交。 ③Read buffer为200 Bytes

测试2：

设置ROWS=10,BINDSIZE=1600,READSIZE=20000

OPTIONS(ROWS=10,BINDSIZE=1600,READSIZE=20000)
LOAD DATA
INFILE 'test.csv'
BADFILE 'test.bad'
INSERT
INTO TABLE TEST_LDR
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
COL1,
COL2,
COL3
)

测试2执行：

SQL> truncate table TEST_LDR;

Table truncated.

SQL> !sqlldr scott/tiger control=test2.ctl log=test2.log

SQL*Loader: Release 12.1.0.2.0 - Production on Sun Jun 26 21:10:31 2016

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

Path used:      Conventional
Commit point reached - logical record count 2
Commit point reached - logical record count 4
Commit point reached - logical record count 6
Commit point reached - logical record count 8
Commit point reached - logical record count 10★一共做了5次提交（commit）

Table TEST_LDR:
  10 Rows successfully loaded.

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

测试2执行结果日志：

SQL*Loader: Release 12.1.0.2.0 - Production on Sun Jun 26 21:10:31 2016

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

Control File:   test2.ctl
Data File:      test.csv
  Bad File:     test.bad
  Discard File:  none specified

 (Allow all discards)

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

Table TEST_LDR, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL1                                FIRST     *   ,  O(") CHARACTER            
COL2                                 NEXT     *   ,  O(") CHARACTER            
COL3                                 NEXT     *   ,  O(") CHARACTER            

value used for ROWS parameter changed from 10 to 2★①

Table TEST_LDR:
  10 Rows successfully loaded.
  0 Rows 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:                   1548 bytes(2 rows)★①
Read   buffer bytes:   20000★②

Total logical records skipped:          0
Total logical records read:            10
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Sun Jun 26 21:10:31 2016
Run ended on Sun Jun 26 21:10:31 2016

Elapsed time was:     00:00:00.12
CPU time was:         00:00:00.03

动作内容： ①每行数据的大小为774 bytes，BINDSIZE设为1600 ，所以为了可以容纳下2行数据。虽然ROWS=10，每10行数据提交一次，但是由于BINDSIZE的影响，每2行都进行了一次提交。 ②Read buffer为20000Bytes

测试3：

设置ROWS=3,BINDSIZE=6000,READSIZE=200

OPTIONS(ROWS=3,BINDSIZE=6000,READSIZE=200)
LOAD DATA
INFILE 'test.csv'
BADFILE 'test.bad'
INSERT
INTO TABLE TEST_LDR
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
COL1,
COL2,
COL3
)

测试3执行：

SQL> truncate table TEST_LDR;

Table truncated.

SQL> !sqlldr scott/tiger control=test3.ctl log=test3.log

SQL*Loader: Release 12.1.0.2.0 - Production on Sun Jun 26 21:18:43 2016

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

Path used:      Conventional
specified value for readsize(200) less than bindsize(6000)
Commit point reached - logical record count 3
Commit point reached - logical record count 6
Commit point reached - logical record count 9
Commit point reached - logical record count 10★一共做了4次提交（commit）

Table TEST_LDR:
  10 Rows successfully loaded.

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

测试3执行结果日志：

SQL*Loader: Release 12.1.0.2.0 - Production on Sun Jun 26 21:18:43 2016

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

Control File:   test3.ctl
Data File:      test.csv
  Bad File:     test.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     3 rows, maximum of 6000 bytes★①
Continuation:    none specified
Path used:      Conventional

Table TEST_LDR, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL1                                FIRST     *   ,  O(") CHARACTER            
COL2                                 NEXT     *   ,  O(") CHARACTER            
COL3                                 NEXT     *   ,  O(") CHARACTER            


Table TEST_LDR:
  10 Rows successfully loaded.
  0 Rows 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:                   2322 bytes(3 rows)★①
Read   buffer bytes:    6000★②

Total logical records skipped:          0
Total logical records read:            10
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Sun Jun 26 21:18:43 2016
Run ended on Sun Jun 26 21:18:43 2016

Elapsed time was:     00:00:00.08
CPU time was:         00:00:00.02

动作内容： ①每行数据的大小为774 bytes，BINDSIZE设为6000，所以为了可以容纳下7.7行数据。但是由于ROWS=3的影响，每3行数据提交一次，BINDSIZE的设置被调整，每3行都进行了一次提交。 ②由于BINDSIZE的设置的影响，READSIZE（Read buffer）调整为6000Bytes。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2016-06-26，如有侵权请联系 cloudcommunity@tencent.com 删除

腾讯云测试服务