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
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.
Not sure about if this is goin to work but you can try it - if the table is not very big and it can be fitted in the db buffers (you can start the instance in "repaire" mode in the maintenance window with maximal db buffers), then ALTER TABLE ** CACHE, make some select with FTS, and then validate the index - if the table is cached, I think the validation of the index will be fast.
Thanks for the suggestion. In some cases (current testing) it may fit in our buffer cache size, but I suspect that will not always be the case.
I should have mentioned that while there is a maintenance window for the operation, we're only locking out access to the specific tables we are working on, the rest of the environment needs to remain available.
I was thinking again about the validation of FK and what steps does the Oracle make to validate it. It should be something like
1. Make lock on the slave table
2. Make lock on the PK index
3. FTS on the slave table
- for each row - select with the index and check if it returns rows
so maybe it will be faster if somehow the index is cached, and not the table. Maybe with some query that makes INDEX FAST FULL SCAN
You said that you tried parallel ddl, what was the results exactly?
ALTER INDEX parallel n
somethinc like that?
That's a good point, I'll look into the index/table and try some parallelism on them. To avoid a ddl change I had just done a quick test with 'alter session force parallel ddl;'
Also, there seems to be something up with the foreign key that is having the timing problem. In our environment it's taking between 40-55 minutes to enable by itself.
In 4 other environments on the same set of databases, 3 of them were similar times. However, one environment only took 14 minutes. I checked and all of the data is the same, so I'm not sure what would cause the others to be so slow.
I'm going to work on testing them individually and getting traces/statspack for what's going on but it's not as easy as it should be.
Before I can get that information compiled, anybody have an idea as to why the one might have been so much faster?
If anybody is curious, when you do a foreign key enable, it runs a query similar to the following to check for orphaned children. If any rows are returned it doesn't enable.
select /*+ all_rows */ a.rowid, 'owner', 'child', 'child_parent_fk'
from owner.child a, owner.parent b
where (a.id is not null) and (b.id (+)= a.id) and (b.id is null)
The reason my times are so high is because the optimizer is choosing a hash join for that query instead of the sort-merge it's choosing on the fast environments. If I hint that to use_merge(a b) and run it on a slow environment, the query returns about as fast as the enable goes on a fast environment.
I could hack it with a stored plan, but I think I'm going to have to dig into the stats & optimizer traces to see what's going on, and why the bad environments are giving the hash joins.