David,
Commit frequency can help or hurt performance. The more often you commit, the
more often undo blocks will be reused. This will also cause the log buffer and
committed blocks in the buffer cache to be flushed. If you commit less often, these
actions happen less often, but undo blocks will not be reused as often. This could
cause space management problems if there is not enough space in the tablespace or
other processes are causing rollback segments to shrink.
The short version is that frequent commits = additional housekeeping (impact
can't be known exactly until tested) and infrequent commits = additional space
allocation (impact can't be exactly known until tested).
The issue of swapping is not related to rollback. I have not tested the merge,
but it may be doing some sorting. When it exceeds your sort_area_size in memory it
begins to write sort runs to disk (temporary tablespace). Is this what they mean by
swapping?
The best approach (IMHO) is to go back to the critics and ask for more
detailed information, including the logic and scenarios describing the logic.
Daniel Fink
David Turner wrote:
>
> I was presenting a new load process and I am using straight merge selects
> from external tables and one of the complaints that came up was that
>
> Performance would be worse because I wasn't committing more frequently.
>
> If my merge was too large memory swapping would occur and it was possibly
> related to rollback.
>
> I said I was not aware of this and would look into it but I had never heard
> of this before and couldn't find any information on this so of course I'm
> sending mail to the list. Both items sound bogus to me. I've really never
> heard where committing more frequently on a load would help performance in
> any way.
>
> Thanks, Dave
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: David Turner
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).begin:vcard
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;;;;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard
