RE: ORA-02294 only on some parallel PK validates
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 22 May 2014 15:46:02 -0400
Is the largest table one of the 15 problems?
(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.)
Are there any other tables over 45 minutes single threaded?
Are the 15 problem tables all amongst the largest?
What is your actual time target?
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 have not tested the ORDERED clause recently on anything huge. I wonder
whether if you excluded the giant table from the datapump and arranged to
load it in the order of the PK whether that would handle your problem. You
would need to verify that this did not destroy any other cluster factors for
indexes on this table such that it made a change for the worse in query
plans selected. IF you decide to try this and you have a chance to take good
notes and metrics, it could possibly be an excellent presentation.
JL has published some writings on doing the best you can with various
parameter settings for large and possibly wide sorts. I don't have time
right now to look up the references, but they should be pretty easy to find.
If the "15 problem tables" are consistent, possibly employing those
techniques could also make the problem go away.
Again, good luck,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Rich Jesse
Sent: Thursday, May 22, 2014 11:22 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: ORA-02294 only on some parallel PK validates
Mark replies:
> It is unclear to me why you do not proceed with the 15 indentified
> tables non-parallel, if that works repeatably.
This is the first time I've seen this. My concern is that this is both
unpredictable and apparently undocumented. I'll be running this process
again tomorrow, so I'll be able to see which, if any, PKs are affected.
> With so many objects available to run in separate threads, it is
> unclear to me that parallel is an advantage for any of this process.
The largest table takes 4 hours to create its PK single-threaded. All 4600+
PKs take 45m total with DOP of 8. No matter how I divide the list, it will
take a minimum of 4h if I don't use parallel.
> In fact, if you decide to go that way, I would expect a significant
> reduction in the overall elapsed time. For tables that will fit in the
> buffer cache, you might want to run a scanner to get nice clean blocks
> in there just before you unleash the index creates for that table,
> with a thread for each index create on a cached table.
I'm dying to test this on our future production DB server as the buffer
cache could nearly fit every table at the time of this part of the
migration. I <3 new hardware. :) I'll also have 2x more cores to throw at
it, but I'm wondering if that could make this problem worse with increased
contention.
Thanks!
Rich
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
