Re: reducing LIO's
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: sjaffarhussain@xxxxxxxxx
- Date: Thu, 31 Mar 2005 08:27:50 -0700
I was watching with amazement how people jumped onto index suggestions
without as so much as question the data distribution. Maybe ORDT_STS='K'
is so unselective, i.e. most of the orws have a value of 'K' for
ORDT_STS, that the optimizer is correct in not using an index - assuming
there is one. Likewise for ORDM_STS IN ('C','G','L'). Or, maybe those
latter are the exception but without a histogram the optimizer wouldn't
know it.
To answer the OP's question - NO, indexes do not necessarily reduce LIO,
possibly on the contrary. Also, what is the goal? Reducing LIO or
improving performance. Those two are not necessarily the same.
My advice: Run the sql with sql_trace on and compare the rowcounts with
the optimizer's cardinality estimates for the corresponding steps. Look
for vast discrepancies (orders of magnitude, multiples of hundreds or
thousands). Then look for reasons why the optimizer miscalculated its
estimate. If the estimates are fairly accurate then I am convinced that
the optimizer does the right thing, given the material to work with. If
that is not good enough you have to look at measures like MV.
The Human Fly wrote:
> Well, I have done it. I have created combination index on those two
> colums and when my query was not using the index, I have even forced
> it to use, it reduces the cost but buffer_get were very high. My
> question was, LIO can be reduced by creating indexs, if so, why my
> buffer_gets were higher with INDEX hint?
> Well, we can't avoid this query as it is mandatory query for every
> trading transaction.
>
>
--
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
