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

概述

本文将介绍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,333333333111111111,222222222,333333333111111111,222222222,333333333111111111,222222222,333333333111111111,222222222,333333333111111111,222222222,333333333111111111,222222222,333333333111111111,222222222,333333333111111111,222222222,333333333111111111,222222222,333333333

测试1：

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

OPTIONS(ROWS=10,BINDSIZE=600,READSIZE=200)LOAD DATAINFILE 'test.csv'BADFILE 'test.bad'INSERTINTO TABLE TEST_LDRFIELDS 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.logSQL*Loader: Release 12.1.0.2.0 - Production on Sun Jun 26 21:00:51 2016Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.Path used:      ConventionalCommit point reached - logical record count 1Commit point reached - logical record count 2Commit point reached - logical record count 3Commit point reached - logical record count 4Commit point reached - logical record count 5Commit point reached - logical record count 6Commit point reached - logical record count 7Commit point reached - logical record count 8Commit point reached - logical record count 9Commit point reached - logical record count 10　★一共做了10次提交（commit）Table TEST_LDR:  10 Rows successfully loaded.Check the log file:  test1.logfor more information about the load.

测试1执行结果日志：

SQL*Loader: Release 12.1.0.2.0 - Production on Sun Jun 26 21:00:51 2016Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.Control File:   test1.ctlData File:      test.csv  Bad File:     test.bad  Discard File:  none specified (Allow all discards)Number to load: ALLNumber to skip: 0Errors allowed: 50Bind array:     10 rows, maximum of 600 bytes★Continuation:    none specifiedPath used:      ConventionalTable TEST_LDR, loaded from every logical record.Insert option in effect for this table: INSERTTRAILING 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:          0Total logical records read:            10Total logical records rejected:         0Total logical records discarded:        0Run began on Sun Jun 26 21:00:51 2016Run ended on Sun Jun 26 21:00:51 2016Elapsed time was:     00:00:00.09CPU 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 DATAINFILE 'test.csv'BADFILE 'test.bad'INSERTINTO TABLE TEST_LDRFIELDS 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.logSQL*Loader: Release 12.1.0.2.0 - Production on Sun Jun 26 21:10:31 2016Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.Path used:      ConventionalCommit point reached - logical record count 2Commit point reached - logical record count 4Commit point reached - logical record count 6Commit point reached - logical record count 8Commit point reached - logical record count 10★一共做了5次提交（commit）Table TEST_LDR:  10 Rows successfully loaded.Check the log file:  test2.logfor more information about the load.

测试2执行结果日志：

SQL*Loader: Release 12.1.0.2.0 - Production on Sun Jun 26 21:10:31 2016Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.Control File:   test2.ctlData File:      test.csv  Bad File:     test.bad  Discard File:  none specified (Allow all discards)Number to load: ALLNumber to skip: 0Errors allowed: 50Bind array:     10 rows, maximum of 1600 bytes★Continuation:    none specifiedPath used:      ConventionalTable TEST_LDR, loaded from every logical record.Insert option in effect for this table: INSERTTRAILING 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:          0Total logical records read:            10Total logical records rejected:         0Total logical records discarded:        0Run began on Sun Jun 26 21:10:31 2016Run ended on Sun Jun 26 21:10:31 2016Elapsed time was:     00:00:00.12CPU 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 DATAINFILE 'test.csv'BADFILE 'test.bad'INSERTINTO TABLE TEST_LDRFIELDS 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.logSQL*Loader: Release 12.1.0.2.0 - Production on Sun Jun 26 21:18:43 2016Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.Path used:      Conventionalspecified value for readsize(200) less than bindsize(6000)Commit point reached - logical record count 3Commit point reached - logical record count 6Commit point reached - logical record count 9Commit point reached - logical record count 10★一共做了4次提交（commit）Table TEST_LDR:  10 Rows successfully loaded.Check the log file:  test3.logfor more information about the load.

测试3执行结果日志：

SQL*Loader: Release 12.1.0.2.0 - Production on Sun Jun 26 21:18:43 2016Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.Control File:   test3.ctlData File:      test.csv  Bad File:     test.bad  Discard File:  none specified (Allow all discards)Number to load: ALLNumber to skip: 0Errors allowed: 50Bind array:     3 rows, maximum of 6000 bytes★①Continuation:    none specifiedPath used:      ConventionalTable TEST_LDR, loaded from every logical record.Insert option in effect for this table: INSERTTRAILING 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:          0Total logical records read:            10Total logical records rejected:         0Total logical records discarded:        0Run began on Sun Jun 26 21:18:43 2016Run ended on Sun Jun 26 21:18:43 2016Elapsed time was:     00:00:00.08CPU time was:         00:00:00.02

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