DBMS_REDEFINITION changed the plan statistics of oracle table with foreign key constraint to primary
Hi All,
This caught us unaware in our production environment.
Unfortunately the table that started Full Table Scans after the Select query's next hard parse contains 32 millions rows.
The sessions accumulated and the instance ran out of memory.
We are using oracle10g, 10.2.0.4.0
The redefinition was implemented on a Sunday morning and by 9:00 am Monday I had a serious issue on my hands.
The select statements running full table scans all referenced the redefined table primary key (WHERE LARGE_TBL.foreign_id = REDEF_TBL.id)
We used the RESTORE statistics for the large table, NO_INVALIDATE => FALSE.
And implemented SQL Profiles to reduce the cost of the Select statements to get the production system running again.
This caught us unaware in our production environment.
Unfortunately the table that started Full Table Scans after the Select query's next hard parse contains 32 millions rows.
The sessions accumulated and the instance ran out of memory.
We are using oracle10g, 10.2.0.4.0
The redefinition was implemented on a Sunday morning and by 9:00 am Monday I had a serious issue on my hands.
The select statements running full table scans all referenced the redefined table primary key (WHERE LARGE_TBL.foreign_id = REDEF_TBL.id)
We used the RESTORE statistics for the large table, NO_INVALIDATE => FALSE.
And implemented SQL Profiles to reduce the cost of the Select statements to get the production system running again.
0