Alter table add column taking a long time
Hi,
we have a table with more than 2 million rows and from user_segments the size of the table is appx 2.3 GB
We issued the following:
alter table shipment_header_aux_bak add (tractor_plate_no char(20 byte) default ' ' not null);
Above took a whopping 15 minutes to complete. While this was running, we pulled up dbconsole and saw that the session waited the most on cache buffer lru chain latch
We also traced the session and ran tkprof on it. The findings were as follows:
TKPROF: Release 10.2.0.4.0 - Production on Mon Aug 24 04:56:52 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
we have a table with more than 2 million rows and from user_segments the size of the table is appx 2.3 GB
We issued the following:
alter table shipment_header_aux_bak add (tractor_plate_no char(20 byte) default ' ' not null);
Above took a whopping 15 minutes to complete. While this was running, we pulled up dbconsole and saw that the session waited the most on cache buffer lru chain latch
We also traced the session and ran tkprof on it. The findings were as follows:
TKPROF: Release 10.2.0.4.0 - Production on Mon Aug 24 04:56:52 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
1