1、在循环中提交
这样做的两个主要原因是:
频繁的提交大量小事务比处理和提交一个大事务更快,也更高效。
没有足够的undo空间。
a、性能影响
u1@ORCL> drop table t;
表已删除。
u1@ORCL> create table t as select * from all_objects;
表已创建。
u1@ORCL> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL 过程已成功完成。
u1@ORCL> variable n number
u1@ORCL> exec :n := dbms_utility.get_cpu_time;
PL/SQL 过程已成功完成。
u1@ORCL> update t set object_name = lower(object_name);
已更新71069行。
u1@ORCL> exec dbms_output.put_line( (dbms_utility.get_cpu_time-:n) || ' cpu hsecs...' );
78
cpu hsecs...
PL/SQL 过程已成功完成。
大多数人更喜欢像下面这样做:
u1@ORCL> drop table t;
表已删除。
u1@ORCL> create table t as select * from all_objects;
表已创建。
u1@ORCL> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL 过程已成功完成。
u1@ORCL> exec :n := dbms_utility.get_cpu_time;
PL/SQL 过程已成功完成。
u1@ORCL> begin
2 for x in ( select rowid rid, object_name, rownum r
3 from t )
4 loop
5 update t
6 set object_name = lower(x.object_name)
7 where rowid = x.rid;
8 if ( mod(x.r,100) = 0 ) then
9 commit;
10 end if;
11 end loop;
12 commit;
13 end;
14 /
PL/SQL 过程已成功完成。
u1@ORCL> exec dbms_output.put_line( (dbms_utility.get_cpu_time-:n) || ' cpu hsecs...' );
341
cpu hsecs...
PL/SQL 过程已成功完成。
对于这个小例子,倘若在循环中频繁的提交,就会慢上好几倍。
b、snapshot old错误
u1@ORCL> drop table t;
表已删除。
u1@ORCL> create table t as select * from all_objects;
表已创建。
u1@ORCL> create index t_idx on t(object_name);
索引已创建。
u1@ORCL> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
PL/SQL 过程已成功完成。
u1@ORCL> create undo tablespace undo_small datafile '+DG' size 10m reuse autoextend off;
表空间已创建。
u1@ORCL> alter system set undo_tablespace = undo_small;
系统已更改。
u1@ORCL> begin
2 for x in ( select /*+ INDEX(t t_idx) */ rowid rid, object_name, rownum r
3 from t
4 where object_name > ' ' )
5 loop
6 update t
7 set object_name = lower(x.object_name)
8 where rowid = x.rid;
9 if ( mod(x.r,100) = 0 ) then
10 commit;
11 end if;
12 end loop;
13 commit;
14 end;
15 /
begin
*
第 1 行出现错误:
ORA-01555: 快照过旧: 回退段号 (名称为 "") 过小
ORA-06512: 在 line 2
这里的关键是,无法通过频繁提交来“节省”undo空间——你会需要这些undo信息。
2、自治事务
自治事务允许你创建一个“事务中的事务”,它能独立于其父事务提交或回滚。利用自治事务,可以挂起当前执行的事务,开始一个新事务,完成一些工作,然后提交或回滚,所有这些都不影响当前执行事务的状态。
u1@ORCL> create table t ( msg varchar2(25) );
表已创建。
u1@ORCL> create or replace procedure Autonomous_Insert
2 as
3
pragma autonomous_transaction;
4 begin
5 insert into t values ( 'Autonomous Insert' );
6 commit;
7 end;
8 /
过程已创建。
u1@ORCL> create or replace procedure NonAutonomous_Insert
2 as
3 begin
4 insert into t values ( 'NonAutonomous Insert' );
5 commit;
6 end;
7 /
过程已创建。
注意这里使用了pragma autonomous_transaction。这个指令告诉数据库:执行这个过程时要作为一个新的自治事务来执行,而且独立于其父事务。下面来观察PL/SQL代码匿名块中非自治事务的行为:
u1@ORCL> begin
2 insert into t values ( 'Anonymous Block' );
3 NonAutonomous_Insert;
4 rollback;
5 end;
6 /
PL/SQL 过程已成功完成。
u1@ORCL> select * from t;
MSG
-------------------------
Anonymous Block
NonAutonomous Insert
可以看到,匿名块执行的工作(insert)由NonAutonomous_Insert过程提交。两个数据行都已提交,所以rollback命令没有什么可以回滚。
u1@ORCL> delete from t;
已删除2行。
u1@ORCL> commit;
提交完成。
u1@ORCL> begin
2 insert into t values ( 'Anonymous Block' );
3 Autonomous_Insert;
4 rollback;
5 end;
6 /
PL/SQL 过程已成功完成。
u1@ORCL> select * from t;
MSG
-------------------------
Autonomous Insert
在此,只有自治事务中完成并已提交的工作会持久保留。匿名块中完成的insert被rollback语句回滚。
总结一下,如果在一个“正常”的过程中commit,它不仅会持久保留自己的工作,也会使该会话中未完成的工作成为永久性的。不过,如果一个自治事务过程中完成commit,只会让这个过程本身的工作成为永久性的。
tom:“从我的经验看,自治事务唯一的合法使用,即记录错误日志或信息型消息,从而可以独立于父事务完成提交。”