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