oracle数据库速度测试,【原创】验证在Oracle数据库中操纵数据的各种方法的速度...

机器配置：组装的PC SERVER，4AMD CPU，8G内存，4块SCSI硬盘。Oracle 10.2.0.4。

试验目的：在244上，对xxx_flow表进行复制、更新，验证在Oracle数据库中操纵数据的各种方法的速度。共35,629,784条记录。

直接建表：

create

/*+parallel*/table tt1 as select /*+parallel(pf, 4)*/ * from xxx_flow pf;

结果：

第一次：126.297秒，约每秒写入28.2W条数据。

第二次：149.109秒，约每秒写入23.9W条数据。

使用BULK COLLECT批量处理：

create table tt1 as select * from xxx_flow

where 1 = 2;

--以下代码还有一个可以参考的地方：没有根据表结构声明太多的类型及变量。

declare

cursor cur_temp is

select /*+parallel(pf, 4)*/* from xxx_flow pf;

type t_person_flow is table of cur_temp%rowtype;--注意：这句很关键，直接引用上面的游标来定义类型

v_person_flow t_person_flow;

begin

open cur_temp;

loop

fetch cur_temp bulk collect into

v_person_flow

limit 1000;

forall i in 1..v_person_flow.last

insert /*+append nologging*/ into tt1

values v_person_flow(i); --注意：这里values后没有扩号

exit when cur_temp%notfound;

end loop;

close cur_temp;

end;

drop table tt1;

结果：

第一次：768.828秒。约每秒写入4.6W条数据。

第二次：505.953秒。约每秒写入7.0W条数据。(加hint，把limit从200改为1000)

如果在目标表上建立索引后再插入，哪怕只建立一个主键约束，耗时也将*2还多。

在复制时，DDL对比DML语句，在速度上还有很有优势的。

更新10%、20%、50%、100%的数据：

/*

建表并建立索引

*/

create

/*+parallel*/table tt1 as select /*+parallel(pf, 4)*/ * from xxx_flow pf; --

147s

alter table tt1

add constraint PK_tt1_PERSON_FLOW primary key (SERIAL_NUMBER)

using index

tablespace NNC_INDEX01

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 64M

minextents 1

maxextents unlimited

);-- 102s

create index I_TT1_PERSON_FLOW_TT

on TT1 (PK_PERSON_ACCOUNT, PK_TRADETYPE)

tablespace NNC_INDEX01

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 128M

minextents 1

maxextents unlimited

);-- 200s

create index I_TT1_PERSON_FLOW_VN on

TT1 (VOUCHER_NUMBER)

tablespace NNC_INDEX01

pctfree 10

initrans 2

maxtrans 255

storage

(

initial 128M

minextents 1

maxextents unlimited

);-- 88s

--更新数据(10%)

declare

i integer;

--从tt1表中，取10%的数据

cursor cur_temp is

select serial_number

from tt1

where rownum < cast((35629784 / 10) as integer);

type t_serial_number is table of tt1.serial_number%type index by

pls_integer;

v_serial_number t_serial_number;

begin

open cur_temp;

loop

fetch cur_temp bulk collect into v_serial_number

limit 1000;

forall i in 1..v_serial_number.count

update /*+nologging*/ tt1

set occur_cash = 1, occur_unit = 1,

cash_balance =1 , balance = 1

where serial_number = v_serial_number(i);

exit when cur_temp%notfound;

end loop;

close cur_temp;

end;

更新10%的数据，共更新356W数据，耗时86.515秒，平均约每秒更新4.1W条。

更新20%的数据，共更新712W数据，耗时161.485秒，平均约每秒更新4.4W条。

更新50%的数据，共更新1781W数据，耗时427.766秒，平均约每秒更新4.1W条。

更新100%的数据，共更新3562W数据，耗时1118.063秒，平均约每秒更新3.2W条。

在更新过程中，游标只读取1个字段，更新4个字段，由于该表有3个索引，虽然更新的字段都不在索引上，发现写入的数据量是读取的数据量的20倍以上。(数据库归档日志打开)

以上只测试单表，并且顺序读取数据，然后根据唯一索引进行更新，如果有多表关联等要求，情况会更复杂。所以实际过程中，速度应该达不到4W/秒。

drop table

tt1;

使用中间表进行更新，并通过判断更新的数据量，进行对应的DDL操作：

--更新10%的数据。

--建立临时表

create

global temporary table temp_tt1 as select * from tt1 where 1 = 2; -- 0.2s

--插入数据

insert

into temp_tt1

select *

from tt1

where rownum < cast((35629784 / 10) as

integer);-- 12s

--更新临时表中的数据

update /*+parallel(temp_tt1, 4) nologging */

temp_tt1

set occur_cash = 1, occur_unit = 1,

cash_balance =1 , balance = 1;-- 60s

分支一：

--删除源表中的数据

delete

from tt1 t1 where exists (select 1 from temp_tt1 temp1 where t1.serial_number =

temp1.serial_number);-- 163s

--把临时表中的数据插入到源表中

insert

/*+parallel(tt1, 4)*/ into tt1 select /*+parallel(temp_tt1, 4)*/* from

temp_tt1;--240s

分支二：

--使用临时表数据更新目标表

update tt1 t1 set (occur_cash,

occur_unit, cash_balance, balance)

= (

select occur_cash, occur_unit,

cash_balance, balance

from temp_tt1 temp

where t1.serial_number =

temp.serial_number

)

where exists (

select 1

from temp_tt1 temp

where t1.serial_number = temp.serial_number

);--约30分钟未完成，取消。

用上述方式更新时，硬盘IO没有规律，与其他方式相比，大部分时间都几乎“没有”IO，极慢。

--改为以下方式：

update /*+parallel(t1, 4) nologging*/ tt1

t1 set (occur_cash, occur_unit, cash_balance, balance)

= (

select /*+parallel(temp,

4)*/occur_cash, occur_unit, cash_balance, balance

from temp_tt1 temp

where t1.serial_number =

temp.serial_number

)

where exists (

select /*+parallel(temp, 4)*/1

from temp_tt1 temp

where t1.serial_number =

temp.serial_number

);--无效果，用大表(即使是临时表，本例有365W数据)更新更大的表时，直接用SQL实现在性能上是无法接受的。

commit;

drop table

temp_tt1;