RE: Bind variable peeking and Dynamic sampling
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>, "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 May 2007 12:00:53 -0700
The plan for a large range works well in all cases, via nested loops and
index lookups. The key here is that regardless of the cardinality, the
application is only going to actually fetch the first 11 rows, so while
the indexes would be horrible for a large range if it actually fetched
them all, they work great for returning just the first 11 rows and then
stopping there. It is my understanding that this behavior is encouraged
by the FIRST_ROWS(n) hint - it optimizes for response time, i.e.
returning the first rows ASAP, without regards to how long it will take
to return all the rows. That is why the FIRST_ROWS hint, from my
experience, seems to force the CBO to favor NL joins to SM joins -
becase a NL join can return the first row right away vs. a SM join,
which has to sort and merge ALL rows before it can return the first row
to the client. Am I misunderstanding something there?
Oddly, in this case, it is actually the *smaller* cardinality (where
t$orno >= 983995) case in which the CBO is choosing to perform the SM
join while it chooses NL joins for the larger cardinality executions.
This is counterintuitive to me - I'm still not sure why the CBO is
choosing the SM join for smaller cardinalities, but I guess it thinks it
can perform them faster than the NL joins for some reason.
I don't really want to tinker with the low and high value stats - this
column is used in the predicates of many queries and I could do more
harm than good.
Unfortunately the hints are plugged in by the Baan database driver and I
have very little control over them. I can't make them send a
cardinality hint - only FIRST_ROWS(n) and INDEX hints. Good idea
though.
Thanks!
Brandon
-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw@xxxxxxxxxxxxx]
What about the reverse? How does a plan established for a large range
perform for a small range?
I see a few strategies to deal with this.
b) since it is the low_value and high_value which "cause the problem"
one could alter their values to have the CBO choose the desired plan.
c) Since you already use hints, you could use a cardinality(a,1) hint to
overwrite the selectivity and thus cardinality from the variable range
predicate.
as for "You'd think the FIRST_ROWS(10) hint would be enough to keep the
CBO from performing a SORT MERGE join", not at all.
FIRST_ROWS(10) only means that the access plan should be created with
the priority to return the first 10 rows fast, not that only 10 row will
be retrieved. If the cardinalities are large enough, sort merge joins
can easily outperform a cascade of NL joins
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions and
other information in this message that do not relate to the official business
of this company shall be understood as neither given nor endorsed by it.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
