RE: ORA-02294 only on some parallel PK validates
- From: "Rich Jesse" <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 22 May 2014 16:22:15 -0500 (CDT)
> Is the largest table one of the 15 problems?
It's not. The Infamous 15 are a mix in terms of size, from 20K rows and
under 5MB in storage to 60+M rows and 20+GB storage.
> (If it is, then we don't know how long it would have taken if it completed,
> right? With perfect scaling 4hours/8 = 30 minutes, so it could be possible
> that it finishes in the 45. I'm beginning to wonder how many CPUs you have
> if beating parallel 8 for one thread of this for 30 minutes leaves headroom
> for the other 4600 in the same window. I would not be surprised at all if
> the pacing item is TEMP.)
I think there are other factors here, as the test of adding the PK on
BIGTABLE single-threaded was done in a single ALTER TABLE...ADD CONSTRAINT
VALIDATE, whereas the parallel first ADD CONSTRAINT...NOVALIDATE, then
validated in a separate step. There are significant performance gains here,
as the DB refuses to parallelize some pieces of the ADD CONSTRAINT where
VALIDATE is specified. See also next answer.
> Are there any other tables over 45 minutes single threaded?
Unknown. In step 4b of my process, I set "large" tables, including 1 of the
Infamous 15, to parallel. With testing, I basically followed:
http://www.asktherealtom.ch/?p=214
> What is your actual time target?
ASAFP -- As Soon As Possible. :)
> If the problem is repeatable for the same 15 tables, then it is probably
> something about those 15. If it moves around, then I'd agree it has to be
> solved before you trust anything parallel. (as per John Hurley.)
I'll find out tomorrow with the next data load. My intent was to "fix" this
issue prior to it, but now I'm just curious, and the workaround to validate
the Infamous 15 afterwards isn't too costly.
> Again, good luck,
Thanks and thanks for your feedback!
Rich
--
//www.freelists.org/webpage/oracle-l
Other related posts:
