row cache lock contention parallel insert

I have a few direct load insert into 10 partitioned (and subpartitioned) tables, each partition is partitioned by hour and there are 8 hash subpartitions per partition. These processes runs in Oracle 10.2.0.4 Linux x64, 6 nodes RAC (the insert processes runs in a single node and parallel processes as well by using parallel_instance_group). Segment extent size are initial 10MB and next 10MB, most of subpartitions occupies 10MB.

The insert is simply insert append into... select, the degree of parallelism is 8 for some tables, 4 for others and 2 for smaller tables. I can observe that when there are 5 of these inserts running concurrently all of them suffers high contention on row cache lock, specifically dc_object_ids and dc_segments. Previously the load processes ran without parallel DML and yesterday pdml was enabled. Same symptoms.

I suspect the row cache contention is caused by space management but not sure how to reduce them. This is casuing some problems because sometimes "waiting too long for row cache" occurs and instance hangs for 1 or 2 hours then dies, when this happens other 5 nodes hang as well.

Cannot find anything on MOS, anyone has experienced this before?

TIA
--

LSC
--

http://www.freelists.org/webpage/oracle-l Received on Sat Dec 19 2009 - 01:58:11 CST