I have two 11.2.0.3 databases on Solaris 11 64bit. They both have the same schemas. One is approx. 1GB and the other is approx. 10GB. On both databases, there is a parent and child table with similar number of records (~300k records each).

From legacy code, the previous DBA disabled a foreign key constraint, did some INSERTs into the parent and child tables and then re-enabled the foreign key.

On the smaller database, this operation takes < 1 second. On the larger database, this operation takes 1.5 minutes.

After timing each instruction, I narrowed it down to the re-enable causing the bottleneck, but I don't know exactly why it's <1 second on one instance and 1.5 minutes on the other. I think it's due to the size. But I'm not sure how to substantiate this to my manager. I cannot use EXPLAIN PLAN.

Is there a way to quantify or determine what's causing the discrepancy in timing?

create table t1 (x primary key) as 
  select level x from dual
  connect by level <= 1000;
  
create table t2 as 
  select t.x, r.y
  from   t1 t, 
         ( select level y from dual connect by level <= 10 ) r;
         
alter table t2 add constraint fk 
  foreign key ( x ) references t1 ( x) disable;
  
alter session set tracefile_identifier = chris;
exec dbms_monitor.session_trace_enable(waits => true);

alter table t2 modify constraint fk enable;

exec dbms_monitor.session_trace_disable();

select /*+ all_rows ordered dynamic_sampling(2) */ A.rowid, :1, :2, :3
from   "CHRIS"."T2" A , "CHRIS"."T1" B 
where( "A"."X" is not null) 
and( "B"."X" (+)= "A"."X") and( "B"."X" is null);