Re: Suggestions please - Query loading 98%

BN.Sarma wrote:
>
> 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
> > >

The main thing newer versions give you (putting aside all the hyped new features that come) is a much better optimizer each time.

But that aside, when looking for first, last of a set, then something using analytic functions (8.1.6+) can be a very efficient method.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."