Re: Query Slow in 10g, runs very fast in 8i

Jonathan Lewis wrote:
> "EscVector" <Junk_at_webthere.com> wrote in message

> > alter session set optimizer_index_cost_adj=10000;
> >
> > run the sql in sqlplus or add this line to the procedure.
> >
> > You can set it back after the procedure is complete.
> >
> > See what happens.
> >
>
> What's the rationale behind that suggestion ?

I think you said it best: "The first (and only) rule of optimization is: "Avoid unnecessary effort".

This no brain approach avoids people effort. If it doesn't work, add brain.

The other simple answer is I've had it work in the past when we had to make very quick adjustments to things we couldn't test or spend time researching and that couldn't be changed.

 Of course there are many factors and I don't like to guess. That said, if dbdude looks at an xplan or 10053 he may have an answer and can ignore my maverick suggestion.

 If he has no time, throwing the optimizer to the complete other end may help. I'm suggesting setting it for that session only, based on experience, and having nothing much else to go by in this thread.

Time is money. If he sets this and it works, and it is low impact, good. In the meantime while this is being run, he can do further research as indicated by Mr. Hooper's reference to your blog.

This no brain approach avoids people effort which is usually the most costly component to any effort. To something while your doing the real thing. That's the rational. Received on Fri Jan 05 2007 - 13:31:08 CST