SQLLDR直接加载能否分批提交?

hrb_qiuyb

测过这样的问题，不过没做细致的分析
oracle也就是一个应用程序，与程序设计有关

sqlldr中的在直接装载时rows所表述的是多少行save 一下data
文档中也说了，这是一个大致的量，实际save多少与buffer所决定的bindsize有关
注意，这里说的是save data

save data只是类似于commit的效果
相同的地方时你在direct load的过程中出现了instance down等类似的状况，save的数据会实际保存到库中
不相同的部分大体有如下几点：
1.直接装载rows参数所表述的save data并没有事务的概念,不经过undo segment，因此无法回滚
2.direct是在hwm上的一个操作，成功了调整hwm，失败则好似未发生过这样的操作
3.direct load在装载时未不维护索引

所以设置了rows参数在load过程表中看不到数据的原因可能如下：
1、如hwm有关，即操作完成及instance recover或pmon清理时才发生调整hwm的操作
2、与direct load是一个无事务的操作有关
3、rows所表述的是save data,即extent操作,与commit所表述的事务操作是不同的

jolly10

谢谢您的回答.

[quote]原帖由 hrb_qiuyb 于 2008-7-17 15:04 发表
测过这样的问题，不过没做细致的分析
oracle也就是一个应用程序，与程序设计有关

sqlldr中的在直接装载时rows所表述的是多少行save 一下data
文档中也说了，这是一个大致的量，实际save多少与buffer所决定的bindsize有关
注意，这里说的是save data

save data只是类似于commit的效果
相同的地方时你在direct load的过程中出现了instance down等类似的状况，save的数据会实际保存到库中
不相同的部分大体有如下几点：
1.直接装载rows参数所表述的save data并没有事务的概念,不经过undo segment，因此无法回滚
  --不太懂没有事务的概念.  普通加载应该是有事务的吧.
2.direct是在hwm上的一个操作，成功了调整hwm，失败则好似未发生过这样的操作
  --这个我再测试一下,loading过程中看hwm是不是增长了
3.direct load在装载时未不维护索引
  --好像不好,我们用的直接加载索引也会维护的.一样受skip_index_maintenance参数的影响

jolly10

此测试的目的是为了证明SQLLDR直接加载时,加了rows参数时,save和commit的区别.
测试发现直接加载的过程中HWM是在增长的.
SQL> set serveroutput on;
SQL>  execute sys.show_space('LOAD_01','L5M','AUTO');
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................5
Last Used Ext BlockId...................1001
Last Used Block.........................4

PL/SQL procedure successfully completed.

load之前,HWM是8-4=4,HWM所在的block为1001+4-1=1004.


[oracle@qht108 sqlldr]$ sqlldr parfile=para.txt

SQL*Loader: Release 10.2.0.4.0 - Production on Thu Jul 17 17:38:39 2008

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

Save data point reached - logical record count 10000.
Save data point reached - logical record count 20000.
Save data point reached - logical record count 30000.
Save data point reached - logical record count 40000.
Save data point reached - logical record count 50000.
Save data point reached - logical record count 60000.
Save data point reached - logical record count 70000.
Save data point reached - logical record count 80000.
Save data point reached - logical record count 90000.
....


SQL> select count(*) from l5m.load_01;

  COUNT(*)
----------
         0


SQL> select segment_name,blocks from dba_segments where owner='L5M' and segment_name='LOAD_01';

SEGMENT_NAME          BLOCKS
------------------------------------------------------------
LOAD_01                16656


SQL> execute sys.show_space('LOAD_01','L5M','AUTO');
Total Blocks............................17408
Total Bytes.............................142606336
Unused Blocks...........................326
Unused Bytes............................2670592
Last Used Ext FileId....................5
Last Used Ext BlockId...................16521
Last Used Block.........................698

PL/SQL procedure successfully completed.

load中,HWM是17408-326=17082,HWM所在的block为16521+698-1=17218.说明HWM随着load的进行在增长.


load完成后的情况.
SQL> select count(*) from l5m.load_01;

  COUNT(*)
----------
   1123237

SQL> select segment_name,blocks from dba_segments where owner='L5M' and segment_name='LOAD_01';

