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

"EscVector" <Junk_at_webthere.com> wrote in message news:1168025465.514691.206890_at_42g2000cwt.googlegroups.com...
>
> Jonathan Lewis wrote:
>>
>> 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.
>

But other people will cheerfully say:

    set optimizer_index_cost_adj to 5
or

    set optimizer_index_caching to 90
or

    set pga_aggregate_target to 2GB
or

    set workarea_size_policy to manual
or

    set db_file_mulitblock_read_count to 128 or

    set db_file_mulitblock_read_count to1

Because it's a no-brainer and has worked for them (possibly once).

How many "no effort, no-brainer" chances does DBdude have for modifying a production system with each of these changes in turn before he's wasted a lot of effort and time, annoyed a lot of people, and lost credibility.

You could at least have supplied an argument in favour of your suggestion so that DBdude had an option for deciding whether or not your argument applied to his system. (Which could also have allowed others to offer reasons why the suggestion might not be appropriate).

Now, if you had said - set optimizer_index_cost_adj to 250, because this will, to a fair degree, offset 10g's automatic cpu_costing mechanism if you have the typical 8KB block size with the common db_file_multiblock_read_count of 8, which might get you back to plans more like the old 8i plans - then I would have been impressed. And I would still have warned DBDude not to do it, because it may safe effort now, but it increased effort in the future.

Possibly the only sensible "quick and dirty" option at this point is to set optimizer_features_enable to 8.1.7, and restart the database. But even then, I wouldn't assume that this help because DBdude seems to have gone from Rule-Based 8i to Cost-Based 10g, and that's two major migration steps in one - there is NO easy option.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html