Re: ASSM bug: slow INSERT after uncomitted DELETE

On Aug 11, 5:43 am, Noons <wizofo...@xxxxxxxxx> wrote:
Actually, I think the problem is more related to the poor practice of
using delete to clear an entire table.  A truncate should be used,
rather than a delete.  And definitely a commit should be in there
somewhere, rather than leaving entire table deletes uncomitted.

I wouldn't agree to that entirely. In general a commit should be used
along the logical transaction, and there are certainly cases where you
want to "reload" a table as part of a larger transaction, and only if
everything else succeeds perform a final commit. You don't want to see
other sessions an intermediate state of the on-going transaction, that
might lead to other undesirable side-effects.

Also think of materialized view refresh groups that by default use the
atomic refresh option. In case of a complete refresh these also will
delete the whole table and re-insert the data in a single transaction
without any intermediate commits.

However I agree that doing so (delete all rows + re-insert within a
single transaction) with very large tables and/or a large number of
indexes will potentially pose problems, regardless of ASSM being used
or not.

One approach that allows to efficiently "replace" table contents
almost in an "atomic" way is to use partitioning with the exchange
table feature. There you can re-load the "new" version of the table to
a "exchange" table, making usage of all work-reducing features
available (unusable indexes, truncate, optionally nologging, direct-
path inserts, even parallel DML / parallel index rebuild for huge data
volumes), and only at the end of the operation "swap" the new table
contents into the partition, making them available almost instantly to
the remaining processing. This could also be used for multiple tables,
although it's not going to be that "atomic" anymore then.

Of course this approach requires an enterprise edition license for the
partitioning option I think.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
.

Relevant Pages

  • RE: Slow connection to Oracle 9i
    ... A commit() should be issues only when necessary - the cost in the database of a commit is large and doing so in this random fashion is an invitation to other performance problems. ... Slow connection to Oracle 9i ... do not get a transaction too long error (can't remember exactly what its ...
    (perl.dbi.users)
  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... the file then that inode would be added to the journal list and the pages ...
    (Linux-Kernel)
  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... delayed allocation, starting a new transaction could to happen a lot to ...
    (Linux-Kernel)
  • Re: [PATCH 2/2] improve ext3 fsync batching
    ... array than it takes to complete the transaction. ... when commit times go up to seconds? ... Transactions on that busier drive would take longer, we would sleep ... longer which would allow us to batch up more into one transaction. ...
    (Linux-Kernel)
  • Re: Backups and Transaction Log file size
    ... It sounds like a classic case of a long running open transaction. ... Find the client and either commit or roll it back. ... If you know it is a garbage connection you can kill the SPID and it will roll back any changes that the SPID may have open and allow you to backup and truncate properly. ... Once the committed trans have been ...
    (microsoft.public.sqlserver.setup)