Dears,
I was working on a project which involves
1. Data Copy with table_exists action Truncate (#70 in nos)
2. Data Copy with table_exists action replace (#5 in nos)
I had to disable constraints and triggers in preperation for this excercise on the candidate schema before kicking of impdp.
Post the impdp, the constraint enabling took ages for 1 table. At one point it was already looking 20000secs.
I was going through various recommendations and options. One noteable callout was...
a. Enable constraint without validation
b. Enable parallel DDL @ session and run validation statement seperately.
I was thinking to log a SR and try this work around. But suddely I thought I am missing something, which is my basic issue diagnosis attempt.
Though I was constantly collecting metrics for the session, I couldnt sense any issue except the buffer gets crossed 1 billion already!!!!!!!!!!!!!!!!!!!!!
So the session was spinning in CPU always.
Finally I pulled a AWR report for 1 isolated window where only this constrtaint enabling was inprogress.
Found out a recursive SQL which was inprogress triggered by the constraint sql.
Noticed the plan of the recursive sql was pathetic (nested loop - expecting to drive 35 billion buffer gets). The constraint was a FK, so the recursive query was trying to find orphan records in the table where I am attempting to enable constraint.
So I decided to fix the plan
a. Patch the sql
b. Look for proper fix --- more info needed.
I continued, I looked at the table statistics, the auto stats, clashed with truncate and made all the tables part of the refresh with num_rows 0 statistics.
So thought this could be a concern. So I collected fresh stats on the tables participating in the referential constraint.
Tried generating the plan now, it was using hash outer join. Tried running the recursive SQL, all went well - 20000secs came down to 90secs!!!!!!!!!!
Tried the constraint enable statement now, the statement just flew by in no time.
So a happy ending for the story :)
