Committing after queries over dblink sucks! Too much redo?

From: NetComrade <andreyNSPAM_at_bookexchange.net>
Date: Fri, 11 Apr 2003 18:23:08 GMT
Message-ID: <3e96eed6.3973444080@nyc.news.speakeasy.net>

A while ago I was looking at queries over the link. (see old thread why frequent commits are not good:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3ab260df.604467728%40news.earthlink.net&rnum=1&prev=/groups%3Fas_q%3Dcommit%26safe%3Dimages%26ie%3DUTF-8%26oe%3DUTF-8%26as_uauthors%3Dnetcomrade%26lr%3D%26hl%3Den) Queries over the link require a small chunk of rollback space, and therefore if session never commits or rollbacks the segment will become 'blocked' by this session. (won't be able to wrap)

It was suggested by Jonathan at the time that rollbacks could be slightly cheaper than commits, but i never actually tested it.

Well, below are my findings. It seems like wrapping a select over a database link with a savepoint and rollback takes MUCH less time than committing consuming much less resource, and actually going almost as fast as not dong any rollbacks at all. The only think I find a bit out of the ordinary is the amount of redo generated by 10000 commits. 10000 commits times our db_block_size would be around 80K. Maybe it's due to redo wastage, that I don't fully understand yet.

No commits/No rollbacks:

Time took: 24.00 secs

Redo generated:              172 bytes
Redo wastage:              324 bytes
Log file syncs:                0

Time took:   18.02 secs
Redo generated:                0 bytes
Redo wastage:              324 bytes
Log file syncs:                0

Time took:   18.23 secs
Redo generated:              172 bytes
Redo wastage:              324 bytes
Log file syncs:                0

2) Commit after every select:

Time took: 134.93 secs

Redo generated:        8,739,060 bytes
Redo wastage:        6,110,520 bytes
Log file syncs:           10,000

Time took:  140.14 secs
Redo generated:        8,738,800 bytes
Redo wastage:        6,102,016 bytes
Log file syncs:           10,000

Time took:  141.52 secs
Redo generated:        8,738,912 bytes
Redo wastage:        6,085,708 bytes
Log file syncs:           10,000

3) Savepoint;select;rollback so savepoint

Time took: 23.39 secs

Redo generated:                0 bytes
Redo wastage:              324 bytes
Log file syncs:                0

Time took:   23.31 secs
Redo generated:              172 bytes
Redo wastage:              324 bytes
Log file syncs:                0

Time took:   21.23 secs
Redo generated:                0 bytes
Redo wastage:                0 bytes
Log file syncs:                0

pl/sql used:

declare

var			number(1);
l_start 		number;
l_end			number;
v_after_redo		number;
v_before_redo		number;
v_after_redow   	number;
v_before_redow  	number;
v_before_lfs		number;
v_after_lfs		number;
v_before_redosync  	number;
v_after_redosync	number;

begin
select value into v_before_redo
  from v$mystat my, v$statname s
  where s.statistic#=my.statistic# and s.name='redo size'; select value into v_before_redow
  from v$sysstat
  where name='redo wastage';
select total_waits into v_before_lfs
  from v$system_event
  where event='log file sync';
select value into v_before_redosync
  from v$sysstat
  where name='redo synch time';

l_start:=dbms_utility.get_time;
for i in 1 .. 10000
loop
savepoint b4dblink;

above wouldn't exist in test 1 and 2 select 1 into var from dual_at_vcrs; rollback to b4dblink; --above wouldn't exist in test 1, and would say commit in test 2 end loop; l_end:=dbms_utility.get_time;

select value into v_after_redo
  from v$mystat my, v$statname s
  where s.statistic#=my.statistic# and s.name='redo size'; select value into v_after_redow
  from v$sysstat
  where name='redo wastage';
select total_waits into v_after_lfs
  from v$system_event
  where event='log file sync';
select value into v_after_redosync
  from v$sysstat
  where name='redo synch time';

dbms_output.put_line ('Time took: '||to_char(round( (l_end-l_start)/100,5),'999.99') ||' secs'); dbms_output.put_line ('Redo generated:
'||to_char(v_after_redo-v_before_redo,'999,999,999,999')||' bytes');
dbms_output.put_line ('Redo wastage:
'||to_char(v_after_redow-v_before_redow,'999,999,999,999')||' bytes');
dbms_output.put_line ('Log file syncs:
'||to_char(v_after_lfs-v_before_lfs,'999,999,999,999'));
dbms_output.put_line ('Redo sync time:
'||to_char(v_after_redosync-v_before_redosync,'999,999,999')||'
nanosecs?');
--above turned out to be useless, therefore i didn't display it end;

.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Fri Apr 11 2003 - 13:23:08 CDT