优化sqlldr加载上100万条记录的数据！

最新推荐文章于 2023-01-20 09:00:00 发布

事后诸葛亮

最新推荐文章于 2023-01-20 09:00:00 发布

阅读量1.1w

点赞数 2

分类专栏：数据加载&卸载文章标签：优化 oracle sql linux object date

本文链接：https://blog.csdn.net/zq9017197/article/details/7352627

版权

数据加载&卸载专栏收录该内容

11 篇文章 1 订阅

订阅专栏

1、生成百万级数据文件

[oracle@linux sqlldr]$ pwd
/u01/sqlldr
[oracle@linux sqlldr]$ ll
总计 4
-rw-r--r-- 1 oracle oinstall 228 03-14 18:55 getdata.sql
[oracle@linux sqlldr]$ cat getdata.sql                          --创建一个sql脚本。
SELECT A.OWNER || ',"' || A.OBJECT_NAME || '",' || A.OBJECT_ID || ',' ||
       TO_CHAR(A.CREATED, 'yyyy-mm-dd hh24:mi:ss') || ',' || A.STATUS
  FROM DBA_OBJECTS A,
       (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM <= 30) B;
[oracle@linux sqlldr]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 3月 14 18:55:27 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn /as sysdba
已连接。
SQL> select count(*) from dba_objects;

  COUNT(*)
----------
     71876

SQL> set echo off
SQL> set term off
SQL> set feedback off
SQL> set heading off
SQL> spool /u01/sqlldr/data.dat
SQL> @/u01/sqlldr/getdata.sql
SQL> spool off
SQL> set heading on
SQL> set feedback on
SQL> set term on
SQL> set echo on

可以看见dba_objects这个表有7万多条记录，将其与一个30跳记录的表做笛卡尔运算就就可以生成一个200多万跳记录了。

[oracle@linux sqlldr]$ ll
总计 170904
-rw-r--r-- 1 oracle oinstall 174824593 03-14 18:58 data.dat
-rw-r--r-- 1 oracle oinstall       228 03-14 18:55 getdata.sql
[oracle@linux sqlldr]$ wc -l data.dat 
2322150 data.dat

可以看见data.dat这个文件有160多MB，而且这个文件有230多万行。

2、初始化环境

SQL> conn u1/u1
已连接。
SQL> create table t1(
  2  owner varchar2(255),
  3  object_name varchar2(255),
  4  object_id number,
  5  status varchar2(255),
  6  created date);

表已创建。

SQL> create index ind_obj_owner_name on t1(owner,object_name);

索引已创建。

3、创建控制文件

[oracle@linux sqlldr]$ cat sqlldr.ctl 
LOAD DATA  
INFILE data.dat  
TRUNCATE INTO TABLE T1  
FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"'  
(owner,  
 object_name,
 object_id,
 created date 'yyyy-mm-dd hh24:mi:ss',
 status)

4、执行导入

$ sqlldr u1/u1 control=sqlldr.ctl errors=999999

因为data.dat里面有很多的空行，所以把errors设置得大一点。截取sqlldr.log日志文件最后一部分：

表 T1:
  2156280 行 加载成功。
  由于数据错误, 165870 行 没有加载。
  由于所有 WHEN 子句失败, 0 行 没有加载。
  由于所有字段都为空的, 0 行 没有加载。


为绑定数组分配的空间:                 82560 字节 (64 行)    --rows是64，使用了82560字节的bindsize。
读取   缓冲区字节数: 1048576

跳过的逻辑记录总数:          0
读取的逻辑记录总数:       2322150
拒绝的逻辑记录总数:     165870
废弃的逻辑记录总数:        0

从 星期三 3月  14 20:02:25 2012 开始运行
在 星期三 3月  14 20:13:05 2012 处运行结束

经过时间为: 00: 06: 44.54
CPU 时间为: 00: 00: 38.14

5、能不能快一点呢

$ sqlldr u1/u1 control=sqlldr.ctl errors=999999 rows=640

常规路径导入时默认一次加载64行，可能有点小了。现在给为640行。截取sqlldr.log日志文件最后一部分：

表 T1:
  2156280 行 加载成功。
  由于数据错误, 165870 行 没有加载。
  由于所有 WHEN 子句失败, 0 行 没有加载。
  由于所有字段都为空的, 0 行 没有加载。


为绑定数组分配的空间:                255420 字节 (198 行)
读取   缓冲区字节数: 1048576

跳过的逻辑记录总数:          0
读取的逻辑记录总数:       2322150
拒绝的逻辑记录总数:     165870
废弃的逻辑记录总数:        0

