Re: Rule Based Optimization Going Away?

Thanks for the update. It sounds like 'bind variable peeking' might help my situation, it certainly sounds more promising than the 'hard-coded' value distribution assumption made in V8. Basically all I'm trying to do is control the order in which multi-table joins are evaluated, based on my knowledge of the nature of the data.

Adrian Bowen

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:anjgur$frr$1$8302bc10_at_news.demon.co.uk...
>
> The primary issue with bind variables is that
> any relevant histograms cannot be used to
> determine the path.
>
> This changes (somewhat) in Oracle 9, where
> 'bind variable peeking' is introduced. On the first
> (hard) parse of a query with bind variables,
> Oracle checks the values of the bind variables
> and generates a plan based on those values.
> ALL subsequent soft parses of that statement
> then use the same plan - which may, or may not,
> be a good thing.
>
> There is also a 'cursor sharing = similar' option,
> which is normally used to deal with the cost
> of excessive amounts of similar, but literally
> different, SQl that appears from applications that
> generates strings. With this parameter setting,
> literals are converted internally to bind variables,
> and plans generated. If there are no histograms
> on any of the columns which would affect the
> plan, then this results in a one-off hard parse
> per "statement class" - however if any columns
> do have histograms, the values are re-inserted
> and a hard parse per statement results. (Of course,
> excessive histograms are bad news, so the hope
> is that on the whole, this setting will reduce
> parse costs).
>
> On top of this, Oracle 9.2 allows for dynamic
> (query run time) sampling of statistics - and
> this may turn out to be a good solution to
> some problems of skewed data.
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____USA__________November 7/9 (Detroit)
> ____USA__________November 19/21 (Dallas)
> ____England______November 12/14
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
> Adrian Bowen wrote in message ...
> >Hi,
> >
> >Apropos of the RBO/CBO switch, what is the 'approved' method of dealing
> with
> >queries which involve bind variables when using the CBO? As I understand
it
> >the table stats are of no use here, since the bind values are unknown at
> the
> >time the query plan is formulated by the CBO, so it just assumes (usually
> >wrongly) some value distribution in the column. I know this caused me
> >endless headaches when trying to use the CBO previously, since my
> >application uses bind variables exclusively. I eventually gave up, and
> >structured the queries manually to generate a sensible execution plan
under
> >RBO.
> >
> >Adrian Bowen
> >
>
>
>
Received on Fri Oct 04 2002 - 03:37:04 CDT