SEGMENT_NAME
--------------------------------------------------------------------------------
    BLOCKS
----------
LOAD_01
     34816


SQL>  execute sys.show_space('LOAD_01','L5M','AUTO');
Total Blocks............................34816
Total Bytes.............................285212672
Unused Blocks...........................631
Unused Bytes............................5169152
Last Used Ext FileId....................5
Last Used Ext BlockId...................33929
Last Used Block.........................393

PL/SQL procedure successfully completed.

[ 本帖最后由 jolly10 于 2008-7-18 16:47 编辑 ]

hrb_qiuyb

初始怀疑dbms_space提交的hwm不准确
我也做了一个测试，以dump segment header方式完成的
结果证明也与hwm无关

测试流程：
[php]
1、库中有一张表rr,准备了300w条数据
SQL> desc rr
Name                    Type         Nullable Default Comments
----------------------- ------------ -------- ------- --------
STAFFID                 CHAR(7)
DEPARTID                CHAR(4)
CITYCODE                CHAR(1)
CLEARDAYTIME            CHAR(8)
CLEARDAYTIME_S          VARCHAR2(20)
CLEARDAYTIME_E          VARCHAR2(20)
AGENT                   CHAR(1)
RUWANGCOUNT             NUMBER(12)
RUWANGFEE               NUMBER(12,2)
XUANHAOCOUNT            NUMBER(12)
XUANHAOFEE              NUMBER(12,2)
DAISHOUWUWEICOUNT       NUMBER(12)
DAISHOUWUWEIFEE         NUMBER(12,2)
BIANGENGCOUNT           NUMBER(12)
BIANGENGFEE             NUMBER(12,2)
CARDIPCOUNT             NUMBER(12)
CARDIPFEE               NUMBER(12,2)
CARD193COUNT            NUMBER(12)
CARD193FEE              NUMBER(12,2)
CARD165COUNT            NUMBER(12)
CARD165FEE              NUMBER(12,2)
CARDSIMCOUNT            NUMBER(12)
... ...

2、导入同样使用direct=true

