Re: reducing LIO's
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: The Human Fly <sjaffarhussain@xxxxxxxxx>
- Date: Fri, 1 Apr 2005 06:36:05 +0100
comments in-line
On Mar 31, 2005 3:56 PM, The Human Fly <sjaffarhussain@xxxxxxxxx> 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.
curious, if the cost using the index were really lower than the cost
for the plan without the index, then the CBO will pick that execution
plan. If however the CBO didn't use an index that I thought it should
then my first reaction would not be, lets see if we can get it to use
the index, but who is missing some information here me or the
optimizer (usually it turns out to be me). in your case however it may
be the cbo. The cbo reckons your query will return 2000 rows, but in
fact it is only returning 54. (so it is wrong by a factor of 37) -
investigating why this is so will - as wolfgang has suggested - likely
be helpful.
> My
> question was, LIO can be reduced by creating indexs, if so, why my
> buffer_gets were higher with INDEX hint?
lio might be reduced or might be increased by using indexes
> Well, we can't avoid this query as it is mandatory query for every
> trading transaction.
You might still be able to avoid the sort if the order by is not mandatory.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
