Extreme Performance Degradation on 7.3.4

We are running Oracle 7.3.4.4.1 on HP/UX 10.20 with striped volumes. Recently we deleted 1.2M rows from several tables in our warehouse database. The resulting tables now have approximately 500K rows. The indexes were rebuilt, and the table reanalyzed. After deleting the rows, the our databases were physically moved on the system. We use the cost based optimized on the instance.

After the database was accessed via our normal reporting environment, performance was extremely degraded. Many queries that ran against these tables that would complete in minutes were taking hours or not returning at all. The instance in question does not have a tremendous amount of client access, and even when we limited access to one active session, performance did not improve. Our cache hit ratios were down in to 30%-30% range.

Other databases that were moved at the same time did not experience performance degradations so we initially ruled that variable out. We check the volumes and found that they were all intact and operating normally from an OS perspective.

We rebuilt indexes again and reanalyzed clusters, tables, and indexes. No change. We later exported data from the instance and reimported back in. We rebuilt the indexes and reanalyzed all objects. After we did this, normal performance was restored. Our cache hits were back up above 85%.

What could have caused this? Even with fragmentation, I don't think performance should have been worse than prior to the deletes since the outlying data would have been in the same physical locations. I would expect that performance should not have improved until after a reorganization, but I would not expect to see a tremendous degradation. How could have a physical relocation of the database files impacted the system? Received on Sat Apr 22 2000 - 00:00:00 CDT