RE: Bind variable peeking and Dynamic sampling

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_at_centrexcc.com]

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.

--

http://www.freelists.org/webpage/oracle-l Received on Wed May 09 2007 - 14:00:53 CDT