Re: reducing LIO's
- From: "Terry Sutton" <terrysutton@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 31 Mar 2005 11:11:50 -0800
I was also surprised that no one asked about the data distribution, but I
read my eamil later in the day than Wolfgang. :-)
I would also add the basic question-- Have you analyzed the tables recently?
(recently meaning "at some time when their size/data distribution were
similar to what they are now"). Bad information given to the optimizer will
result in sub-optimal execution plans.
--Terry
----- Original Message -----
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
