MySQL, Oracle, Linux, 软件架构及大数据技术知识分享平台

网站首页 > 数据库 / 正文

Oracle对百万条记录的数据sqlldr加载(超详细)

2024-11-26 17:57 huorong 数据库 5 ℃ 0 评论

欢迎关注

奋进的码哥的博客_CSDN博客-Oracle,hadoop,PostgreSQL领域博主

https://blog.csdn.net/u010438126?type=blog

背景：

在实际工作中应用SQLLDR执行数据加载，多数情况是数据量已经达到了一定的级别，下面是我自己构建了100W条记录的场景，看看实际加载情况如何，以及如何进行加载优化。

1，找到一张合适的表

[root@dongjj-pc-01 file]#sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on 星期三 12月 28 17:45:36 2022

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

SQL> conn sys/abcABC123 as sysdba

已连接。

SQL> select count(*) from dba_objects;

COUNT(*)

----------

72930

做笛卡尔积关联，生成130W的记录

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 as rn from dual connect by rownum <=20) b;

SQL> select count(*) from (

2 select a.owner||',"'||a.object_name||'",'||a.object_id||','||to_char(a.created,'YYYY-MM-DD hh24:mi:ss')||','||a.status

3 from dba_objects a,(select rownum as rn from dual connect by rownum <=20) b

4 ) m

5 ;


COUNT(*)

----------

1458600

2，生成数据文件并初始化数据

生成140W的记录，到指定csv文件中，具体的操作步骤如下

首先创建一个sql文件，保存上面的语句，注意在sql后面已经带分号 ;

[root@dongjj-pc-01 sql]#ls

load.sql

[root@dongjj-pc-01 sql]#pwd

/file/sql

[root@dongjj-pc-01 sql]#cat load.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 as rn from dual connect by rownum <=20) b;

[root@dongjj-pc-01 sql]#

创建执行sql文件，getdata.sql

set echo off

set term off

set line 1000 pages 0

set feedback off

set heading off

spool /file/data/load_data.csv

@/file/sql/load.sql

spool off

set heading on

set feedback on

set term on

set echo on

[root@dongjj-pc-01 sql]#pwd

/file/sql

[root@dongjj-pc-01 sql]#cat getdata.sql

set echo off

set term off

set trims on --去掉空字符

set line 1000 pages 0

set feedback off

set heading off

spool /file/data/load_data.csv

@/file/sql/load.sql

spool off

set heading on

set feedback on

set term on

set echo on

[root@dongjj-pc-01 sql]#

然后到sqlplus中，命令行执行getdata.sql

@/file/sql/getdata.sql

[root@dongjj-pc-2005 data]#ll -h

总用量 1.4G

-rw-r--r-- 1 root root 1.4G 1月 8 18:08 load_data.csv

初始化，创建测试表

create table tb_getdata(

owner varchar2(30),

object_name varchar2(50),

object_id number,

status varchar2(50),

created date

);

create index idx_ob on tb_getdata(owner,object_name);

4，创建控制文件 ldr_tb_getdata.ctl

LOAD DATA

INFILE '/file/data/load_data.csv'

truncate into table tb_getdata

fields terminated by "," optionally enclosed by '"'

(OWNER,

OBJECT_NAME,

OBJECT_ID,

CREATED date 'yyyy-mm-dd hh24:mi:ss',

STATUS

)

注意时间列这个字段，已经指定了时间格式，并进行了转换，一定要和数据文件的时间格式相符。不然会导致失败。

5，sqlldr 命令常规导入

sqlldr c##djj/c##djj@orcl control=ldr_tb_getdata.ctl errors = 10

查看导入日志

SQL*Loader: Release 12.2.0.1.0 - Production on 星期日 1月 8 20:41:42 2023

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

控制文件:      ldr_tb_getdata.ctl
数据文件:      /file/data/load_data.csv
  错误文件:    load_data.bad
  废弃文件:    未作指定
 
(可废弃所有记录)

要加载的数: ALL
要跳过的数: 0
允许的错误: 10
绑定数组: 64 行, 最大 256000 字节
继续:    未作指定
所用路径:       常规

表 TB_GETDATA,已加载从每个逻辑记录
插入选项对此表 TRUNCATE 生效

   列名                  位置   长度  中止 包装 数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
