Tuesday, February 03, 2009

update and undo

Goal
----
Research how to skip the update on some columns.
Logically, Update table set column = column is an option.
Need to check the resource usage.

Result
------
Update column without change the value, still generate same amount of undo and redo.
The undo is proportion to target value size.

Tested on Oracle 11.1.0.7

Setup
-----

drop table t;
drop table t1;

create table t nologging
as
select rownum id, lpad('x',500,'x') c1
from dual
connect by level <= 50000;

create table t1 nologging
as
select rownum id, lpad('x',500,'x') c1
from dual
connect by level <= 50000;

set serveroutput on

Benchmark
---------
exec runstats_pkg.rs_start;
update t set id = id, c1 = c1;
commit;
exec runstats_pkg.rs_middle;
update t1 set id = id;
commit;
exec runstats_pkg.rs_stop;


Run1 ran in 414 hsecs
Run2 ran in 119 hsecs
run 1 ran in 347.9% of the time

Name Run1 Run2 Diff
LATCH.shared pool 117 120 3
STAT...redo synch time 23 7 -16
STAT...redo buffer allocation 16 0 -16
LATCH.DML lock allocation 22 5 -17
STAT...physical reads 1,786 1,808 22
LATCH.redo writing 62 22 -40
LATCH.redo allocation 78 23 -55
STAT...CPU used by this sessio 73 16 -57
LATCH.enqueues 152 84 -68
STAT...commit cleanouts 979 1,120 141
STAT...rollback changes - undo 142 0 -142
LATCH.cache buffers lru chain 4,909 5,184 275
STAT...Elapsed Time 414 119 -295
STAT...DB time 418 119 -299
STAT...IMU Redo allocation siz 9,412 15,396 5,984
STAT...consistent changes 48,013 30 -47,983
STAT...consistent gets 51,861 1,878 -49,983
STAT...redo entries 54,046 3,579 -50,467
STAT...db block gets 56,883 2,111 -54,772
STAT...IMU undo allocation siz 63,992 1,080 -62,912
STAT...db block changes 110,023 7,330 -102,693
STAT...session logical reads 108,744 3,989 -104,755
STAT...physical read bytes 29,261,824 29,622,272 360,448
LATCH.cache buffers chains 487,509 26,861 -460,648
STAT...undo change vector size 32,362,024 2,399,528 -29,962,496
STAT...redo size 69,525,648 5,092,152 -64,433,496

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
521,401 45,711 -475,690 1,140.65%

No comments: