Re: row cache lock contention parallel insert
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 20 Dec 2009 14:08:12 +0100
Hello
After doing more tests I am puzzled, I have managed reproduce row cache
contention with cache id 8 and 11 (dc_object_ids and dc_objects) but the
results are not consistent, sometimes contentions are reproduced and
sometimes not. So I am still a bit lost.
Mark
Since my results are not consistent I am not really sure if the problem is
caused by extent or segment space allocation. But this is the physical
configuration:
- Bigfile Tablespace, each table's subpartitions goes there
- Cant find a natural way to to natual insert as parallel jobs
- Looking for performance, obvisouly if we could load directly to the
subpartitions performance would be best but it is not feasible (without some
deepper thinking)
TIA
--
LSC
On Sat, Dec 19, 2009 at 10:13 PM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
> 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:
