如何在十分钟内插入1亿条记录到Oracle数据库（转）

这里提供一种方法，使用 APPEND 提示，使得十分钟内插入上亿数据成为可能。

-- Create table

create table TMP_TEST_CHAS_LEE

(

f01 VARCHAR2(20),

f02 NUMBER(10) not null,

f03 VARCHAR2(21),

f04 VARCHAR2(21),

f05 NUMBER,

f06 NUMBER(20)

);

--创建一个临时表，用于提供序列号

CREATE GLOBAL TEMPORARY table t_sequence_num(

sequenceNum number(8) not null

)

ON COMMIT PRESERVE ROWS;

--开始插入数据

begin

--先生成1万个序号

delete from t_sequence_num;

for i in 0..9999 loop

insert into t_sequence_num(sequenceNum) values(i);

end loop;

 

--使用APPEND提示，每次1万条，进行数据插入

for i in 1..10 loop

insert /*+ append */ into TMP_TEST_CHAS_LEE

(f01, f02, f03, f04, f05, f06)

select

8613800000000 + i * 10000 + t_sequence_num.sequencenum as MSISDN,

'12106000',

0,

'20120312072000',

'500231891000',

null

from t_sequence_num;

 

--每批次必须要提交一次

commit;

 

end loop;

end;

/

可以看出，这种方法的关键是使用了 APPEND 提示，也就是使用了 Direct Path Insert.

效果非常惊人，上亿的数据，十分钟左右就搞定了，也就是说，每秒钟插入了超过10万条以上的记录。

这个方法很简单，就是要利用 APPEND提示，方法就是需要有个序列表，来辅助数据生成。

性能测试人员不用耗费大量的时间来等待测试数据生成了。

需要注意的问题：

1. 插入数据时，表上不要建立索引

2. 可以在数据插入完毕后，通过 nologging 和 parallel 来创建索引

3. 这里默认是1万条记录提交一次，可以改的更大，应该会更快
4. APPEND方式插入数据后，必须要提交后才能对表进行其它操作

5. 生成的数据的每个字段都是可以根据自己的需要灵活产生的

_______________________________________________________

针对append提示，深入了解一下的。

http://www.askoracle.org/oracle/performance/717.html

关于insert /*+ append */我们需要注意以下三点：

a、非归档模式下，只需append就能大量减少redo的产生；归档模式下，只有append+nologging才能大量减少redo。
b、insert /*+ append */ 时会对表加锁（排它锁），会阻塞表上的除了select以外所有DML语句；传统的DML在TM enqueue上使用模式3（row exclusive），其允许其他DML在相同的模式上获得TM enqueue。但是直接路径加载在TM enqueue使用模式6（exclusive），这使其他DML在直接路径加载期间将被阻塞。
c、insert /*+ append */ 直接路径加载，速度比常规加载方式快。因为是从HWM的位置开始插入，也许会造成空间浪费。

1.环境介绍

数据库版本：

SQL> select * from v$version;
BANNER
---------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

网上说测试时不要使用auto trace来查看redo size，这个值是有偏差的.建议建立一个视图：

SQL> create or replace view redo_size as
select value
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
-- 视图已创建。

2.示例演示：

2.1 非归档模式

SQL> archive log list
数据库日志模式             非存档模式
自动存档             禁用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     95
当前日志序列           97

2.1.1 nologging表

SQL> create table test1 nologging as select * from dba_objects where 1=0;
表已创建。
  
SQL> select * from redo_size;
     VALUE
----------
  25714940
  
SQL> insert into test1 select * from dba_objects;
已创建72753行。
  
SQL> select * from redo_size;
     VALUE
----------
  34216916
  
SQL> insert /*+ APPEND */  into test1 select * from dba_objects;
已创建72753行。
  
SQL> select * from redo_size;
     VALUE
----------
  34231736
  
SQL> select (34231736-34216916) redo_append , (34216916-25714940) redo_normalfrom dual;
REDO_APPEND REDO_NORMAL
----------- -----------
      14820     8501976

2.1.2 logging表：

SQL> create table test2 as select * from dba_objects where 1=0;
  
表已创建。
  
SQL> select * from redo_size;
     VALUE
----------
  34273348
  
SQL> insert into test2 select * from dba_objects;
  
已创建72754行。
  
SQL> select * from redo_size;
     VALUE
----------
  42775336
  
SQL> insert /*+ APPEND */  into test2 select * from dba_objects;
已创建72754行。
  
SQL> select * from redo_size;
     VALUE
----------
  42790156
  
SQL> select (42790156-42775336) redo_append , (42775336-34273348) redo_normalfrom dual;
REDO_APPEND REDO_NORMAL
----------- -----------
      14820     8501988

2.2归档模式下：

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE例程已经关闭。
SQL> startup mount
ORACLE例程已经启动。
  
Total System Global Area  477073408 bytes
Fixed Size                  1337324 bytes
Variable Size             293603348 bytes
Database Buffers          176160768 bytes
Redo Buffers                5971968 bytes
数据库装载完毕。
SQL> alter database archivelog;
数据库已更改。
  
SQL> alter database open;
数据库已更改。
  
SQL> archive log list
数据库日志模式            存档模式
自动存档             启用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     95
下一个存档日志序列   97
当前日志序列           97

2.2.1 nologging表

SQL> select * from redo_size;
    VALUE
----------
     17936
  
