RE: adaptive cursor sharing and bind peeking

> May be I have not understood clearly the original poster question.
> For me the question was whether the CBO (for cursors that are bind aware)
> will always peek at the passed bind variable and optimize it or not?

The OP never asked whether a re-optimization was performed. Instead, he wanted to know whether bind peeking was taking place. So, the "and optimize it" part was added by you.

> This is why I answered that even if the cursor is bind aware the CBO will not
> always use the passed bind variable to optimize a new plan (child cursor).

This is true. Optimization is not done every time. But, bind peeking is done every time.

> Instead it will share the plan that has been optimized for the bind variable having the
> same "range of selectitivity" as the passed bind variable

To estimate the range of selectivity and select the best child cursor bind peeking must take place.

In summary: To me it seems you are implying that "bind peeking" = "re-optimization". However, these are two completely different functionalities. Bind peeking might take place even though a re-optimization is not performed.

HTH
Chris Antognini

Troubleshooting Oracle Performance, Apress 2008 http://top.antognini.ch

--
http://www.freelists.org/webpage/oracle-l