Re: row cache lock contention parallel insert

  • From: LS Cheng <exriscer@xxxxxxxxx>
  • To: info@xxxxxxxxxxxxxxxxxxxxx
  • Date: Mon, 21 Dec 2009 12:50:23 +0100

Hi Randolf

The insert runs against different segments, against 16 to 64 subpartitions,
I understand that we should not observe locking in this situation (enq: TM
Contention). We are not running several inserts such as

insert  /*+ append */ into t1 in different sessions

We have

insert  /*+ append */ into t1
insert  /*+ append */ into t2
insert  /*+ append */ into t3
insert  /*+ append */ into t4
insert  /*+ append */ into t5

In different sessions

Thanks

--
LSC

On Mon, Dec 21, 2009 at 12:44 PM, Randolf Geist
<info@xxxxxxxxxxxxxxxxxxxxx>wrote:

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