sqlldr批量导入导出数据测试(86天)

sqlldr是在处理大数据量的操作中建议采用的方式,它有许多性能想关的开关,能最大程度的减少redo,undo的生成,控制数据的处理方式(insert,append,replace,truncate) 因为项目需要,对比datapump性能还是不理想,所以还是希望采用sqlldr来做。个人做了简单的测试。 根据thomas kyte的介绍,并行执行路径加载时最快的方式,能够直接写只格式化的数据块,最大限度的减少redo,undo的生成。 先写了如下的脚本。可以动态的从某个用户的表中生成元数据。 sqlplus -s $1 <<EOF set pages 0 col object_name format a30 set linseize 10000 set feedback off set colsep ',' spool $2.lst select *from $2 ; spool off; EOF 运行后生成的数据大体如下。

[ora11g@rac1 sqlldr]$ ksh spooldata.sh n1/n1 t
    370753,     10205,KU$_DOMIDX_OBJNUM_VIEW        ,VIEW
    370754,     10207,KU$_OPTION_OBJNUM_T           ,TYPE
    370755,     10208,KU$_EXPREG                    ,VIEW
    370756,     10210,SYS_YOID0000010209$           ,TYPE
    370757,     10209,KU$_OPTION_OBJNUM_VIEW        ,VIEW
    370758,     10211,KU$_OPTION_VIEW_OBJNUM_VIEW   ,VIEW
    370759,     10212,KU$_MARKER_T                  ,TYPE
    370760,     10214,SYS_YOID0000010213$           ,TYPE
    370761,     10213,KU$_MARKER_VIEW               ,VIEW
    370762,     10215,KU$_TABPROP_VIEW              ,VIEW
    370763,     10216,KU$_PFHTABPROP_VIEW           ,VIEW
    370764,     10217,KU$_REFPARTTABPROP_VIEW       ,VIEW
    370765,     10218,KU$_MVPROP_VIEW               ,VIEW
    370766,     10219,KU$_MVLPROP_VIEW              ,VIEW
    370767,     10220,KU$_TTS_VIEW                  ,VIEW
    370768,     10221,KU$_TAB_TS_VIEW               ,VIEW
    370769,     10222,KU$_TTS_IND_VIEW              ,VIEW
    370770,     10223,KU$_IND_TS_VIEW               ,VIEW
    370771,     10224,KU$_CLU_TS_VIEW               ,VIEW

然后准备控制文件 sqlldr.ctl,把数据从t加载到tt里面去。 load data into table tt fields terminated by ',' (id,object_id,object_name,object_type) 尝试导入: [ora11g@rac1 sqlldr]$ sqlldr n1/n1 control=sqlldr.ctl data=t.lst SQL*Loader: Release 11.2.0.3.0 - Production on Tue May 27 08:09:25 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. 但是没有任何的反馈。 查看自动生成的sqlldr.log 里面有如下的错误。 Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- ID FIRST * , CHARACTER OBJECT_ID NEXT * , CHARACTER OBJECT_NAME NEXT * , CHARACTER OBJECT_TYPE NEXT * , CHARACTER Record 1: Rejected - Error on table TT, column OBJECT_TYPE. Field in data file exceeds maximum length Record 2: Rejected - Error on table TT, column OBJECT_TYPE. Field in data file exceeds maximum length Record 3: Rejected - Error on table TT, column OBJECT_TYPE. Field in data file exceeds maximum length Record 4: Rejected - Error on table TT, column OBJECT_TYPE. Field in data file exceeds maximum length

