Re: reducing LIO's

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.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 31 2005 - 14:16:08 CST