關(guān)于使用insert append和parallel
2021-08-10 10:16:55

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

?

關(guān)于使用insert append

Eygle大師曾經(jīng)對(duì)insert append和logging/nologging的關(guān)系做過比較全面的測(cè)試和說明（http://www.itpub.net/thread-242761-1-1.html）。

這里不詳述了，只給出結(jié)論：

?

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

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

?

注意在direct insert?下，實(shí)際上操作是不會(huì)對(duì)數(shù)據(jù)產(chǎn)生redo和undo的，但這不代表不會(huì)產(chǎn)生redo和undo。

?

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

?

以append方式插入記錄后，要執(zhí)行commit，才能對(duì)表進(jìn)行查詢。否則會(huì)出現(xiàn)錯(cuò)誤：

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

?

另外insert append時(shí)在表上加“6”類型的鎖，會(huì)阻塞表上的所有DML語句。因此在有業(yè)務(wù)運(yùn)行的情況下要慎重使用。若同時(shí)執(zhí)行多個(gè)insert append對(duì)同一個(gè)表并行加載數(shù)據(jù)，并不一定會(huì)提高速度。

?

使用append insert方法：

alter table t nologging;

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

commit;

?

關(guān)于使用parallel

可以在表級(jí)設(shè)置并行度，比如：

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

alter table t parallel (degree 4);

?

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

alter session enable parallel dml;

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

?

順便給出我以前的2個(gè)測(cè)試結(jié)論：

實(shí)驗(yàn)1：insert時(shí)間比較

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

普通insert。

?

alter table t nologging;

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

direct insert，時(shí)間最短效率最好。

?

alter session enable parallel dml;

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

direct insert +parallel，其實(shí)并沒有direct insert效率高，不過比普通insert好點(diǎn)。

?

實(shí)驗(yàn)2：create table + parallel產(chǎn)生UNDO測(cè)試

方法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;

?

監(jiān)控方式：

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');

?

所有測(cè)試執(zhí)行三次，結(jié)果如下：

?

?	第1次undo產(chǎn)生量(單位：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

?

結(jié)論：創(chuàng)建表時(shí)增加并行度會(huì)產(chǎn)生較多的undo量。