Re: row cache lock contention parallel insert
Date: Mon, 21 Dec 2009 12:44:13 +0100
> AWR shows row cache lock as top second wait event
> I did further testing, it seems that it's the APPEND hint makes tons of row
> cache queries, dc_users, dc_objects, dc_object_ids are queried thousand of
> times for a simple insert of a single row. In order to reduce row cache
> queries I specified the in the insert statement the partition name, this
> reduced from 176502, 176419, 264684 dc_* gets to 468, 401 and 1084.
> So APPEND + partition clause gives good performance.
> If I dont use APPEND clause the performance is well with and without
> specifying partition.
> So I have a doubt, why append needs to make so many recursive queries to row
> cache (even without parallel) :-?
Just to ask the obvious, since no-one else seems to have raised this so far:
May be I'm missing something from your description, but what I understand is that you try to perform multiple concurrent direct-path inserts into the same segment?
Are you aware of the fact that serial direct-path inserts (or parallel DML) require exclusive access to the segment?
No two processes can perform direct-path operations to the same segment at the same time.
So your multiple processes will effectively serialize in execution if you use the APPEND hint or parallel DML.
I assume that what you see are merely symptoms rather than the root cause.
If a segment is partitioned, then there is the possibility to limit the lock to the partition or subpartition by using explicit partition pruning in the INSERT part (insert /*+ append */ into tab [sub-]partition(A)), like you obviously now did in your last tests. Of course this implies that you can only insert data that matches the partition mentioned.
Using that approach multiple direct-path operations can be performed against a table simultaneously - exactly one per (sub-)partition.
Oracle related stuff blog:
Co-author of the forthcoming "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT! Jetzt freischalten unter http://movieflat.web.de Received on Mon Dec 21 2009 - 05:44:13 CST