Hi Tanel,
thanks a lot for your wonderful explanation.
sorry to pester u you again with my doubts on
the first day of the week.
My only doubt is:
1.After analyzing the index,CBO doesn't read the index because
it is costlier in terms of I/O and other resources.
2.But without analyzing,CBO uses the index now,which is supposed
to be coslty,and hence incurs the same I/O and other resources .
3.when the role of CBO is over,the h/w resources comes into play
to read the index.even now it reads the full index and the
response is faster.how is it so ?
hope i am clear in telling u what i had understood ?!
plz. excuse me if i'm sound silly and stupid.
of the above 3 points,where am i wrong ?
Regards,
Jp.
25-7-2003 21:24:45, "Tanel Poder" <[EMAIL PROTECTED]> wrote:
>Hi!
>
>Your original post shows that when index was not analyzed, optimizer
used
>default statistics and found out that index scan is quite cheap.
Also, no
>sorting had to be done, since descending index range scan could be
used to
>satisfy your order by clause.
>
>Whe index was analyzed, then CBO actually saw, that index isn't that
good as
>defaul values showed, the number of leaf blocks was higher and also
the
>clustering factor was probably much-mugh higher, meaning that for any
>non-unique index key value it has to visit several different data
blocks to
>get all matching rows. That means lot's of IOs. Eventually CBO
decided that
>it's cheaper to do let say 1600 multiblock (plus one segment header +
>possibly bitmap) reads directly and scan through the whole table than
to
>traverse through index branches, scan leaf blocks and visit every
data block
>individually.
>
>So, with not-analyzed index, CBO had nothing else to do, than to be
>super-optimistic about the index (#LB, CLUF) thus using index in your
case.
>But when analyzed, CBO had accurate data, but did make bad decision
because
>too pessimistic values for optimizer_index_cost_adj and
>optimizer_index_caching. You should set them at session level for
testing to
>let say 50 and 90, but read the document below for understanding
those
>parameters.
>
>http://www.evdbt.com/SearchIntelligenceCBO.doc
>
>Cheers,
>Tanel.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Prem Khanna J
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