尝试了好一会儿,最终发现时set linesize的时候长度设置的比较大,在根据逗号','来解析的时候,最后一个字段的长度就包含了剩余的空格,最终加载的时候就会发现它的长度太大了。已经超出了表定义的长度。 这种情况,我总不能一个一个指定长度吧。 这时候想到trimspool的功能,尝试果然奏效。 spooldata.sh的脚本内容如下: sqlplus -s $1 <<EOF set pages 0 col object_name format a30 set linesize 10000 set trimspool on set feedback off set colsep ',' spool $2.lst select *from $2 where rownum<20 ; spool off; EOF 再次尝试导入,就没有问题了。 [ora11g@rac1 sqlldr]$ sqlldr n1/n1 control=sqlldr.ctl data=t.lst SQL*Loader: Release 11.2.0.3.0 - Production on Tue May 27 08:14:44 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Commit point reached - logical record count 19 到此为止,来开始看看direct方式到底有多大的性能提升 对于将近80万的数据量测试情况如下。 没有采用direct方式的时候,会有一定的频度(默认50条一次)来加载数据,耗时79秒,基本一秒一万条 Commit point reached - logical record count 793480 Commit point reached - logical record count 793544 Commit point reached - logical record count 793608 Commit point reached - logical record count 793672 Commit point reached - logical record count 793736 Commit point reached - logical record count 793800 Commit point reached - logical record count 793864 Commit point reached - logical record count 793928 Commit point reached - logical record count 793992 Commit point reached - logical record count 794056 Commit point reached - logical record count 794120 Commit point reached - logical record count 794184 Commit point reached - logical record count 794248 Commit point reached - logical record count 794312 Commit point reached - logical record count 794369 但是使用了direct=true的时候,速度明显提升,而且输出也很简单,就下面一行。耗时8秒,基本一秒10万条数据。 8s [ora11g@rac1 sqlldr]$ sqlldr n1/n1 direct=true control=sqlldr.ctl data=t.lst SQL*Loader: Release 11.2.0.3.0 - Production on Tue May 27 07:56:31 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Load completed - logical record count 794369.

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-05-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

Oracle数据库的初始化与跟踪学习方法

编辑说明:《Oracle性能优化与诊断案例精选》出版以来,收到很多读者的来信和评论,我们会通过连载的形式将书中内容公布出来,希望书中内容能够帮助到更多的读者朋友...

3439
来自专栏杨建荣的学习笔记

通过shell解析dump生成parfile(r2笔记76天)

当我们得到一个dump文件的时候,总是有些不太确定dump文件中是否含有一些我们原本不希望出现的表,如果在未知的情况下对dump文件进行操作时很危险的,比如我们...

2033
来自专栏更流畅、简洁的软件开发方式

记录容易忘记的方法

提取每个分类前n条记录 SELECT ID, Name, CategoryID FROM TableName AS a WHERE (ID IN ...

22710
来自专栏乐沙弥的世界

Oracle 控制文件(CONTROLFILE)

为二进制文件,初始化大小由CREATE DATABASE指定,可以使用RMAN备份

882
来自专栏乐沙弥的世界

Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)

--==============================================

593
来自专栏杨建荣的学习笔记

关于奇怪的并行进程分析(二) (r6笔记第46天)

前几天的并行问题自己分析了下,也算有了一些进展,但是目前还没有找到让人信服的理由,有些读者也比较关心这个问题,所以第二篇中会把自己的分析过程写出来,第三篇中应该...

2533
来自专栏数据和云

推陈出新:12C 推进 SCN 新方法实践

在数据库异常恢复中,经常需要修改数据库的 SCN 值,在 12C 之前,我们常用的方法有如下几个: oradebug poke 直接修改内存中的值; event...

31110
来自专栏乐沙弥的世界

PL/SQL-->UTL_FILE包的使用介绍

    在PL/SQL中,UTL_FILE包提供了文本文件输入和输出互功能。也就是说我们可以通过该包实现从操作系统级别来实现文件读取输入或者是写入到操作系统文件...

521
来自专栏数据库

Oracle常用经典SQL查询(一)

注:本文的查询测试以oracle11.2.0.4.0企业版作为查询测试,截图只是为了证明SQL的可用性及正确性,查询结果因测试环境不同各有差异。 1、查看表空间...

1666
来自专栏杨建荣的学习笔记

Oracle 12c Data Guard搭建(一) (r10笔记第57天)

对于使用12c的PDB,如果想尽快熟悉,掌握,那就是和业务挂钩,让它跑在业务上。当然是在能够基本驾驭它的前提下,要不就真成了甩手掌柜。11g可以玩得很好...

3569

扫码关注云+社区