Re: ASSM bug: slow INSERT after uncomitted DELETE
Date: Tue, 11 Aug 2009 02:42:00 -0700 (PDT)
Message-ID: <39e52150-8b74-460a-aa0e-164178a71a20_at_f37g2000yqn.googlegroups.com>
On Aug 11, 5:43 am, Noons <wizofo..._at_gmail.com> 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, directpath 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/
Received on Tue Aug 11 2009 - 04:42:00 CDT