关于绑定变量，直方图，和bind peeking

Yong Huang

原帖由 zergduan 于 2008-12-4 17:04 发表

版主，我记得如果将bind peeking关闭，将回到9i之前的方式，直方图不会被用于计算，东南西北将使用25%被考虑，这样的执行计划并不好，我认为既然lz可以修改sql，还不如不使用绑定变量，大不了硬解析多一些罢了

It depends on the application. It's OK if hard parsing doesn't cause too much library cache latch contention and shared pool memory pressure to the level that can't be tolerated.

If you search on the Internet, you'll find that many people don't like bind peeking. One reason is that users would rather have predictable performance than performance problems that can't be (easily) explained. In the particular case of 4 values of directions (东南西北), turning off bind peeking thus not using histograms probably causes the SQLs querying 南,西,北 to use an index, which is correct. So at least the users and DBAs know queries of these three values always run OK and the query of 东 always runs bad. With bind peeking, there's no telling when it's OK when it's not.

Many shops take the approach of writing two queries as beyond_sea mentioned. I think it's a good solution. Otherwise, use higher-than-default-level dynamic sampling. I think Tom Kyte shows an example of it to overcome this dilemma.

Yong Huang

beyond_sea

原帖由 Yong Huang 于 2008-12-6 14:04 发表


It depends on the application. It's OK if hard parsing doesn't cause too much library cache latch contention and shared pool memory pressure to the level that can't be tolerated.

If you search on the Internet, you'll find that many people don't like bind peeking. One reason is that users would rather have predictable performance than performance problems that can't be (easily) explained. In the particular case of 4 values of directions (东南西北), turning off bind peeking thus not using histograms probably causes the SQLs querying 南,西,北 to use an index, which is correct. So at least the users and DBAs know queries of these three values always run OK and the query of 东 always runs bad. With bind peeking, there's no telling when it's OK when it's not.

Many shops take the approach of writing two queries as beyond_sea mentioned. I think it's a good solution. Otherwise, use higher-than-default-level dynamic sampling. I think Tom Kyte shows an example of it to overcome this dilemma.

Yong Huang

调优真的是一门艺术,需要在各种方面之间权衡.谢谢版主.

owlstudio

"
Otherwise, use higher-than-default-level dynamic sampling. I think Tom Kyte shows an example of it to overcome this dilemma.
"

这个我还没查到， Yong Huang 能否详细讲讲？

Yong Huang

原帖由 owlstudio 于 2009-2-19 02:31 发表
"
Otherwise, use higher-than-default-level dynamic sampling. I think Tom Kyte shows an example of it to overcome this dilemma.
"
这个我还没查到， Yong Huang 能否详细讲讲？

It took me a while to find Tom's article. Now I'm reading it, his use of dynamic sampling is not to solve the problem of unlucky bind peeking; instead it's to solve the wrong cardinality estimate. But I think there's still some relevance here. Let me think about it.

See Q&A "Why the Cardinality Difference?" at
http://www.oracle.com/technology ... -may/o38asktom.html

Also read the link referenced in it:
asktom.oracle.com/tkyte/DynamicSample.html

Yong Huang

kewin

原帖由 zergduan 于 2008-12-5 12:49 发表
不至于吧，向你说的一共就4个sql能造成4031?你share_pool也太小了...

改为常量后，每次都会做hard parse？在大并发的情况下，这样得不偿失吧。

yyp2009

up

liqy103

to sharing with my experiment in short.

" In some situations bind peeking can occur when it should not eg: Bind peeking can occur for user binds even if  "_optim_peek_user_binds" is set to FALSE. "
except from Query using Bind Variables is suddenly slow [ID 387394.1]

I tried in 10.2.0.4 and failed to avoid bind variable peeking.

In 11.2.0.2, I failed to simulate adaptive cursor sharing .  (need to drill down further)

but for both version, literal SQL works fine.

wang1352083

学习了.

gangang

个人感觉，peeking导致的问题比它解决的问题多。在正确的时间，peeking到正确的变量，何其幸也！

注意在10.2.0.3，关闭_optim_peek_user_binds 也未必管用

(unpublished) Bug:5082178 (fixed in 10.2.0.4 and 11.x).

Details:
In some situations bind peeking can occur when it should not eg: Bind peeking can occur for user binds even if  "_optim_peek_user_binds" is set to FALSE.
This can cause binds to be marked "unsafe" leading to cursors not being shared when they should be.
This fix is notable as plan changes could occur if statements suffering this problem execute in a release with this fix as the CBO will no longer have peeked data to use when determining an execution plan.

wang1352083

直方图的数据要做 搜集的吧.而且数据量小的时候,也会full scan的