知识&案例：SQL*Loader的执行性能--Conventional Path 篇
知识&案例：SQL*Loader的执行性能--Conventional Path 篇
“工以利器为助”**Keyword: **
SQL*Loader
Performance
OLTP compression
Conventional Path
ROWS、BINDSIZE、READSIZE概述
本章将通过某案例介绍在使用SQL*Loader工具时的性能相关知识和提高执行效率的方法。
从一个案例开始
某客户想要把包含2亿条数据的CSV文件（大概60GB），通过SQLLoader装载到该公司的Exadata DB的环境上，但是发现执行时间需要近4个小时。 咨询是否能够提高SQLLoader的执行效率？
因为是生产环境，由于其业务和环境的限制不能使用直接路径（Direct Path）只能通过常规路径（Conventional Path）执行SQL*Loader。 并且提供了一个参考的数据：在其服务器上，通过OS命令单纯拷贝文件的速率大概是1GB/s。
从何着手？
面对这个问题，我们该从何着手开始调查？ 通过SQL*Loader的工具进行常规路径（Conventional Path）的数据装载虽然和OS命令单纯拷贝文件的速率会有很大的差距，但是在这个环境上4个小时装载60GB的数据，是否是正常现象？ 为了进一步调查，我们假设这是一个故障解决型的问题，从现象和数据收集入手一步一步进行分析和寻找方法。
澄清问题和核实问题（IC &IV）
首先我们要明确问题并通过收集信息来证明存在的问题和状况。 为了查看执行状况，我们要求用户提供执行用的SQL*Loader配置文件、执行日志以及执行过程中的AWR报告和SQLTRACE。
信息收集(Data Collection)
1.首先我们查看SQL*Loader配置文件如下：
OPTIONS(SKIP=100,ERRORS=100,ROWS=100000,
BINDSIZE=1900000000,READSIZE=2000000000)
LOAD DATA
INFILE 'DATA.csv'
BADFILE 'DATA.bad'
INSERT
INTO TABLE SCHEMANAME.TABLENAME
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
col1 "TO_DATE(:CMN_CRT_DT,'YYYY/MM/DD HH24:MI:SS')",
col2,
col3,
col4 "TO_DATE(:CMN_UPD_DT,'YYYY/MM/DD HH24:MI:SS')",
...
col60
)我们可以看到配置文件中，SQL*Loader是通过常规路径（Conventional Path）进行的数据装载， 并且OPTIONS中设置了READSIZE、BINDSIZE、ROWS等参数。
2.查看执行日志：
Table SCHEMANAME.TABLENAME:
264610586 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:963743004 bytes(65534 rows)★
Read buffer bytes: 2000000000★Elapsed time was: 03: 50: XX.XX
CPU time was : 01: 15: XX.XX
执行时间近4个小时，CPU时间约为1个多小时。
3.查看SQLTRACE：
SQL ID: xxxxxxxxxxx Plan Hash: 0INSERT INTO SCHEMANAME.TABLENAME (col1,col2,...col60)
VALUES
(TO_DATE(:"col1",'YYYY/MM/DD HH24:MI:SS'),:col2,
...
:col60)call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 64 278.36 279.24 0 335082 1687283 4194176
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 64 278.36 279.24 0 335082 1687283 4194176Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYSElapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net more data from client 168607 0.00 0.98
enq: FB - contention 868 0.00 0.10
gc current multi block request 314 0.00 0.05
SQL*Net message to client 64 0.00 0.00
SQL*Net message from client 64 0.00 0.00
row cache lock 46 0.00 0.00
log file switch completion 3 0.00 0.01因为用户提供的只是部分SQLTRACE，所以我们仅能看到一段时间的部分操作内容。 从SQLTRACE的输出可以看到，常规路径（Conventional Path）进行的数据装载就是执行了Insert文，并且从等待事件上来看，由于【SQL*Net more data from client】稍微影响了一点效率，但并不是主要原因。
4.查看AWR 报告
根据AWR的输出，并没有什么特别的等待或者异常的情况。 但是我们看到在操作过程中的Redo和Undo以及数据文件的写入都很多。 （包含2亿条数据的CSV文件大概60GB，但是Redo产生了大概275.1G，数据文件写了近181.5G）。
理清问题后的调查和探究（Research ）
现在我们对以下的一些问题会产生疑问：
配置文件的参数是否影响效率？
为什么产生了这么多的Redo和Undo以及数据文件的写？配置文件的参数意义
我们看到用户配置文件的参数中可能和性能有关的参数主要包括ROWS、BINDSIZE和READSIZE。
OPTIONS(SKIP=100,ERRORS=100,ROWS=100000,BINDSIZE=1900000000,READSIZE=2000000000)首先，在传统常规路径（Conventional Path）过程中，SQLLoader是利用SQL INSERT文和绑定数组缓冲区（bind array buffer）进行数据加载的。 绑定数组缓冲区（bind array buffer）是SQLLoader用于缓存加载数据的内存区域。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 Bytes10=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）根据上面的介绍，我们在回顾本次案例中的日志，我们可以看到缓冲区（BINDSIZE）和行数（ROWS）设置的值较大，但是由于ROWS的最大值为65534 ，所以一次提交的最大行数为65534 行； 并且READSIZE也极其接近其在Linux平台上的最大值，因此ROWS、BINDSIZE和READSIZE参数的设置并没有什么问题。
SQL*Loader log:
Space allocated for bind array:963743004 bytes(65534 rows)★
Read buffer bytes: 2000000000★过多Redo和Undo的产生原因
下面我们的关注点放在为什么产生了那么多的Redo和Undo？ 通过WR的输出，我们看到在操作过程中的Redo和Undo以及数据文件的写入都很多。（包含2亿条数据的CSV文件大概60GB，但是Redo产生了大概275.1G，数据文件写了近181.5G ）。 但是AWR和和SQL Trace都没有并没有什么特别的等待或者异常的情况，因此很难定位原因。
是不是对象表的索引或者构造上有什么问题呢？ 因此我们查看该表的表定义，并无索引并且该表进行了OLTP压缩。
简化后的表定义：CREATE TABLE SCHEMANAME.TABLENAME
(
COL1 VARCHAR2(15),
COL2 VARCHAR2(10),
....
COL60 VARCHAR2(5)
)
COMPRESS FOR OLTP;★也许和OLTP压缩有关？让我们查看一下OLTP压缩相关的知识。
OLTP压缩(OLTP Compression)
OLTP压缩(OLTP Compression)功能是从Oracle 11g版本开始推出的一种高级行压缩技术。通过OLTP压缩功能，可以在普通的DML操作过程中也可以对数据进行压缩，提高系统的性能。
和案例中的表定义一样，可以通过指定COMPRESS属性来使用压缩功能。
COMPRESS属性
※关于OLTP压缩(OLTP Compression)属性的设定语法，在Oracle的各个版本上略有不同，但向后兼容，并且12.1的版本上也可以使用11.1和11.2版本的语法，具体语法如下：
11.1版本上：
CREATE TABLE <table_name> COMPRESS FOR OLTP；11.2版本上：
CREATE TABLE <table_name> COMPRESS FOR ALL OPERATIONS;12.1版本上：
CREATE TABLE <table_name> COMPRESS ROW STORE COMPRESS ADVANCED ;例： （on 12.1 version）
SQL> CREATE TABLE t1 (c1 number) COMPRESS FOR ALL OPERATIONS;
Table created.
SQL> CREATE TABLE t2 (c2 number) COMPRESS FOR OLTP ;
Table created.
SQL> CREATE TABLE t3 (c3 number) ROW STORE COMPRESS ADVANCED ;
Table created.
SQL> col COMPRESS_FOR format a20
SQL> col TABLE_NAME format a20
SQL> select table_name, compression, compress_for from user_tables;TABLE_NAME COMPRESS COMPRESS_FOR
-------------------- -------- --------------------
T3 ENABLED ADVANCED
T2 ENABLED ADVANCED
T1 ENABLED ADVANCED通过3种方法的设置结果是相同的，在12c上都表示为ADVANCED压缩 即OLTP压缩。OLTP压缩的处理逻辑
OLTP压缩并不是以SQL文（如insert、update）为单位进行处理的，而是以块（Block）为单位进行处理。 当DML（insert、update）进行处理时，如果块的使用率超过某临界值*1时，就会触发块内数据的压缩处理。
简单的处理逻辑可以概括如下：
0.某块为可以进行Insert的状态（容量并没有达到PCTFREE）。1.和普通DML处理逻辑一样进行的数据的INSERT或UPDATE。2.当由于INSERT或UPDATE处理导致块的使用率超过某临界值*1时，触发块内数据的压缩处理。
（INSERT或UPDATE的进程进行）3.由于压缩处理，块内再次有了空余空间，正常进行INSERT处理（非压缩）。4.块的使用率再次到达某临界值*1时，触发块内数据的压缩处理。5. 循环步骤3和4。最终效果是块内同时存在着压缩和非压缩的数据。※关于触发压缩处理的临界值*1： 关于这个临界值通常根据块的空余大小、行大小、PCTFREE等因素，由Oracle内部算法算出。
OLTP压缩和Redo、Undo的联系
OLTP压缩时，块在被成功压缩前，为了一致的读取和事务回滚，需要把变更前的块保存到UNDO中。因此，在对OLTP压缩表进行更新和插入时会比非压缩表产生更多的UNDO；而产生UNDO时也会产生REDO，因此，OLTP压缩会导致相对较多的UNDO和REDO.
OLTP压缩详细还可以参考MOS文档：
Master Note for OLTP Compression (Doc ID 1223705.1)通过上面的分析便可以解释为什么产生了那么多的Redo和Undo。
花开月明
为了进一步验证猜测和解决问题，我们需要验证不采取OLTP压缩时的执行状况。 因此我们要求用户分别把表变更为混合列压缩(Exadata Hybrid Columnar Compression 简称EHCC)或非压缩后进行测试。
EHCC压缩:
alter table SCHEMANAME.TABLENAME compess for query low;
非压缩
alter table SCHEMANAME.TABLENAME nocompess;经过测试，我们得到该表的处理结果：
OLTP压缩时的处理效率：每秒1万2千件左右
非压缩时的处理效率：每秒3万5千件左右
EHCC压缩时的处理效率：每秒3万5千件左右
（由于EHCC压缩通常仅对直接路径导入有效，所以考虑EHCC压缩并没有起作用，
因此和非压缩时的处理效率差不多）下图是REDO和UNDO的测试结果：
通过改变表的压缩模式可以提高3倍多的速率，基本是用户可接受的改进。
原因认定和原因认定的理由（CD & CJ）
根据上面的调查结果，我们可以判断本次现象的原因是由于表进行了OLTP压缩，导致过多的REDO和UNDO产生，进而影响SQL*Loader的执行性能。 详细可以参考上文Research 部分。
推荐的解决方案和推荐的理由（PS & PJ）
由于本次案例的特殊性（不能使用直接路径导入），所以推荐的解决方案即更改表的压缩属性，变为 混合列压缩(Exadata Hybrid Columnar Compression 简称EHCC)或非压缩后，在进行数据加载。 如：
EHCC压缩:
alter table SCHEMANAME.TABLENAME compess for query low;
或
非压缩
alter table SCHEMANAME.TABLENAME nocompess;另外，还可以参考以下文在线文档进行一些小参数的调整。（这里不做详细介绍）
Database Utilities
>General Performance Improvement Hints
知识点总结（KM）
通过本次案例， 我们详细描述了解决问题的思路和过程，并介绍了以下的知识点。
ROWS、BINDSIZE和READSIZE参数的作用和意义
OLTP压缩的概述和处理逻辑
OLTP压缩和Redo、Undo的联系参考：
Database Utilities
>Conventional Path Loads
Database Utilities
>General Performance Improvement Hints
Master Note for OLTP Compression (Doc ID 1223705.1)