OWNER                               FIRST     *   ,  O (") CHARACTER            
OBJECT_NAME                          NEXT     *   ,  O (") CHARACTER            
OBJECT_ID                            NEXT     *   ,  O (") CHARACTER            
CREATED                              NEXT     *   ,  O (") DATE yyyy-mm-dd hh24:mi:ss
STATUS                               NEXT     *   ,  O (") CHARACTER            


表 TB_GETDATA:
  已成功载入 1453560 行。
  由于数据错误, 0 行 没有加载。
  由于所有 WHEN 子句失败, 0 行 没有加载。
  由于所有字段都为空值, 0 行 没有加载。


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

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

从 星期日 1月  08 20:41:42 2023 开始运行
在 星期日 1月  08 20:42:24 2023 处运行结束

经过时间为: 00: 00: 41.83
CPU 时间为: 00: 00: 05.31

5.1 sqlldr 优化，使用rows

sqlldr这个命令默认的加载行数是64行，因为现在导入的行数是百万级别，需要重新设置rows的值

--加载全部数据，同时指定errors参数值=10，指定出现10次错误就停止加载。rows = 640

sqlldr c##djj/c##djj@orcl control=ldr_tb_getdata.ctl errors = 10 rows = 640

经过重设rwos的值，发现本次的导入时间缩短


SQL*Loader: Release 12.2.0.1.0 - Production on 星期日 1月 8 20:49:13 2023

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

控制文件:      ldr_tb_getdata.ctl
数据文件:      /file/data/load_data.csv
  错误文件:    load_data.bad
  废弃文件:    未作指定
 
(可废弃所有记录)

要加载的数: ALL
要跳过的数: 0
允许的错误: 10
绑定数组: 640 行, 最大 256000 字节
继续:    未作指定
所用路径:       常规

表 TB_GETDATA,已加载从每个逻辑记录
插入选项对此表 TRUNCATE 生效

   列名                  位置   长度  中止 包装 数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
OWNER                               FIRST     *   ,  O (") CHARACTER            
OBJECT_NAME                          NEXT     *   ,  O (") CHARACTER            
OBJECT_ID                            NEXT     *   ,  O (") CHARACTER            
CREATED                              NEXT     *   ,  O (") DATE yyyy-mm-dd hh24:mi:ss
STATUS                               NEXT     *   ,  O (") CHARACTER            

ROWS 参数所用的值已从 640 更改为 198

表 TB_GETDATA:
  已成功载入 1453560 行。
  由于数据错误, 0 行 没有加载。
  由于所有 WHEN 子句失败, 0 行 没有加载。
  由于所有字段都为空值, 0 行 没有加载。


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

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

从 星期日 1月  08 20:49:13 2023 开始运行
在 星期日 1月  08 20:49:43 2023 处运行结束

经过时间为: 00: 00: 29.98
CPU 时间为: 00: 00: 04.45

5.2 sqlldr优化，使用bindsize

可以设置bindsize的值，默认是256K，可以改为10m（即 1024*1024*10 = 10485760），同时将一次加载的行数提高到5000

sqlldr c##djj/c##djj@orcl control=ldr_tb_getdata.ctl errors = 10 rows = 5000 bindsize = 10485760

SQL*Loader: Release 12.2.0.1.0 - Production on 星期日 1月 8 20:56:01 2023

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

控制文件:      ldr_tb_getdata.ctl
数据文件:      /file/data/load_data.csv
  错误文件:    load_data.bad
  废弃文件:    未作指定
 
(可废弃所有记录)

要加载的数: ALL
要跳过的数: 0
允许的错误: 10
绑定数组: 5000 行, 最大 10485760 字节
继续:    未作指定
所用路径:       常规

表 TB_GETDATA,已加载从每个逻辑记录
插入选项对此表 TRUNCATE 生效

   列名                  位置   长度  中止 包装 数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
OWNER                               FIRST     *   ,  O (") CHARACTER            
OBJECT_NAME                          NEXT     *   ,  O (") CHARACTER            
OBJECT_ID                            NEXT     *   ,  O (") CHARACTER            
CREATED                              NEXT     *   ,  O (") DATE yyyy-mm-dd hh24:mi:ss
STATUS                               NEXT     *   ,  O (") CHARACTER            


表 TB_GETDATA:
  已成功载入 1453560 行。
  由于数据错误, 0 行 没有加载。
  由于所有 WHEN 子句失败, 0 行 没有加载。
  由于所有字段都为空值, 0 行 没有加载。


为绑定数组分配的空间:               6450000 字节 (5000 行)
读取   缓冲区字节数:10485760

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

从 星期日 1月  08 20:56:01 2023 开始运行
在 星期日 1月  08 20:56:25 2023 处运行结束

经过时间为: 00: 00: 23.81
CPU 时间为: 00: 00: 03.58

发现又缩短了几秒的时间。

那还能不能在快点呢？下面该使用杀手锏了。。。。。

可以使用直接路径加载，需要设置direct参数

5.3 sqlldr 优化，direct参数

sqlldr c##djj/c##djj@orcl control=ldr_tb_getdata.ctl direct = true


SQL*Loader: Release 12.2.0.1.0 - Production on 星期日 1月 8 21:00:23 2023

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

控制文件:      ldr_tb_getdata.ctl
数据文件:      /file/data/load_data.csv
  错误文件:    load_data.bad
  废弃文件:    未作指定
 
(可废弃所有记录)

要加载的数: ALL
要跳过的数: 0
允许的错误: 50
继续:    未作指定
所用路径:       直接

表 TB_GETDATA,已加载从每个逻辑记录
插入选项对此表 TRUNCATE 生效

   列名                  位置   长度  中止 包装 数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
OWNER                               FIRST     *   ,  O (") CHARACTER            
OBJECT_NAME                          NEXT     *   ,  O (") CHARACTER            
OBJECT_ID                            NEXT     *   ,  O (") CHARACTER            
CREATED                              NEXT     *   ,  O (") DATE yyyy-mm-dd hh24:mi:ss
STATUS                               NEXT     *   ,  O (") CHARACTER            

表 TB_GETDATA 的以下索引已处理:
索引 C##DJJ.IDX_OB 已成功加载, 具有 1453560 个关键字

表 TB_GETDATA:
  已成功载入 1453560 行。
  由于数据错误, 0 行 没有加载。
  由于所有 WHEN 子句失败, 0 行 没有加载。
  由于所有字段都为空值, 0 行 没有加载。

  日期转换高速缓存由于溢出而被禁用 (默认大小: 1000)

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

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

从 星期日 1月  08 21:00:23 2023 开始运行
在 星期日 1月  08 21:00:38 2023 处运行结束

经过时间为: 00: 00: 14.80
CPU 时间为: 00: 00: 02.01

时间直接缩短了10多秒，相当不错了啊

那还能不能再快点啊？别着急，还有办法啊

5.4 sqlldr优化，streamsize参数和date_cache参数

streamsize :

直接路径加载默认读取全部数据，因此不需要设置row参数，读取的数据存入流缓存区。该参数默认值是256k，可以加载到10mb，即10485760

date_cache:

该参数指定一个转换后的时间格式缓存区，以条为单位，默认值是1000条，即保存1000条后转换时间格式。因为导入的数据中有时间列，所以把该参数设置5000，以降低日期转换操作带来的时间开销。

sqlldr c##djj/c##djj@orcl control=ldr_tb_getdata.ctl direct = true streamsize = 10485760 date_cache = 5000

SQL*Loader: Release 12.2.0.1.0 - Production on 星期日 1月 8 21:17:16 2023

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

控制文件:      ldr_tb_getdata.ctl
数据文件:      /file/data/load_data.csv
  错误文件:    load_data.bad
  废弃文件:    未作指定
 
(可废弃所有记录)

要加载的数: ALL
要跳过的数: 0
允许的错误: 50
继续:    未作指定
所用路径:       直接

表 TB_GETDATA,已加载从每个逻辑记录
插入选项对此表 TRUNCATE 生效

   列名                  位置   长度  中止 包装 数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
OWNER                               FIRST     *   ,  O (") CHARACTER            
OBJECT_NAME                          NEXT     *   ,  O (") CHARACTER            
OBJECT_ID                            NEXT     *   ,  O (") CHARACTER            
CREATED                              NEXT     *   ,  O (") DATE yyyy-mm-dd hh24:mi:ss
STATUS                               NEXT     *   ,  O (") CHARACTER            

表 TB_GETDATA 的以下索引已处理:
索引 C##DJJ.IDX_OB 已成功加载, 具有 1453560 个关键字

表 TB_GETDATA:
  已成功载入 1453560 行。
  由于数据错误, 0 行 没有加载。
  由于所有 WHEN 子句失败, 0 行 没有加载。
  由于所有字段都为空值, 0 行 没有加载。

  日期高速缓存:
   最大大小:      5000
   条目数:      1663
   命中数    :   1451897
   未命中数  :         0

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

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

从 星期日 1月  08 21:17:16 2023 开始运行
在 星期日 1月  08 21:17:24 2023 处运行结束

经过时间为: 00: 00: 07.65
CPU 时间为: 00: 00: 01.09

从以上结果看出，时间直接缩短了两倍不止啊。。。。。

Tags：oracle 记录

猜你喜欢

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言