APPEND,bulk collect,正常插入比較

在最佳化某個庫歸檔過多專案時，我建議開發人員用bulk collect,代替正常的批次插入
最後在檢查他們最佳化的時候，發現他們使用append代替我建議的bulk collect,，同樣也達到了目的。
       針對他們之間的產生歸檔量的效果，我做了簡單的測試。
SQL> create table david_forall tablespace users as select * from dba_objects;

Table created.

SQL> create table david_forall2 tablespace users as select * from dba_objects where rownum=0;

Table created.

SQL> select count(*) from david_forall;

  COUNT(*)
----------
     89880

SQL> select count(*) from david_forall2;

  COUNT(*)
----------
         0

SQL> set timing on;
SQL> select  st.name,ss.value from v$mystat ss, v$statname st
  2     where ss.statistic# = st.statistic#                
  3     and st.name in ('redo size','undo change vector size','CPU used by this session');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                150
redo size                                                          10851424
undo change vector size                                               26288

Elapsed: 00:00:00.02
SQL>   begin
  2 
  3          insert /*+ append */  into david_forall2 select * from maclean_forall;
  4       commit;
  5 
  6    end;
  7    /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.21
SQL> select  st.name,ss.value from v$mystat ss, v$statname st
  2      where ss.statistic# = st.statistic#                
  3      and st.name in ('redo size','undo change vector size','CPU used by this session');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                168
redo size                                                          21653724
undo change vector size                                               38236

Elapsed: 00:00:00.01
SQL> declare
  2       type recstartyp is table of david_forall%rowtype index by BINARY_INTEGER;
  3       rec_tab recstartyp;
  4       cursor temp is select * from david_forall;
  5    begin
  6       open temp;
  7       fetch temp bulk collect into rec_tab;
  8       FORALL i in rec_tab.first..rec_tab.last
  9          insert  into david_forall2 values rec_tab(i);
 10       commit;
 11       close temp;
 12    end;
 13    /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.15
SQL> select  st.name,ss.value from v$mystat ss, v$statname st
  2      where ss.statistic# = st.statistic#                
  3      and st.name in ('redo size','undo change vector size','CPU used by this session');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                272
redo size                                                          32286160
undo change vector size                                              383664

Elapsed: 00:00:00.00
SQL> declare
  2    
  3        cursor temp is select * from david_forall;
  4     begin
  5    
  6       for i in temp loop
  7       insert into david_forall2 values i;
  8       end loop;
  9       commit;
 10     end;
 11     /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.93
SQL> select  st.name,ss.value from v$mystat ss, v$statname st
  2      where ss.statistic# = st.statistic#                
  3      and st.name in ('redo size','undo change vector size','CPU used by this session');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
CPU used by this session                                                945
redo size                                                          66555352
undo change vector size                                             6490104

Elapsed: 00:00:00.00


SQL> select 10851424,21653724-10851424,32286160-21653724,66555352-32286160 from dual;

  10851424 21653724-10851424 32286160-21653724 66555352-32286160
---------- ----------------- ----------------- -----------------
  10851424          10802300          10632436          34269192

Elapsed: 00:00:00.00

可以看出/*+ APPEND */和bulk collect所產生的日誌量是差不多的
/*+ APPEND */  redo  10802300         
bulk collect   redo  10632436         
正常插入        redo   34269192


為此，我們需要訪問兩個動態效能檢視：
  V$MYSTAT，其中有會話的提交資訊。
  V$STATNAME，這個檢視能告訴我們 V$MYSTAT 中的每一行表示什麼（所檢視的統計名）。

   生成的 redo 越多，操作花費的時間就越長，整個系統也會越慢。你不光在影響你自己的會話，還會影響每一個會話。redo管理是資料庫中的一個序列點。任何 Oracle 例項都只有一個 LGWR，最終所有事務都會歸於 LGWR，要求這個程式管理它們的 redo，