SQL> insert into test1 select * from dba_objects;
已创建72754行。
  
SQL> select * from redo_size;
     VALUE
----------
   8490972
  
SQL> insert /*+ APPEND */  into test1 select * from dba_objects;
已创建72754行。
  
SQL> select * from redo_size;
     VALUE
----------
   8506164
  
SQL> select (8506164-8490972) redo_append , (8490972-17936) redo_normal fromdual;
REDO_APPEND REDO_NORMAL
----------- -----------
      15192     8473036

2.2.2 logging表

SQL> select * from redo_size;
     VALUE
----------
   8506780
  
SQL> insert into test2 select * from dba_objects;
已创建72754行。
  
SQL> select * from redo_size;
     VALUE
----------
  16979516
  
SQL> insert /*+ APPEND */  into test2 select * from dba_objects;
已创建72754行。
  
SQL> select * from redo_size;
     VALUE
----------
  25518172
  
SQL> select (25518172-16979516) redo_append , (16979516-8506780) redo_normalfrom dual;
REDO_APPEND REDO_NORMAL
----------- -----------
    8538656     8472736

在归档模式下,对于常规表的insert append产生和insert同样的redo
此时的insert append实际上并不会有性能提高.
但是此时的append是生效了的。

3.insert /*+ append */会阻塞除select以外的DML语句，direct-path insert操作是单独一个事务。

SQL> select count(*) from test2;
  COUNT(*)
----------
    291016
  
SQL> insert into test2 select * from dba_objects;
已创建72754行。
  
SQL> select count(*) from test2;
  COUNT(*)
----------
    363770
  
SQL> insert /*+ APPEND */ into test2 select * from dba_objects;
已创建72754行
  
同一个session下：
  
SQL> select count(*) from test2;
select count(*) from test2
*
第 1 行出现错误:
ORA-12838: 无法在并行模式下修改之后读/修改对象
  
SQL> commit;
提交完成。
  
SQL> select count(*) from test2;
  COUNT(*)
----------
    436524
  
SQL> insert /*+ APPEND */ into test2 select * from dba_objects;
已创建72754行。
  
SQL> shutdown immediate
ORA-01097: 无法在事务处理过程中关闭 - 请首先提交或回退
  
SQL> select  * from v$mystat where rownum<2;
  
       SID STATISTIC#      VALUE
  
---------- ---------- ----------
  
       224          0          1
  
SQL> select KADDR,TYPE,LMODE from v$lock where sid=224;
  
KADDR            TY      LMODE
---------------- -- ----------
0000000071BAE180 TM          6
0000000070CB11B8 TX          6

另外开启一个会话，就会发现只能select，其他DML全部阻塞。

网上搜集的资料

在使用了append选项以后，insert数据会直接加到表的最后面，而不会在表的空闲块中插入数据。

使用append会增加数据插入的速度。
/*+APPEND*/的作用是在表的高水位上分配空间,不再使用表的extent中的空余空间
append 属于direct insert,归档模式下append+table nologging会大量减少日志,非归档模式append会大量减少日志,append方式插入只会产生很少的undo
不去寻找 freelist 中的free block , 直接在table HWM 上面加入数据。

使用append提示进行insert叫做直接路径加载插入，使用这种提示因为系统不去查找freelist链表中的空闲块，直接在高水标记位以上插入数据，因此速度比较快。但是也应该注意直接路径加载插入有一些不同：
1.它不记录日志，因此一旦插入的数据在没有保存回磁盘的时候发生掉电之类的状况插入的数据不能重做。
2.它比较浪费磁盘空间，有心的同学可以做个简单的实验，创建个空表，用insert 插一些数据，然后delete掉，然后再用insert 插一些数据，再delete掉，这样反复操作，最终用delete把表清空，然后用下面的语句查一下表的大小，可以看到一个空表占了可怕的磁盘空间！
Select round(Sum(bytes)/1024,2)||‘KB’ as sizes
From dba_extents
where segment_name='表名'
Group By owner,segment_name,segment_type;
3.它在不同的Oracle版本中所加的锁是不一样的，我在两个版本中做了实验。
在Oracle Database 10g Enterprise Edition Release 10.2.0.1.0中，如果插入操作的语法是
insert into XXX values(XXX);
那么系统加的是row exclusive锁，也就是正常insert所加的行级锁，但是如果插入操作的语法是
insert into XXX select XXX from XXX;
那么系统加的是exclusive锁，相当于表级锁，加表级锁意味着在本会话没有commit的时候其他任何会话都不能再进行insert,update,delete操作。不过这里看起来更严重，就连自己的会话也不能再进行任何insert,update,delete操作，否则就会提示：
ETL@RACTEST> insert into ljntest select a from ljntest2;

1 row created.

Elapsed: 00:00:00.01
ETL@RACTEST> insert into ljntest select a from ljntest2;
insert into ljntest select a from ljntest2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
在Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 中就简单一些了，不管是哪种语法，一概加的是exclusive锁！
所以大家在使用直接路径加载插入时或是测试开发的代码时一定要注意这三点。

posted @ 2014-12-03 10:16 milkty 阅读(2874) 评论(0) 收藏举报

刷新页面返回顶部

milkty

芝兰生于深谷，不以无人而不芳。君子修身养德，不以穷困而改志。

如何在十分钟内插入1亿条记录到Oracle数据库（转）

公告