關于使用insert append和parallel
2021-08-10 10:16:55

為避免將來的理解沖突，本文所有說明基于Oracle 10g版本。

?

關于使用insert append

Eygle大師曾經對insert append和logging/nologging的關系做過比較全面的測試和說明（http://www.itpub.net/thread-242761-1-1.html）。

這里不詳述了，只給出結論：

?

非歸檔模式下，不管表是否設置logging/nologging，append都會減少redo和undo的生成。

歸檔模式下，append+nologging才能減少redo和undo的生成。

?

注意在direct insert?下，實際上操作是不會對數據產生redo和undo的，但這不代表不會產生redo和undo。

?

另外在direct insert后，由于插入的數據沒有在redo中記錄，此時如果需要進行恢復，插入的記錄數據會丟失。所以在插入數據后，需要進行全庫數據備份。

?

以append方式插入記錄后，要執行commit，才能對表進行查詢。否則會出現錯誤：

ORA-12838:?無法在并行模式下修改之后讀/修改對象

?

另外insert append時在表上加“6”類型的鎖，會阻塞表上的所有DML語句。因此在有業務運行的情況下要慎重使用。若同時執行多個insert append對同一個表并行加載數據，并不一定會提高速度。

?

使用append insert方法：

alter table t nologging;

insert /*+ append */ into test.t select * from test.t2;

commit;

?

關于使用parallel

可以在表級設置并行度，比如：

create table test nologging PARALLEL (DEGREE 7) as select * from dba_tables;

alter table t parallel (degree 4);

?

也可以通過SQL hint在會話級別設置，但是需要注意，如果想在執行DML語句時使用并行（比如insert、delete、update），需要enable parallel dml，比如：

alter session enable parallel dml;

insert /*+ append?parallel(t 4)?*/ into t select * from t2;

?

順便給出我以前的2個測試結論：

實驗1：insert時間比較

insert into t select * from t2;?（耗時00:00:12.96）

普通insert。

?

alter table t nologging;

insert /*+ append */ into t select * from t2; ?（耗時00:00:00.74）

direct insert，時間最短效率最好。

?

alter session enable parallel dml;

insert /*+ append parallel(t 4) */ into t select * from t2;?（耗時00:00:02.07）

direct insert +parallel，其實并沒有direct insert效率高，不過比普通insert好點。

?

實驗2：create table + parallel產生UNDO測試

方法1：

create table test as select * from dba_tables;

?

方法2：

create table test nologging as select * from dba_tables;

?

方法3：

create table test nologging PARALLEL (DEGREE 7) as select * from dba_tables;

?

方法4：

create table test as select * from dba_tables where 1=0;

insert into test select * from dba_tables;

commit;

?

監控方式：

SQL> select sid,username from v$session;

SQL> select * from v$sesstat where sid=(select sid? from v$session where username='TEST') and STATISTIC#=(select STATISTIC# from v$statname where name='undo change vector size');

?

所有測試執行三次，結果如下：

?

?	第1次undo產生量(單位：bytes)	第2次	第3次
方法1(create table as select)	29212	30380	36220
方法2(nologging)	28684	26956	28640
方法3(nologging parallel)	90120	89312	91260
方法4(create table and insert data)	40660	43796	41940

?

結論：創建表時增加并行度會產生較多的undo量。