Re: Problem with indexes

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Tue, 4 Nov 2003 12:08:56 +0100
Message-ID: <bo81h8$1b1m09$1@ID-152732.news.uni-berlin.de>

"Katarina Laclavikova" <klaclavikova_at_europe.com> schrieb im Newsbeitrag news:bbd8a2fb.0311030725.25c3ae98_at_posting.google.com...
> Hi all
> Last weekend, l was move index tablespace to another disk. After that,
> application running more slower.
> It doesn't use some indexes. I was rebuild indexes, collect statistics
> and application is still running slowly.
> ( SELECT which was running 20 sec, now is running 1 hour )
>
> Any comments or suggestions appreciated.
>
> Thanks
> Katarina
>
>
> Katarina Laclavikova
> klaclavikova_at_europe.com

Hi,

just recently I had a very similar situation with a Report query degrading from 30 sec to 1 hour.
The reason was that the desired index was only taken when it had *no* statistics,
but when analyzed, it was ignored ... the CBO can do weird things sometimes :)

If You want to throw away statistics of that Index, use ANALYZE INDEX ... DELETE STATISTICS
instead of DBMS_STATS.DELETE_INDEX_STATS( ), see my thread from October 28th.
DBMS_STATS has been confirmed to be buggy.

hth, Jan Received on Tue Nov 04 2003 - 05:08:56 CST