Re: Buffer gets - more

]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?
]

The logic is

    If an outer join between child and parent returns no "preserved" child rows (i.e. no child without a parent) then the constraint is valid.

    So if you insert the result of the join, and insert no rows, you can enable the constraint.

Find an example of a very expensive INSERT, and show us the statement, stats, and execution path from the awrsqrpt.sql script .
It may be that some of your joins use very silly execution paths.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

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