產生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.
