Re: using bind variables makes the optimizer choose a bad plan

On Thu, 08 Sep 2005 05:33:32 -0700, EdStevens wrote:

> That's one of the trade-offs when deciding to use bind variables with
> highly skewed data. When dealing with bind variables, the optimizer
> has no choice but to assume an even distribution of data values. So,
> as in everything else, the answer to the question of whether to use
> bind variables is "it depends."

Things like that are resolved by using histograms. Histogram is the tool to describe the value distribution for the particular column. Here things get slightly complicated because the OP is using Oracle8i which, if my memory serves me right, assumed that the value of the bind variable will have frequency of the NUM_ROWS/4 or 25% of the table. In 8i, hints were the only cure for such a situation.
Oracle 9i changed the things and introduced the notion of "bind variable peeking". In Oracle9i the optimizer will, as a part of a hard parse, take a value from the bind variable and use histograms to determine the proper access path. If you remember, in Oracle7 they introduced "deferred parsing". Statement was not actually parsed, until it was executed for the first time. This postponement of the parsing is what made the bind variable peeking possible. The optimizer will use the peeping Tom technique only during the hard parse and will continue to use the same plan after that. One of the eagerly awaited improvements of Oracle 9.2.0.7 is that CBO will no longer use variable peeping, if the relevant histograms aren't there.

Second, Oracle8i didn't know anything about things like the average attained multiblock read, average time for a single block read or average time for the multiblock read, so its decision was based on much less information, which resulted in bad plans and forced tampering with the "optimizer_index" parameters, especially for the OLTP sites. This has effectively turned CBO into RBO. Index was used if it was there, even if it didn't make sense. That can also be a solution, if the user for some reason cannot upgrade to Oracle 9.2.

-- 
http://www.mgogala.com