百度360必应搜狗淘宝本站头条
linux文件压缩linux用户组查看linux磁盘mysql安装linuxlinux多线程
当前位置：网站首页 > 优雅编程 > 正文

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

sinye56 2024-10-03 00:34 23 浏览 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

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

相关推荐

程序员:JDK的安装与配置(完整版)_jdk的安装方法

对于Java程序员来说，jdk是必不陌生的一个词。但怎么安装配置jdk，对新手来说确实头疼的一件事情。我这里以jdk10为例，详细的说明讲解了jdk的安装和配置，如果有不明白的小伙伴可以评论区留言哦下...

Linux中安装jdk并配置环境变量_linux jdk安装教程及环境变量配置

一、通过连接工具登录到Linux(我这里使用的Centos7.6版本)服务器连接工具有很多我就不一一介绍了今天使用比较常用的XShell工具登录成功如下：二、上传jdk安装包到Linux服务器jdk...

麒麟系统安装JAVA JDK教程_麒麟系统配置jdk

检查检查系统是否自带java在麒麟系统桌面空白处，右键“在终端打开”，打开shell对话框输入：java–version查看是否自带java及版本如图所示，系统自带OpenJDK，要先卸载自带JDK...

学习笔记-Linux JDK - 安装&amp;配置

前提条件#检查是否存在JDKrpm-qa|grepjava#删除现存JDKyum-yremovejava*安装OracleJDK不分系统#进入安装文件目...

Linux新手入门系列:Linux下jdk安装配置

本系列文章是把作者刚接触和学习Linux时候的实操记录分享出来，内容主要包括Linux入门的一些理论概念知识、Web程序、mysql数据库的简单安装部署，希望能够帮到一些初学者，少走一些弯路。注意：L...

测试员必备:Linux下安装JDK 1.8你必须知道的那些事

1.简介在Oracle收购Sun后，Java的一系列产品就被整合到Oracle官网中，打开官网乍眼一看也不知道去哪里下载，还得一个一个的摸索尝试，而且网上大多数都是一些Oracle收购Sun前，或者就...

Linux 下安装JDK17_linux 安装jdk1.8 yum

一、安装环境操作系统：JDK版本：17二、安装步骤第一步：下载安装包下载Linux环境下的jdk1.8，请去官网（https://www.oracle.com/java/technologies/do...

在Ubuntu系统中安装JDK 17并配置环境变量教程

在Ubuntu系统上安装JDK17并配置环境变量是Java开发环境搭建的重要步骤。JDK17是Oracle提供的长期支持版本，广泛用于开发Java应用程序。以下是详细的步骤，帮助你在Ubuntu系...

如何在 Linux 上安装 Java_linux安装java的步骤

在桌面上拥抱Java应用程序，然后在所有桌面上运行它们。--SethKenlon（作者）无论你运行的是哪种操作系统，通常都有几种安装应用程序的方法。有时你可能会在应用程序商店中找到一个应用程序...

Windows和Linux环境下的JDK安装教程

JavaDevelopmentKit（简称JDK），是Java开发的核心工具包，提供了Java应用程序的编译、运行和开发所需的各类工具和类库。它包括了JRE（JavaRuntimeEnviro...

linux安装jdk_linux安装jdk软连接

JDK是啥就不用多介绍了哈，外行的人也不会进来看我的博文。依然记得读大学那会，第一次实验课就是在机房安装jdk，编写HelloWorld程序。时光飞逝啊，一下过了十多年了，挣了不少钱，买了跑车，娶了富...

linux安装jdk,全局配置，不同用户不同jdk

jdk1.8安装包链接：https://pan.baidu.com/s/14qBrh6ZpLK04QS8ogCepwg提取码：09zs上传文件解压tar-zxvfjdk-8u152-linux-...

运维大神教你在linux下安装jdk8_linux安装jdk1.7

1.到官网下载适合自己机器的版本。楼主下载的是jdk-8u66-linux-i586.tar.gzhttp://www.oracle.com/technetwork/java/javase/downl...

window和linux安装JDK1.8_linux 安装jdk1.8.tar

Windows安装JDK1.8的步骤：步骤1：下载JDK打开浏览器，找到JDK下载页面https://d.injdk.cn/download/oraclejdk/8在页面中找到并点击“下载...

最全的linux下安装JavaJDK的教程(图文详解)不会安装你来打我?

默认已经有了linux服务器，且有root账号首先检查一下是否已经安装过java的jdk任意位置输入命令：whichjava像我这个已经安装过了，就会提示在哪个位置，你的肯定是找不到。一般我们在...

取消回复欢迎 你 发表评论:

一周热门
最近发表
标签列表