“工以利器为助”
**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的数据,是否是正常现象? 为了进一步调查,我们假设这是一个故障解决型的问题,从现象和数据收集入手一步一步进行分析和寻找方法。
首先我们要明确问题并通过收集信息来证明存在的问题和状况。 为了查看执行状况,我们要求用户提供执行用的SQL*Loader配置文件、执行日志以及执行过程中的AWR报告和SQLTRACE。
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)。
现在我们对以下的一些问题会产生疑问:
配置文件的参数是否影响效率?
为什么产生了这么多的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参数的作用和意义如下:
对于传统常规路径(Conventional Path)导入的情况,代表一次提交(Commit)的行数(默认:6 最大值:65534)。
Database Utilities
>ROWS
(※直接路径(Direct Path)情况下有其他作用,这里暂不展开)
每次提交记录的缓冲区的最大值(仅适用于传统常规路径加载),默认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的设定。
读取缓冲区的大小 (适用于传统常规路径和直接路径加载),默认 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? 通过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)功能是从Oracle 11g版本开始推出的一种高级行压缩技术。通过OLTP压缩功能,可以在普通的DML操作过程中也可以对数据进行压缩,提高系统的性能。
和案例中的表定义一样,可以通过指定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压缩并不是以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压缩时,块在被成功压缩前,为了一致的读取和事务回滚,需要把变更前的块保存到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倍多的速率,基本是用户可接受的改进。
根据上面的调查结果,我们可以判断本次现象的原因是由于表进行了OLTP压缩,导致过多的REDO和UNDO产生,进而影响SQL*Loader的执行性能。 详细可以参考上文Research 部分。
由于本次案例的特殊性(不能使用直接路径导入),所以推荐的解决方案即更改表的压缩属性,变为 混合列压缩(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
通过本次案例, 我们详细描述了解决问题的思路和过程,并介绍了以下的知识点。
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)