Re: Rule Based Optimization Going Away?

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

>