Re: Problem with indexes

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:bo81h8$1b1m09$1_at_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
> :)

It generally has a reason though.

>
> 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.

I have had a look at this thread and I'm not at all clear that you can apply it to the situation in this case. We don't know if there were stats or not to start with, nor if they are accurate or if there is some other issue with them. Selectively deleting stats might be helpful in specific cases but in general it is a poor tactic, and likely to cause problems elsewhere.

Incidentally did you ever log a TAR as was suggested.

-- 
Niall Litchfield
Oracle DBA
Audit Commission Uk