hpn2$[/hisfs1/work]cat *ctl
OPTIONS(BINDSIZE=16777216,READSIZE=16777216,ERRORS=-1,ROWS=10000,direct=true)
LOAD DATA
INFILE 'rr.txt' "STR X'0a'"
INSERT INTO TABLE rr
FIELDS TERMINATED BY X'2c' TRAILING NULLCOLS
(
  STAFFID CHAR,
  DEPARTID CHAR,
  CITYCODE CHA
... ...

3、找寻segment header
SQL>SELECT segment_name,header_file,header_block
  2    fROM dba_segments
  3  WHERE owner='QIUYB' AND segment_name='RR'；

SEGMENT_NAME         HEADER_FILE HEADER_BLOCK
-------------------- ----------- ------------
RR                            22           12

4、执行load数据，过程中不断dump segment header,做过5次
hpn2$[/hisfs1/work]sqlldr qiuyb/qiuyb control=rr_sqlldr.ctl

SQL*Loader: Release 10.2.0.3.0 - Production on Fri Jul 18 10:01:38 2008

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

Save data point reached - logical record count 10000.
Save data point reached - logical record count 20000.
Save data point reached - logical record count 30000.
Save data point reached - logical record count 40000.
Save data point reached - logical record count 50000.
Save data point reached - logical record count 60000.
Save data point reached - logical record count 70000.
Save data point reached - logical record count 80000.
Save data point reached - logical record count 90000.
Save data point reached - logical record count 100000.
Save data point reached - logical record count 110000.
Save data point reached - logical record count 120000.
Save data point reached - logical record count 130000.
Save data point reached - logical record count 140000.
Save data point reached - logical record count 150000.
Save data point reached - logical record count 160000.
Save data point reached - logical record count 170000.
Save data point reached - logical record count 180000.
Save data point reached - logical record count 190000.
Save data point reached - logical record count 200000.
Save data point reached - logical record count 210000.
Save data point reached - logical record count 220000.
Save data point reached - logical record count 230000.
Save data point reached - logical record count 240000.
Save data point reached - logical record count 250000.
Save data point reached - logical record count 260000.
Save data point reached - logical record count 270000.
Save data point reached - logical record count 280000.
Save data point reached - logical record count 290000.
Save data point reached - logical record count 300000.
Save data point reached - logical record count 310000.
Save data point reached - logical record count 320000.
Save data point reached - logical record count 330000.
Save data point reached - logical record count 340000.
Save data point reached - logical record count 350000.
Save data point reached - logical record count 360000.
Save data point reached - logical record count 370000.
Save data point reached - logical record count 380000.
Save data point reached - logical record count 390000.
Save data point reached - logical record count 400000.
Save data point reached - logical record count 410000.
Save data point reached - logical record count 420000.
Save data point reached - logical record count 430000.
Save data point reached - logical record count 440000.
Save data point reached - logical record count 450000.
SQL*Loader-2028: load discontinued to user interrupt (Ctrl-C) [4]

Load completed - logical record count 455000.

5、按顺序摄取5次hwm的DBA如下:
(1)  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 128
                  last map  0x00000000  #maps: 0      offset: 2720
      Highwater::  0x0580000d  ext#: 0      blk#: 4      ext size: 128

(2)  Extent Header:: spare1: 0      spare2: 0      #extents: 58     #blocks: 7424
                  last map  0x00000000  #maps: 0      offset: 2720
      Highwater::  0x0600032b  ext#: 56     blk#: 34     ext size: 128
(3)  Extent Header:: spare1: 0      spare2: 0      #extents: 110    #blocks: 14080
                  last map  0x00000000  #maps: 0      offset: 2720
      Highwater::  0x050005f4  ext#: 106    blk#: 107    ext size: 128
(4)  Extent Header:: spare1: 0      spare2: 0      #extents: 228    #blocks: 29184
                  last map  0x00000000  #maps: 0      offset: 2720
      Highwater::  0x05400c1a  ext#: 224    blk#: 17     ext size: 128
(5)  Extent Header:: spare1: 0      spare2: 0      #extents: 232    #blocks: 29696
                  last map  0x00000000  #maps: 0      offset: 2720
      Highwater::  0x06800cb1  ext#: 229    blk#: 40     ext size: 128;
[/php]

看来原因还是与direct load的无事务操作有关
有待于进一步的测试

holly_pansy

今天做了如下测试.
1.将表建立在普通模式
  create table ttt(id varchar2(100));
2.control文件内容为load.txt
  LOAD DATA
   INFILE 'data.txt'
   append
   INTO TABLE ttt
   FIELDS TERMINATED BY ","
   (id)
3.数据库运行在归档模式
4.数据文件data.txt大小为4g.
5.direct load
   sqlldr user/pass control=load.txt diret=true rows=1000000

结果如下:   
数据库会产生大量归档日志,可以观察到该sqlldr所产生的会话,以及该会话一直在使用回滚段,表的高水位标志一直在扩展,但是查询该表一直无数据,中断sqlldr后表内可查询到数据.


考虑:
从归档日志,会话情况,回滚段情况来看,direct load与常规load一样;从表的数据查询来看,direct load与常规load不一样
从rows参数的描述,常规load描述为commit,direct load描述为save.

jolly10

我不认同你这一点:
从归档日志,会话情况,回滚段情况来看,direct load与常规load一样;

direct load的目的就是减少redo的产生,如果产生的归档日志一样,还要direct干嘛呢.

测一下传统加载的情况

参数文件改了一下:
[oracle@qht108 sqlldr]$ cat para.txt
userid=l5m/l5m
control='/home/oracle/sqlldr/control.txt'
data='/home/oracle/sqlldr/data.txt'
log='/home/oracle/sqlldr/log.txt'
bad='/home/oracle/sqlldr/bad.txt'
rows=300
bindsize=2560000


SQL> select * from redo_size;

     VALUE
----------
604331212

load之后,再看redo
redo生成的比较大,传统加载嘛,可以理解.
SQL> select * from redo_size;

     VALUE
----------
1211475560

SQL> select 1211475560-604331212 from dual;

1211475560-604331212
--------------------
           607144348

试试直接加载,将rows设为10000

[oracle@qht108 sqlldr]$ cat para.txt
userid=l5m/l5m
control='/home/oracle/sqlldr/control.txt'
data='/home/oracle/sqlldr/data.txt'
log='/home/oracle/sqlldr/log.txt'
bad='/home/oracle/sqlldr/bad.txt'
rows=10000
direct=true

SQL> select * from redo_size;

     VALUE
----------
1213797372


load后的数据

SQL> select * from redo_size;

     VALUE
----------
1214767672


SQL> select 1214767672-1213797372 from dual;

1214767672-1213797372
---------------------
               970300


传统加载产生的redo是607144348,直接加载只产生 970300

jolly10

谢谢,版主费心了,希望早点有结果.

holly_pansy

谢谢楼主的指出,我的关于"direct load与常规load一样;"的确不够严谨.

我在做本次测试的时候,是和另外一个测试一起做的.
得出这段话的依据如下:

我的data.txt文件大小为4.3G,这个文件里的数据格式为
more  data.txt
        97         97                                                           
      4474         97                                                           
      4481         97                                                           
      1810         97                                                           
      5770         97                                                           
      5788         97                                                           
这个数据是从从2个dba_objects表做笛卡尔乘积的2个object_id列spool出来取的数据,在data文件到4g的时候中断spool.

在sqlldr的过程中,direct load加载到30,000,000万行的时候终止sqlldr,共产生了31个100M的归档文件.产生的归档日志量还是很大的.

这个测试还有后续一部分,将该表建立为nologging模式
create table ttt(id varchar2(100)) nologging;
进行同样的测试,以及同样在33,000,000行的时候结束sqlldr,只产生了2个100M的归档日志.

再测试用常规load加载到30,000,000结束sqlldr,观察产生的归档数量为30个100M的归档,与31个100M的归档处于同一数量级.

最后关于一楼的分批提交问题，我的看法是的确不是commit,因为表里面无数据；同样，在rows参数的说明中，oracle特意使用save,而不是commit，不知楼主的看法如何？

jolly10

原帖由 holly_pansy 于 2008-7-18 17:48 发表
谢谢楼主的指出,我的关于"direct load与常规load一样;"的确不够严谨.

我在做本次测试的时候,是和另外一个测试一起做的.
得出这段话的依据如下:

我的data.txt文件大小为4.3G,这个文件里的数据格式为
more  data.txt
        97         97                                                           
      4474         97                                                           
      4481         97                                                           
      1810         97                                                           
      5770         97                                                           
      5788         97                                                           
这个数据是从从2个dba_objects表做笛卡尔乘积的2个object_id列spool出来取的数据,在data文件到4g的时候中断spool.

在sqlldr的过程中,direct load加载到30,000,000万行的时候终止sqlldr,共产生了31个100M的归档文件.产生的归档日志量还是很大的.

这个测试还有后续一部分,将该表建立为nologging模式
create table ttt(id varchar2(100)) nologging;
进行同样的测试,以及同样在33,000,000行的时候结束sqlldr,只产生了2个100M的归档日志.

再测试用常规load加载到30,000,000结束sqlldr,观察产生的归档数量为30个100M的归档,与31个100M的归档处于同一数量级.

最后关于一楼的分批提交问题，我的看法是的确不是commit,因为表里面无数据；同样，在rows参数的说明中，oracle特意使用save,而不是commit，不知楼主的看法如何？

你好,very sorry for reply to you.
你的数据库应该是在archive mode下吧.
noarchive mode就不用care 表是nologging或logging的状态.


我也不明白save是为何物,不过save就不能rollback,但其它session是看不到save的数据.

jayli426

Data Save Versus Commit
In a conventional load, ROWS is the number of rows to read before a commit operation. A direct load data save is similar to a conventional load commit, but it is not identical.

The similarities are as follows:

A data save will make the rows visible to other users.

Rows cannot be rolled back after a data save.

The major difference is that in a direct path load data save, the indexes will be unusable (in Index Unusable state) until the load completes.

我觉得这句话 可能是错的
"A data save will make the rows visible to other users."

因为从大家的试验结果和我自身的结果来看,这个应当是不对的
在装载过程种数据保存之后对rows应当是不可见的
直到整个装载完成才会