RE: row cache lock contention parallel insert
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <exriscer@xxxxxxxxx>, "'Greg Rahn'" <greg@xxxxxxxxxxxxxxxxxx>
- Date: Sat, 19 Dec 2009 16:13:34 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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@xxxxxxxxxxxxxxxxxx> 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@xxxxxxxxx> 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
Other related posts:
