row cache lock contention parallel insert
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 19 Dec 2009 08:58:11 +0100
Hi
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
Other related posts: