Buffer gets - more

From: Joe Smith <joe_dba_at_hotmail.com>
Date: Thu, 21 Apr 2011 08:05:55 -0500
Message-ID: <BLU158-w2574705A99BC8632EDAE4297920_at_phx.gbl>

How does Oracle check data when it enables a FK constraint?  

This is 10gRel2 on Solaris 10 32-bit.  

We enable constraints thru a plsql loop with an exceptions table.  

Loop

                Alter table name constraint con_name enable exceptions into exceptions;

End loop;  

We just had to add some more tables to our app that are about 180k records with FK constraints. It is taking hours to enable the constraints.  

I ran an AWR report and saw that the alter table statement was doing millions of buffer_gets and the CPU time was off the charts ( i.e. 50k seconds ). I also noticed that paired with each alter table enable constraint statement under the heading “SQL with buffer gets” and “SQL with CPU time” there was a INSERT INTO EXCEPTIONS table statement that Oracle was generating. Even though it was not inserting data into the exceptions table. The INSERT INTO EXCEPTIONS table statement had approximately the same buffer_gets and CPU time.  

Why would Oracle run an INSERT INTO EXCEPTIONS table statement when it was not inserting into that table?  

How does Oracle check the data when it enables FK constraints? Does it have to load the table into the buffer cache or can it do it at the block level?                                                  

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

Received on Thu Apr 21 2011 - 08:05:55 CDT