从 星期三 3月  14 20:20:53 2012 开始运行
在 星期三 3月  14 20:29:42 2012 处运行结束

经过时间为: 00: 05: 11.45            --可以看见这里还是提高了点时间。
CPU 时间为: 00: 00: 16.64

由于640行所占用空间已经超出了参数bindsize的默认值，因此rows自动修改为198行。这说明bindsize偏小，我们将bindsize改为10M（10*1024*1024=10485760）,同时将rows提高到5000行。

$ sqlldr u1/u1 control=sqlldr.ctl errors=999999 rows=5000 bindsize=10485760

截取sqlldr.log日志文件最后一部分：

表 T1:
  2156280 行 加载成功。
  由于数据错误, 165870 行 没有加载。
  由于所有 WHEN 子句失败, 0 行 没有加载。
  由于所有字段都为空的, 0 行 没有加载。


为绑定数组分配的空间:               6450000 字节 (5000 行)     --可以看见这里的bindsize才使用了6M左右的空间。
读取   缓冲区字节数:10485760

跳过的逻辑记录总数:          0
读取的逻辑记录总数:       2322150
拒绝的逻辑记录总数:     165870
废弃的逻辑记录总数:        0

从 星期三 3月  14 20:36:25 2012 开始运行
在 星期三 3月  14 20:46:51 2012 处运行结束

经过时间为: 00: 04: 43.84         --看这里，时间又提高了。
CPU 时间为: 00: 00: 12.27

6、能不能再快一点呢

前面的测试都是基于常规路径加载，下面使用直接路径加载，所有的参数都默认：

$ sqlldr u1/u1 control=sqlldr.ctl errors=999999 direct=true

截取sqlldr.log日志文件最后一部分：

表 T1:
  2156280 行 加载成功。
  由于数据错误, 165870 行 没有加载。
  由于所有 WHEN 子句失败, 0 行 没有加载。
  由于所有字段都为空的, 0 行 没有加载。

  日期高速缓存:
   最大大小:      1000
   条目数:       926
   命中数    :   2155354
   未命中数  :         0

在直接路径中没有使用绑定数组大小。
列数组  行数:    5000
流缓冲区字节数:  256000
读取   缓冲区字节数: 1048576

跳过的逻辑记录总数:          0
读取的逻辑记录总数:       2322150
拒绝的逻辑记录总数:     165870
废弃的逻辑记录总数:        0
由 SQL*Loader 主线程加载的流缓冲区总数:      500
由 SQL*Loader 加载线程加载的流缓冲区总数:      334

从 星期三 3月  14 21:12:45 2012 开始运行
在 星期三 3月  14 21:16:05 2012 处运行结束

经过时间为: 00: 02: 16.57                --可以看见这次时间提升还是比较多的。
CPU 时间为: 00: 00: 10.11

直接路径加载的参数也有不少，这里先设置参数，后面再详细介绍每个参数的含义。

$ sqlldr u1/u1 control=sqlldr.ctl errors=999999 direct=true streamsize=10485760 date_cache=5000

截取sqlldr.log日志文件最后一部分：

表 T1:
  2156280 行 加载成功。
  由于数据错误, 165870 行 没有加载。
  由于所有 WHEN 子句失败, 0 行 没有加载。
  由于所有字段都为空的, 0 行 没有加载。

  日期高速缓存:
   最大大小:      5000              --可以看见日日缓存是5000
   条目数:       926                --从这里看日期的唯一性比较低，使用默认值1000也就够了。
   命中数    :   2155354
   未命中数  :         0

在直接路径中没有使用绑定数组大小。
列数组  行数:    5000
流缓冲区字节数:10485760
读取   缓冲区字节数: 1048576

跳过的逻辑记录总数:          0
读取的逻辑记录总数:       2322150
拒绝的逻辑记录总数:     165870
废弃的逻辑记录总数:        0
由 SQL*Loader 主线程加载的流缓冲区总数:      500
由 SQL*Loader 加载线程加载的流缓冲区总数:        0

从 星期三 3月  14 21:20:36 2012 开始运行
在 星期三 3月  14 21:22:22 2012 处运行结束

经过时间为: 00: 01: 15.51                    --时间又提高了。
CPU 时间为: 00: 00: 09.97

其实影响性能的因素还有很多，下面列举一些还能降低性能的因素：

a、如果控制文件中全部指定数据类型，并且改为定长格式，导入效率也能提升一些。

b、再比如说表上面有索引，把索引禁用掉，效率又可以提升。

c、还有就是表改为nologging也可以提升效率。