Re: row cache lock contention parallel insert
From: Greg Rahn <greg_at_structureddata.org>
Date: Sat, 19 Dec 2009 10:01:27 -0800
Message-ID: <a9c093440912191001h510d6a4dpbe2d27c4dd8166ec_at_mail.gmail.com>
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>;
> 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.
Date: Sat, 19 Dec 2009 10:01:27 -0800
Message-ID: <a9c093440912191001h510d6a4dpbe2d27c4dd8166ec_at_mail.gmail.com>
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-lReceived on Sat Dec 19 2009 - 12:01:27 CST