产生undo的测试

王朝other·作者佚名 2008-05-19

在优化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.