Re: NL cost question
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: jclarke@xxxxxxxxxxxxxxx
- Date: Tue, 01 Feb 2005 14:43:09 -0700
Actually, rsc_io the rsc_io cost "formula" is something like
rsc_io = lvls + IX_SEL*#LB + TB_SEL*CLUF
Depending on the index access type (scan, no sta/stp key, index-only,
join-index, index-eq, ...) parts of that may be omitted, e.g. for
index-only the tb_sel*cluf component does not apply, of course, and for
index-eq it reduces to just lvls.
For your index access types, join-index and index-only, the ix_sel*#lb
part ought to apply, and for join-index even the tb_sel*cluf component -
at least I have seen values in 10053 traces that support that theory,
certainly much higher than the index height (lvls). The IX_SEL values
for the F and _ indexes are certainly high enough to make the ix_sel*#lb
component "disappear". Check if the same is true for the E index.
John Clarke wrote:
> I've got a query that CBO is generating a "bad" plan for and was wonderi=
> ng whether anyone could shed some light ...
>
>
> Am I interpreting this correctly=3F And if so, what can be done about i=
> t short of hints and/or seeding bogus blevel statistics=3F Is this situ=
> ation the join uniformity assumption fallacy that I've read about, or am=
> I misinterpreting the statistics=3F
>
I don't believe this has anything to do with the "join uniformity
assumption fallacy" since the CBO got the cardinality right. It is a
matter that it has the choice of indexes that are equally suited and the
cost is marginally different and the actually less suitable index happen
to have a slightly lower cost.
What can you do about it? The only predicate that is responsible for
choosing index E is business_unit and since you have another index with
business_unit you could change the column order of the E index, making
it ineligible for this join, or only through a skip-scan which should
have a higher cost.
A negative effect of such an index change could be that queries with
just business_unit as a predicate will be forced to use the ps_ index
and that appears to have more levels and especially more #LB and a
potentially a higher CLUF and will therefore perform worse.
--
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
