Re: Hints
From: Neil Chandler <neil_chandler_at_hotmail.com>
Date: Mon, 15 Aug 2011 21:25:04 +0100
Message-ID: <BLU0-SMTP13F8CCF316FCAEEEACF6EC85260_at_phx.gbl>
There are 2 problems with using optimizer_index_cost_adj. Tim covered the first extensively (it's a problematic parameter). However, the main reason for me is that you shouldn't use global parameters to get around a problem with a few of sections of code. There are so many other options to control optimisation; object stats, histograms, system stats, hints, outlines, profiles and baselines to name a few. as well as rewriting the code of course.
Date: Mon, 15 Aug 2011 21:25:04 +0100
Message-ID: <BLU0-SMTP13F8CCF316FCAEEEACF6EC85260_at_phx.gbl>
There are 2 problems with using optimizer_index_cost_adj. Tim covered the first extensively (it's a problematic parameter). However, the main reason for me is that you shouldn't use global parameters to get around a problem with a few of sections of code. There are so many other options to control optimisation; object stats, histograms, system stats, hints, outlines, profiles and baselines to name a few. as well as rewriting the code of course.
Try not to use the sledgehammer of global change unless you have global problems and will see an overall net benefit from such an implementation.
Regs
Neil Chandler
sent from my phone
On 15 Aug 2011, at 18:13, "MacGregor, Ian A." <ian_at_slac.stanford.edu> wrote:
> It may be that Oracle has incorrect statistics concerning your I/O system, or even if those statistics are good, has still misjudged its capabilities. It may be the OS statistics were gathered at an inappropriate time. I am of the opinion that Oracle still overrates full table scans as compared to index lookups. There is an initialization parameter which addresses this.
>
> optimizer_index_cost_adj
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Aug 15 2011 - 15:25:04 CDT