常见dml、ddl语句使用nologging选项所生成的redo和undo大小比较

说明:反映undo、redo占用量的统计指标是:session

undo change vector sizeapp

redo sizeide

 

DDL/DML Operationsspa

Direct-pathit

nologgingio

paralleltable

noarchivelog modeclass

archivelog modedate

Redoselect

Undo

Redo

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

                 

 

结论:

一、insert into:非direct方式下noarchivelog和archivelog两种模式均产生大量undo、redo;direct方式下noarchivelog模式均产生少许undo、redo;direct+logging方式下archivelog模式产生少许undo、大量redo;direct+nologging方式下archivelog模式产生少许undo、少许redo

 

二、create table as:CTAS自己就是一种direct的操做,CTAS+logging方式下noarchivelog模式产生少许redo、少许undo;CTAS+nologging方式下noarchivelog模式产生更少许redo、更少许undo;CTAS+logging方式下archivelog模式产生少许undo、大量redo;CTAS+nologging方式下archivelog模式产生少许undo、少许redo;

 

三、alter table ... move:nologging方式下noarchivelog模式产生少许undo、少许redo;nologging方式下archivelog模式产生少许undo、少许redo;logging方式下archivelog模式产生少许undo、大量redo

 

四、create index:logging+nologging方式下noarchivelog模式产生少许undo、少许redo;logging方式下archivelog模式产生少许undo、大量redo;nologging方式下archivelog模式产生少许undo、少许redo

 

五、update:enable parallel方式下,noarchivelog+ archivelog模式生成少许undo、少许redo;非enable parallel方式下,noarchivelog+ archivelog模式生成大量undo、大量redo

 

六、delete:任何组合都会生成大量undo、大量redo

相关文章
相关标签/搜索