RE: ORA-02294 only on some parallel PK validates

(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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse
Sent: Thursday, May 22, 2014 11:22 AM
To: oracle-l_at_freelists.org
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

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l