Re: Table access slow

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Tue, 10 Feb 2004 21:18:20 +0100
Message-ID: <c0bdmg$2do$1@news4.tilbu1.nb.home.nl>

Felipe Mart?nez wrote:

>>Reading it all, some questions:
>>- are you sure you did what you described, and just truncated
>>   the table (and not drop it - in which case you could miss indexes)?

>
>
> First I truncated the table. Didn't work
> Then I regenerated indexes. Didn't work
> Then I droped the table and regenerated table, indexes,
> views...etc. Didn't work
>
> Next: I will try to explain plan and analyze indexes. I don't
> think it is an index problem it goes slow both with and without
> indexes.
>
>

>>- post explain plans - even if you cannot change the statements, it can
>>   point in a direction with a bit more accuracy than this stabbing in
>>   the dark.
>>- Did you analyze the indexes as well? Correct me if I'm wrong, but a
>>   ROT was to analyze the index, and estimate the table.
>>- Did you have statistics in the first place? 7.3 CBO was not up to
>>   the standard of 9i/10i (which I'm currently downloading...).
>>   In your test/acceptance environment, drop all stats and see if
>>   performance is back to 'normal'. Only then do so in production.

>
>
> ROT? CBO? Sorry, but I don't understand the third point.
Rule-of-Thumb, Cost Based Optimizer

I
> recolected statistics before doing anything and I was confident it
> would work like many other times, so I lost those statistics. But I
> remember cache hitratio over 93%. Statistics I am collecting are from
> v$ tables which reset everytime the database starts up (don't they?)
>
>
> Thanks for your advise. I will post later with explain plan and
> indexes analyzed.

The question was: did you ever try without _any_ statistics? I've seen collecting statistics (and using the Cost Based Optimizer, which *will* kick in if optimizer_mode=choose, sometimes even with optimizer_mode=rule) with these early versions often do more bad than good.

And statistics meaning:
  analyze table xxx estimate statistics;   analyze index xxxxy compute statistics;

See Oracle 7, release 7.3 Tuning mnl, part# A32537-1

-- 

Regards,
Frank van Bortel

Received on Tue Feb 10 2004 - 14:18:20 CST