oracle trigger performance,Oracle Trigger 性能测试

前一段对用户表做了个触发器:TRI_USER,当表有更新操作时触发,触发字段:UPDATE_TIME,记录记录修改日期时间.

USER表结构

SQL > DESC USER(

user_id number(20) not null primary key,

user_name varchar(128) not null,

user_password varchar(128) not null,

user_mailbox varchar(128),

user_flag number(2),

user_zip number(10),

user_address varchar(255),

user_address2 varchar(255),

user_mobilephone number(20),

regist_time date,

update_tim date)

当前数据量三千万条,使用存储过程分三次插入/更新用户表,日志记录过程执行时间.操作数据10万条.

1.插入过程

declare

v_i number;

v_time varchar2(10);

begin

v_i:=0;

select to_char(sysdate,'sssss')

into v_time

from dual;

dbms_output.put_line(v_time);

for v_i in 1..100000 loop

insert into user (user_id,user_name,user_password,user_mailbox,user_flag,user_zip,user_address,user_address2,user_mobilephone,regist_time)

values(v_i,'testtrigger','123456','test@test.com',1,'100000','address1','address2','13000000000',to_date('2006-06-06 10:10:10','yyyy-mm-dd hh24:mi:ss'));

commit;

end loop;

select to_char(sysdate,'sssss')

into v_time

from dual;

dbms_output.put_line(v_time);

end;

/

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

2.更新过程

declare

v_i number;

v_time varchar2(10);

begin

v_i:=0;

select to_char(sysdate,'sssss')

into v_time

from dual;

dbms_output.put_line(v_time);

dbms_output.put_line(v_xing);

for v_i in 1..100000 loop

update usr1

set user_name='aaaaaa',

usr_flag=0,

regist_time=to_date('2006-06-12 23:36:53','yyyy-mm-dd hh24:mi:ss')

where usr_onlyid=v_i;

commit;

end loop;

select to_char(sysdate,'sssss')

into v_time

from dual;

dbms_output.put_line(v_time);

end;

/

两表插入相差1/7,更新相差1/17.

[@more@]