产生undo的测试
2024-07-21 02:34:16
供稿：网友
在优化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 5SQL> commit; --- insert select 产生较少的undoCommit 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 1000SQL> 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 244commit;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 2000SQL> --- 过多的索引产生不必要的undo;索引dml操作相当于delete 然后 insert，都会产生undo；同时维护索引产生的redo数目也不可忽视。 什么时候set autotrace on可以包括 undo size.
