Re: row cache lock contention parallel insert

From: Randolf Geist <info_at_sqltools-plusplus.org>
Date: Mon, 21 Dec 2009 12:44:13 +0100
Message-Id: <996386982_at_web.de>

> 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.

Regards,
Randolf

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

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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Dec 21 2009 - 05:44:13 CST