Re: Extreme Performance Degradation on 7.3.4

can you please explain what do you mean by "physically moved" on the system ? were the datafiles were relocated to different drives ?
the significant drop in the cache hit ratio (from 85% to 30%) is an indicator that there was a much larger burden placed on to the i/o subsystem after the move.

when the rows were "deleted", the high water mark remained, the 500K data rows data may have ended up sparsely populated over the space previously
occupied by the 1200K rows, and after an analyze ... compute statistics, the cost based optimizer may have begun deciding that a full table scan was more
appropriate than an index lookup, or vice versa. the altered query plans could have a significant impact on queries using nested loop joins.

it's possible that the cost based optimizer generating began query plans different than the ones generated reviously, either because statistics were not recomputed, or were re-estimated inaccurately. deleting 700K rows would have probably have some impact on the selectivity of at least some of the indexes...

perhaps one or more indexes were inadvertantly dropped ?

if statistics for all of the tables and indexes were not re-computed, then it is possible that the optimizer was scanning the tables to determine statistics each and every time a query was run... (or did the optimizer not start doing that until 8.0)

i'm kinda grasping here...

"Phill Giancarlo" <pgiancarlo_at_snet.net> wrote in message news:DspM4.59$lF.7558_at_typhoon.snet.net...
> 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