Re: Extreme Performance Degradation on 7.3.4

Phill Giancarlo wrote:
>
> 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?

A possible explanation...

You start with 1.2M rows - Oracle says "whoa thats a lot" I going with indexes all the time.

You reduce to 0.5M rows and reanalyze - you are still using just as much space but after re-analyze, Oracle starts thinking full scans are looking nice but because you are still using just as much space, the full scans run like a dog.

You export/import - you are still at 0.5M rows but now using much less space, Oracle still likes full scans, but now the high water marks are much lower and full scans are much more efficient.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse