比较使用sql*loader的直接加载方式和传统加载方式的性能差异

一数据库版本

SYS@LEO1>select* from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - 64bit Production

PL/SQLRelease 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS forLinux: Version 11.2.0.1.0 - Production

NLSRTLVersion 11.2.0.1.0 – Production

操作系统信息

[oracle@leonarding1admin]$ uname -a

Linuxleonarding1.oracle.com 2.6.32-200.13.1.el5uek #1 SMP Wed Jul 27 21:02:33 EDT 2011x86_64 x86_64 x86_64 GNU/Linux

二比较使用sql*loader的直接加载方式和传统加载方式的性能差异，给出演示过程和结论。

第一 我们先要生成平面数据（文本数据）

LEO1@LEO1>create table leo2 as select *from dba_objects;      创建数据源，我们的平面数据就是从这个表中取出

Table created.

第二 我们利用spool工具将屏幕中显示出来的记录写入到指定文件，这样我们就可以得到一个平面文件啦

set termout off;            是否在屏幕上显示输出内容，off屏幕不显示查询语句，主要与spool结合使用

set feedback off;           关闭本次sql命令处理的记录条数，默认为on即去掉最后的已经选择的行数

set echo off;              关闭脚本中正在执行的SQL语句的显示

set heading off;            关闭标题的输出，设置为off就去掉了select结果的字段名只显示数据

set trimout on;            去除标准输出每行后面多余的空格

set trimspool on;          将每行后面多余的空格去掉【linesize-实际字符数=多余空格】

spool /home/oracle/sql_loader/leo3.txt      在屏幕上的所有内容都包含在该文件中

select owner||','||object_name||','||object_id||','||object_typefrom leo2;   

spool off                         只有关闭spool输出，才会在输出文件中看到输出的内容

备注：在实用SPOOL输出内容到本地文件时，需注意编码格式，否则会出现乱码的问题

[oracle@leonarding1 sql_loader]$ ll

total 28468

-rw-r--r-- 1 oracle oinstall  3246601 Jun 22 14:06 leo3.txt          已经生成平面文件leo3.txt

[oracle@leonarding1 sql_loader]$ cat leo3.txt| wc -l              文件中有72678行记录

72678

第三 创建装入的表leo3_loader

LEO1@LEO1>create table leo3_loader

(

  owner       varchar2(30),

  object_name varchar2(130),

  object_id   number,

  object_type varchar2(20)

);

2    3    4   5    6    7  

第四 创建sql*loader的控制文件leo3_loader.ctl

[oracle@leonarding1 sql_loader]$ vim leo3_loader.ctl

load data

infile '/home/oracle/sql_loader/leo3.txt'                             待加载的数据文件

badfile '/home/oracle/sql_loader/leo3_bad.txt'                       格式不匹配写入坏文件

discardfile'/home/oracle/sql_loader/leo3_discard.txt'                  条件不匹配写入丢弃文件

append into table leo3_loader                                     追加的方式插入数据

fields terminated by ","                                           字段与字段之间的分隔符

trailing nullcols                                                 这句的意思是将没有对应值的列都置为null

(owner,object_name,object_id,object_type)                         数据插入的对应字段

第五 执行sqlldr直接加载命令

[oracle@leonarding1 sql_loader]$ sqlldrleo1/leo1 control=leo3_loader.ctl log=leo3_loader.log direct=true

SQL*Loader: Release 11.2.0.1.0 - Productionon Sat Jun 22 14:08:31 2013

Copyright (c) 1982, 2009, Oracle and/or itsaffiliates.  All rights reserved.

Load completed - logical record count72678.

已经加载了72678行，条件不匹配有72行，实际加载入72606行

LEO1@LEO1>select count(*) fromleo3_loader;   表中也是

    72606

我们在看一下sql*loader日志

。。。省略前部份。。。

Total logical records skipped:          0

Total logical recordsread:         72678

Total logical records rejected:         0

Total logical records discarded:       72                   条件不匹配有72行

Total stream buffers loaded by SQL*Loadermain thread:       17

Total stream buffers loaded by SQL*Loaderload thread:        6

Run began on Sat Jun 22 14:08:31 2013

Run ended on Sat Jun 22 14:08:34 2013

Elapsed time was:     00:00:02.60                       所用耗时2.6秒

CPU time was:         00:00:00.13

使用conventional传统加载方式写入数据

LEO1@LEO1>truncate table leo3_loader;                   清空表在加载一次

[oracle@leonarding1 sql_loader]$ sqlldrleo1/leo1 control=leo3_loader.ctl log=leo3_loader.log

LEO1@LEO1>select count(*) fromleo3_loader;   表中也是

    72606

我们在看一下sql*loader日志

。。。省略前部份。。。

Total logical records skipped:          0

Total logical recordsread:         72678

Total logical records rejected:         0

Total logical records discarded:       72                  条件不匹配有72行

Run began on Sat Jun 22 15:25:45 2013

Run ended on Sat Jun 22 15:26:05 2013

Elapsed time was:     00:00:20.79                       所用耗时2.6秒

CPU time was:         00:00:00.48

小结：经过比对direct比conventional要提高了20倍效率，为什么direct会这么高效呢，下面我们来说说这两种的区别。

Direct 特点

（1）数据绕过SGA直接写入磁盘的数据文件

（2）数据直接写入高水位线HWM之后的新块，不会扫描HWM之前的空闲块

（3）commit之后移动HWM他人才能看到

（4）不对已用空间进行扫描

（5）使用direct几乎不产生redo log，不是完全不产生(安全性差)，但会产生undo数据

（6）适用OLAP在线分析场景，增 删 改不频繁的场景

Conventional传统加载特点

（1）数据先加载 -> SGA -> 磁盘的数据文件

（2）会扫描高水位线HWM之前的数据块，如果有空闲块(碎片经常DML导致)就会利用，如果没有再插入新块

（3）高水位线HWM之前的数据块是放在SGA区的

（4）会产生redo log和undo数据

（5）安全性高，可恢复数据

（6）传统加载与SQL语句insert插入没区别

 

 

Leonarding
2013.6.22
北京&summer
分享技术~成就梦想
Blog：www.leonarding.com