RE: row cache lock contention parallel insert

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Sat, 19 Dec 2009 16:13:34 -0500
Message-ID: <F2AE2FFBCD7F430BA22937D82E0CA1FF_at_rsiz.com>

Is it extent allocation or segment space allocation that triggers the waits?  

Are your subpartitions in the same or different tablespaces?  

Is there a natural way to run the inserts as parallel jobs instead of one job with a parallel degree?  

If so, is the nature of the hash that you could run one dedicated job per subpartition?  

Are you trying to achieve maximum throughput or explore performance limits? Both are valid pursuits, I'm just curious.  

Regards and good luck,  

mwf  

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of LS Cheng
Sent: Saturday, December 19, 2009 1:07 PM To: Greg Rahn
Cc: Oracle Mailinglist
Subject: Re: row cache lock contention parallel insert  

Hi

I have done that already but still. But I just find out the possible cause after doing several tests.

The row cache is caused by space allocation when several PX slaves are inserting into several subpartitions, I noticed this after running two insert append, the first had tons of row cache waits and the second insert cero.

Looks like expected behaviour but in a 6 nodes RAC this look like a killer.

Thanks

--
LSC



On Sat, Dec 19, 2009 at 7:01 PM, Greg Rahn <greg_at_structureddata.org> wrote:

I dont recall the bug# but there is an issue related to quotas that
can show when there are a relatively high number of concurrent PX
sessions inserting into the same tablespace.  The work around is to do
a direct grant (dba or unlimited quota sys priv is not sufficient).
alter user <name> quota unlimited on <ts>;

That may be enough for you to find the bug (look for the dc row cache
stuff also).

Hope that helps.


On Fri, Dec 18, 2009 at 11:58 PM, LS Cheng <exriscer_at_gmail.com> wrote:
>


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


--
Regards,
Greg Rahn
http://structureddata.org

 




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

Received on Sat Dec 19 2009 - 15:13:34 CST