Oracle NoLogging Append 方式减少批量insert的redo_size
业务处理中,很多时候使用实表临时表处理中间结果,而实表的Insert操作缺省会记录redo log,针对此问题收集相关测试总结信息如下:
DDL/DML Operations |
Direct-path |
nologging |
parallel |
Noarchivelog Redo |
Noarchivelog Undo |
Archivelog Redo |
Archivelog Undo |
Insert into XXX select * from YYY |
N |
N |
N |
19076324 |
627240 |
18938620 |
612980 |
Alter table XXX nologging; Insert into XXX select * from YYY; |
N |
Y |
N |
19085860 |
631584 |
18935548 |
612912 |
Insert /*+ append */ into XXX select * from YYY |
Y |
N |
N |
26628 |
4048(only metadata) |
19145656 |
4048 |
Alter table XXX nologging; Insert /*+ append */ into XXX select * from YYY |
Y |
Y |
N |
26868 |
4048(only metadata) |
26836 |
4048 |
Create table XXX as select * from YYY |
Y |
N |
N |
77624 |
18500 |
19162220 |
15468 |
Create table XXX nologging as select * from YYY |
Y |
Y |
N |
52160 |
11176 |
52408 |
11248 |
Alter table XXX move nologging; |
N |
Y |
N |
36288 |
6208 |
36576 |
6208 |
Alter table XXX move; |
N |
N |
N |
39788 |
7288 |
19154024 |
5972 |
Create index XXX |
N |
N |
N |
21280 |
1864 |
3093616 |
1888 |
Create index XXX nologging |
N |
Y |
N |
22264 |
2208 |
22620 |
2232 |
update XXX set |
N |
N |
N |
122903212 |
47987880 |
122403692 |
47786680 |
Update /*+ parallel(5) */ XXX set |
N |
N |
Y(Queries) |
121629928 |
46706296 |
120818336 |
46466056 |
Alter table XXX nologging; update XXX set |
N |
Y |
N |
123155288 |
48006404 |
110396512 |
41617700 |
Alter table XXX nologging; update /*+ parallel(5) */ XXX set |
N |
Y |
Y(Queries) |
119748064 |
46152324 |
120205036 |
46261536 |
Alter session enable parallel dml Update /*+ parallel(5) */ XXX set |
N |
N |
Y(DML) |
59846488 |
24680220 |
59740516 |
24611672 |
Alter table XXX nologging; Alter session enable parallel dml Update /*+ parallel(5) */ XXX set |
N |
Y |
Y(DML) |
59831756 |
24673396 |
59717116 |
24602628 |
delete XXX; |
N |
N |
N |
60684720 |
37650208 |
61770472 |
38254648 |
delete /*+parallel(5) */ XXX; |
N |
N |
Y(Queries) |
60685416 |
37650436 |
61988568 |
38461832 |
Alter table XXX nologging; delete XXX; |
N |
Y |
N |
60684956 |
37650216 |
61989984 |
38460424 |
Alter table XXX nologging; delete /*+parallel(5) */ XXX; |
N |
Y |
Y(Queries) |
60685092 |
37650224 |
61986156 |
38459172 |
Alter session enable parallel dml delete /*+parallel(5) */ aabbn; |
N |
N |
Y(DML) |
65072896 |
40990412 |
61979216 |
38457372 |
Alter table XXX nologging; Alter session enable parallel dml delete /*+parallel(5) */ aabbn; |
N |
Y |
Y(DML) |
65073828 |
40998048 |
62022668 |
38500548 |
|