一、實驗說明
作業系統：redhat 5.4
資料庫:oracle 11g r2
二、在Noarchivelog模式中的比較：
1 SQL> conn jack/jack 2 Connected. 3 SQL> drop table t purge; 4 5 Table dropped. 6 7 SQL> conn /as sysdba 8 Connected. 9 SQL> archive log list; 10 Database log mode No Archive Mode --Noarchivelog模式 11 Automatic archival Disabled 12 Archive destination USE_DB_RECOVERY_FILE_DEST 13 Oldest online log sequence 5 14 Current log sequence 7 15 SQL> conn jack/jack 16 Connected. 17 SQL> create table t as select * from dba_objects; 18 19 Table created. 20 21 SQL> set autotrace traceonly; 22 SQL> insert into t select * from t; 23 24 72544 rows created. 25 26 27 Execution Plan 28 ---------------------------------------------------------- 29 Plan hash value: 1601196873 30 31 --------------------------------------------------------------------------------- 32 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 33 --------------------------------------------------------------------------------- 34 | 0 | INSERT STATEMENT | | 285K| 56M| 590 (1)| 00:00:08 | 35 | 1 | LOAD TABLE CONVENTIONAL | T | | | | | 36 | 2 | TABLE ACCESS FULL | T | 285K| 56M| 590 (1)| 00:00:08 | 37 --------------------------------------------------------------------------------- 38 39 Note 40 ----- 41 - dynamic sampling used for this statement (level=2) 42 43 44 Statistics 45 ---------------------------------------------------------- 46 586 recursive calls 47 9343 db block gets 48 2449 consistent gets 49 1033 physical reads 50 8428664 redo size 51 673 bytes sent via SQL*Net to client 52 601 bytes received via SQL*Net from client 53 3 SQL*Net roundtrips to/from client 54 2 sorts (memory) 55 0 sorts (disk) 56 72544 rows processed 57 58 SQL> set autotrace off; 59 SQL> drop table t purge; 60 61 Table dropped. 62 63 SQL> create table t as select * from dba_objects; 64 65 Table created. 66 67 SQL> set autotrace traceonly; 68 SQL> insert /*+ append */ into t select * from t; 69 70 72544 rows created. 71 72 73 Execution Plan 74 ---------------------------------------------------------- 75 ERROR: 76 ORA-12838: cannot read/modify an object after modifying it in parallel 77 78 79 SP2-0612: Error generating AUTOTRACE EXPLAIN report 80 81 Statistics 82 ---------------------------------------------------------- 83 594 recursive calls 84 1292 db block gets 85 1218 consistent gets 86 1033 physical reads 87 22472 redo size 88 662 bytes sent via SQL*Net to client 89 615 bytes received via SQL*Net from client 90 3 SQL*Net roundtrips to/from client 91 2 sorts (memory) 92 0 sorts (disk) 93 72544 rows processed
小結：
2.1、append減少資料塊讀的數量，普通插入db block gets為9343，加/*+ append */為1292；
2.2、append減少一致讀的數量，普通插入consistent gets為2449，加/*+ append */為1218；
2.3、append減少回滾的寫入數量，普通插入redo size為8428664，加/*+ append */為22472。
三、在Arachivelog模式下比較(前提是不使用alter table t nologging;)
1 SQL> conn /as sysdba 2 Connected. 3 SQL> shutdown immediate; 4 Database closed. 5 Database dismounted. 6 ORACLE instance shut down. 7 SQL> startup mount; 8 ORACLE instance started. 9 10 Total System Global Area 372449280 bytes 11 Fixed Size 1336624 bytes 12 Variable Size 146803408 bytes 13 Database Buffers 218103808 bytes 14 Redo Buffers 6205440 bytes 15 Database mounted. 16 SQL> alter database archivelog; 17 18 Database altered. 19 20 SQL> alter database open; 21 22 Database altered. 23 24 SQL> archive log list; 25 Database log mode Archive Mode 26 Automatic archival Enabled 27 Archive destination USE_DB_RECOVERY_FILE_DEST 28 Oldest online log sequence 5 29 Next log sequence to archive 7 30 Current log sequence 7 31 SQL> conn jack/jack 32 Connected. 33 SQL> drop table t purge; 34 35 Table dropped. 36 37 SQL> create table t as select * from dba_objects; 38 39 Table created. 40 41 SQL> set linesize 160; 42 SQL> set autotrace traceonly; 43 SQL> insert into t select * from t; 44 45 72544 rows created. 46 47 48 Execution Plan 49 ---------------------------------------------------------- 50 Plan hash value: 1601196873 51 52 --------------------------------------------------------------------------------- 53 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 54 --------------------------------------------------------------------------------- 55 | 0 | INSERT STATEMENT | | 285K| 56M| 590 (1)| 00:00:08 | 56 | 1 | LOAD TABLE CONVENTIONAL | T | | | | | 57 | 2 | TABLE ACCESS FULL | T | 285K| 56M| 590 (1)| 00:00:08 | 58 --------------------------------------------------------------------------------- 59 60 Note 61 ----- 62 - dynamic sampling used for this statement (level=2) 63 64 65 Statistics 66 ---------------------------------------------------------- 67 586 recursive calls 68 9341 db block gets 69 2395 consistent gets 7 1033 physical reads 71 8428488 redo size 72 679 bytes sent via SQL*Net to client 73 601 bytes received via SQL*Net from client 74 3 SQL*Net roundtrips to/from client 75 2 sorts (memory) 76 0 sorts (disk) 77 72544 rows processed 78 79 SQL> set autotrace off; 80 SQL> drop table t purge; 81 82 Table dropped. 83 84 SQL> create table t as select * from dba_objects; 85 86 Table created. 87 88 SQL> set autotrace traceonly; 89 SQL> insert /*+ append */ into t select * from t; 90 91 72544 rows created. 92 93 94 Execution Plan 95 ---------------------------------------------------------- 96 ERROR: 97 ORA-12838: cannot read/modify an object after modifying it in parallel 98 99 100 SP2-0612: Error generating AUTOTRACE EXPLAIN report 101 102 Statistics 103 ---------------------------------------------------------- 104 594 recursive calls 105 1292 db block gets 106 1218 consistent gets 107 1033 physical reads 108 8536868 redo size 109 665 bytes sent via SQL*Net to client 110 615 bytes received via SQL*Net from client 111 3 SQL*Net roundtrips to/from client 112 2 sorts (memory) 113 0 sorts (disk) 114 72544 rows processed
小結：
3.1、append減少資料塊讀的數量，普通插入db block gets為9341，加append為1292；
3.2、append減少一致讀的數量，普通插入consistents gets為2395，加append為1218；
3.3、回滾段的數量沒有什麼變化。
四、在Arachivelog模式下比較(前提是使用alter table t nologging;)
1 SQL> conn /as sysdba 2 Connected. 3 SQL> archive log list; 4 Database log mode Archive Mode 5 Automatic archival Enabled 6 Archive destination USE_DB_RECOVERY_FILE_DEST 7 Oldest online log sequence 7 8 Next log sequence to archive 9 9 Current log sequence 9 10 11 SQL> drop table t purge; 12 13 Table dropped. 14 15 SQL> create table t as select * from dba_objects; 16 17 Table created. 18 19 SQL> alter table t nologging; 20 21 Table altered. 22 23 SQL> set autotrace traceonly; 24 SQL> insert into t select * from t; 25 26 72544 rows created. 27 28 29 Execution Plan 30 ---------------------------------------------------------- 31 Plan hash value: 1601196873 32 33 --------------------------------------------------------------------------------- 34 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 35 --------------------------------------------------------------------------------- 36 | 0 | INSERT STATEMENT | | 285K| 56M| 590 (1)| 00:00:08 | 37 | 1 | LOAD TABLE CONVENTIONAL | T | | | | | 38 | 2 | TABLE ACCESS FULL | T | 285K| 56M| 590 (1)| 00:00:08 | 39 --------------------------------------------------------------------------------- 40 41 Note 42 ----- 43 - dynamic sampling used for this statement (level=2) 44 45 46 Statistics 47 ---------------------------------------------------------- 48 766 recursive calls 49 9347 db block gets 5 2352 consistent gets 51 1033 physical reads 52 8434036 redo size 53 681 bytes sent via SQL*Net to client 54 601 bytes received via SQL*Net from client 55 3 SQL*Net roundtrips to/from client 56 6 sorts (memory) 57 0 sorts (disk) 58 72544 rows processed 59 60 SQL> drop table t purge; 61 62 Table dropped. 63 64 SQL> set autotrace off; 65 SQL> create table t as select * from dba_objects; 66 67 Table created. 68 69 SQL> alter table t nologging; 70 71 Table altered. 72 73 SQL> set autotrace traceonly; 74 SQL> insert /*+ append */ into t select * from t; 75 76 72544 rows created. 77 78 79 Execution Plan 80 ---------------------------------------------------------- 81 ERROR: 82 ORA-12838: cannot read/modify an object after modifying it in parallel 83 84 85 SP2-0612: Error generating AUTOTRACE EXPLAIN report 86 87 Statistics 88 ---------------------------------------------------------- 89 774 recursive calls 90 1292 db block gets 91 1237 consistent gets 92 1033 physical reads 93 22472 redo size 94 666 bytes sent via SQL*Net to client 95 615 bytes received via SQL*Net from client 96 3 SQL*Net roundtrips to/from client 97 6 sorts (memory) 98 0 sorts (disk) 99 72544 rows processed
小結：
4.1、append減少資料塊的數量，普通插入db block gets為9347，加append為1292；
4.2、append減少一致讀的數量，普通插入consistent gets為2352，加append為1237；
4.3、append減少回滾段的寫入數量，普通插入redo size為8434036，加append為22472。
五、總結
5.1、只有在歸檔模式下，對Logging表插入時，加不加append提示，產生的回滾段數量都差不多，其他情況下，加append能顯著減少回滾段的產生。
5.2、不論是在什麼情況下，加append都能減少資料塊讀的數量和一致讀的數量。