We have a process which requires us to enable foreign keys on a number of tables (some parents of each other, or other misc relationships). We need to do it as fast as possible, since the process will require a maintenace window in the operational system.

All fk columns are indexed.

We are enabling all the foreign keys in parallel. Initially we had a locking issue, but that can be worked around by first enabling NOVALIDATE, and then running a second enable in parallel. Validating a constraint that is already enabled doesn't take the locks so everything can be done in parallel.

The issue we have now is with a single foreign key that is taking a long time. The rest finish up since they are in parallel but we are bound by this longest key.

Anybody have some good ideas to speed it up? From a session doing the enable these are the wait events. It's a little misleading as the scattered read & direct write both occur up front, and never increase again after hitting those numbers. Once that happens, direct path read is the only wait event increasing. It's hard to say if that wait is a problem as it's a misleading event sometimes.



event                    waits       time waited

-----------------------------------------------------

direct path read         2147820     65546

db file scattered read     53151     19166

direct path write          50686      3957


In the session statistics, the big hitters are



physical reads direct: 14797874

physical reads:        15459428


I tried increasing sort area size to cut back on the direct path reads but it didn't seem to affect much. I suppose I could keep going it higher, it was at 80m which is probably not enough.

CPU Time/CPU used by this session didn't show up much in the v$sesstat/v$session_event views but statspack reports for an entire run of the FK's have had it near the top. I'm not convinced it's cpu bound yet.

Is there anything we can do to increase the enabling/validation of a single constraint? Or verify without much doubt that we have it running as fast as it ever will on current hardware?

I tried using 'force parallel ddl' but in tests so far, it hurt more than it helped.

Thanks