绑定变量与PEEKING --CBO 的痛 ？

lijietz

bind peeking 和histograms感觉互相制约。。
        
如果没用绑定变量,分析了列的histograms后就能得到更加精确的执行计划。
用了绑定变量就如biti所说，就看Peek得到的变量的值了。
              
不知道10g是否有改进？

rwq_

最初由 ylsired 发布
[B]最近在9i 做SQL TUNNING，经常发现EXPLAIN PLAN的执行计划与语句实际执行的计划(v$sql_plan中查询）不同，原因就是语句中多处使用了绑定变量，特别是对于日期范围查询，单证范围查询等用了 > ,< 操作符的条件，cbo往往无法选择到真正最佳的执行计划；而不使用绑定变量，就没有此问题，真是苦恼啊。
大家对此类问题又没有什么好的避免措施？ [/B]

找个折衷的办法，查询结果老是变化很大的，不用绑定变量、不然就用绑定。

liuyi8903

我认同楼上的看法，对于查询参数比较确定，就是说where条件的值相对比较固定的话，用常量来替代绑定将产生更加少的soft parse.否则反之。

我记得这个好像也是tom建议的！

Yong Huang

Follow-up to rwq_ and liuyi8903's two messages. It sounds like a good idea to not use bind variables if the bind values change significantly. The benefit comes from not only plan stability as you described, but possibly also bind length graduation. When the bind value length changes across a bind threshold, a different cursor version is created. For instance, first you bind 'short string' and later you bind 'a string longer than 32 chars...'. A new row is shown in v$sql for the same sql address and v$sqlarea.version_count increments, and v$sql_shared_cursor.bind_mismatch shows 'Y'. I.e., the first cursor is not shared. But the plan is probably still the same.

On the other hand, this benefit is much smaller. Would you not use bind variables simply because run-time bind values change length wildly? I have to think about an answer.

If what I write is not clear, let me know and I'll clarify.

Yong Huang

rwq_

最初由 Yong Huang 发布
[B]Follow-up to rwq_ and liuyi8903's two messages. It sounds like a good idea to not use bind variables if the bind values change significantly. The benefit comes from not only plan stability as you described, but possibly also bind length graduation. When the bind value length changes across a bind threshold, a different cursor version is created. For instance, first you bind 'short string' and later you bind 'a string longer than 32 chars...'. A new row is shown in v$sql for the same sql address and v$sqlarea.version_count increments, and v$sql_shared_cursor.bind_mismatch shows 'Y'. I.e., the first cursor is not shared. But the plan is probably still the same.

On the other hand, this benefit is much smaller. Would you not use bind variables simply because run-time bind values change length wildly? I have to think about an answer.

If what I write is not clear, let me know and I'll clarify.

Yong Huang [/B]

也许你所说的情况是可能存在的，我们在很多情况下只能解决大部分的问题，而不能解决所有的问题。

biti_rainy

最初由 Yong Huang 发布
[B]Follow-up to rwq_ and liuyi8903's two messages. It sounds like a good idea to not use bind variables if the bind values change significantly. The benefit comes from not only plan stability as you described, but possibly also bind length graduation. When the bind value length changes across a bind threshold, a different cursor version is created. For instance, first you bind 'short string' and later you bind 'a string longer than 32 chars...'. A new row is shown in v$sql for the same sql address and v$sqlarea.version_count increments, and v$sql_shared_cursor.bind_mismatch shows 'Y'. I.e., the first cursor is not shared. But the plan is probably still the same.

On the other hand, this benefit is much smaller. Would you not use bind variables simply because run-time bind values change length wildly? I have to think about an answer.

If what I write is not clear, let me know and I'll clarify.

Yong Huang [/B]

如果只是考虑到变量长度的原因，但是变量的值本身还是很多的话，还是使用 bind  var为好的。毕竟，oracle在给变量定义长度的时候的区间还是很少的，具体是哪几个我不清楚，可能就是比如

32/64/128/256/1024/2048/4000   这样的少数值了。这样长度变化带来的不同的hard  parse毕竟还是少数。