在优化sql的时候也要考虑undo产生的数目了； 如果可能，使用 insert select ，比insert row by row要快好多。
SQL> create table t(id number);
Table created.
SQL> insert into t select object_id from dba_objects where rownum<=1000;
1000 rows created.
SQL> select used_ublk,used_urec from v$transaction;
USED_UBLK USED_UREC
---------- ----------
1 5
SQL> commit; --- insert select 产生较少的undo
Commit complete.
SQL> begin
2 for i in 1..1000 loop
3 insert into t values(i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select used_ublk,used_urec from v$transaction;
USED_UBLK USED_UREC --- insert row by row产生较多的undo
---------- ----------
9 1000
SQL> truncate table t;
Table truncated.
SQL> create index t_idx on t(id);
Index created.
SQL> insert into t select object_id from dba_objects where rownum<=1000 ;
1000 rows created.
SQL> select used_ublk,used_urec from v$transaction;
USED_UBLK USED_UREC
---------- ----------
6 244
commit;
SQL> begin
2 for i in 1..1000 loop
3 insert into t values(i); end loop;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> select used_ublk,used_urec from v$transaction;
USED_UBLK USED_UREC
---------- ----------
20 2000
SQL> --- 过多的索引产生不必要的undo;索引dml操作相当于delete 然后 insert，都会产生undo；同时维护索引产生的redo数目也不可忽视。
什么时候set autotrace on可以包括 undo size.
