Re: row cache lock contention parallel insert

  • From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • 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.

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

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


Other related posts: