Re: Suggestions please - Query loading 98%

Greetings Kenneth and Jonathan,

Thank You for the suggestions.

I don't see too much of disk sorting.
I do follow Jonothan, Steve, and Toms articles and answers. For the same reason I didn't think about BMI. I do rebuild the index immediatley after the delete. Infact I have suggested our Production guys that I can copy the info to a temporary table where status='WAITING' and truncate the table, then copy back the info from the temporary table. Thye are yet to consider it. I do analyze (estimate sample 10 percent) the schema every day as an oracle job
using dbms_utility.

Jonothan, if its newer version you said we could have tried some tricks, could you please give me more info on it. I am desperatley pushing our guys to go for 9.2.x. Since it needs hardware change (thanks to Oracle for 64bit) its taking time for the $$$$ approval. There is no 32 bit release of 9i for AIX.

Regards & Thanks
BN

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<ao696o$cmf$1$8302bc10_at_news.demon.co.uk>...
> Kenneth,
>
> Perhaps the best (certainly the laziest) answer
> I can give in the forum is a pointer to an article
> I wrote recently for http://www.dbazine.com
> about how bitmap indexes are structured and
> how they work.
>
> If you prefer it in Russian, it's also at:
> http://ln.com.ua/~openxs/articles/jlewis3_ru.html
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____USA__________November 7/9 (Detroit)
> ____USA__________November 19/21 (Dallas)
> ____England______November 12/14
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
> kennethkoenraadt_at_no-spam.hotmail.com wrote in message
> <3da6874c.3453706_at_news.mobilixnet.dk>...
> >Hi Jonathan,
> >
> >You are probably right all over the line. Especially the part about
> >redesigning the application. The first thing I learned about tuning :
> >"90% of performance gain comes from the application"
> >apparently still seems to be true.
> >
> >I just have one question, that's your part about bitmap indexes :
> >
> >"Single row updates are likely (though not guaranteed)
> >to make bitmap indexes explode if you are updating
> >the indexed column"
> >
> >I noticed that the MSG_STATUS column is only updated within the
> >existing range of values (WAITING,SUCCEEDED, FAILED). Updating the
> >column from one of these values to another should only consist of
> >changing a bitmap from '101' to '100' or so.
> >
> >I do not understand how that can make the index "explode" ?
> >
> > If new values of MSG_STATUS were introduced continously, I clearly
> >understand the problem. But that is not the case here.
> >
> >
> >- Kenneth Koenraadt
> >
Received on Fri Oct 11 2002 - 10:28:53 CDT