How do I force a query to use bind-aware cursor sharing?
来源:互联网 发布:sql for循环 编辑:程序博客网 时间:2024/04/27 00:42
By Allison on Feb 13, 2012
no_BIND_AWARE是恰恰相反的作用
Way back in our original post on adaptive cursor sharing, a commenter asked if there was a way to skip the monitoring for a query and go straight to bind-aware cursor sharing. We mentioned then that we would add a hint for this purpose, but we never addressed the new hint here on the blog. Starting in 11.1.0.7, the BIND_AWARE hint can be used to force bind-aware cursor sharing from the first execution. The hint only works if all of the other criteria required for bind-aware cursor sharing are met; for instance, the query must have binds, the binds must appear in where-clause predicates, and the columns in those predicates must have the proper statistics (e.g. a histogram) to allow the plan to actually change when the query is executed with different bind values.
Let's look again at the query we used in that original post on ACS.
select count(*), max(empno)
from emp
where deptno = :deptno;
Recall that in that example, we had to execute the query twice, with different bind values, before we switched to bind-aware cursor sharing. So we had to run the query a total of at least four times in order to populate the cursor cache with the two plans we want to use, depending on bind value. This is what the cursor cache looked like after 5 executions:
Now let's look at the behavior with the BIND_AWARE hint:
After two executions with two different bind values, there are two bind-aware child cursors. And as expected, the very selective bind value (9) used an index scan, while the not-so-selective bind value (10) used a full table scan:
Now that the cursor cache is populated with these two bind-aware child cursors, they will behave just as if they were created due to adaptive cursor sharing. For instance, if I run the query again with another very selective bind value, the first child cursor will be used.
Before you start using this hint all over the place, keep in mind that there is a reason why we monitor queries before deciding to use bind-aware cursor sharing by default. There are various forms of overhead from using this form of cursor sharing. These include:
- A small amount of additional cursor memory, to store the information we need to pick the child cursor to use for a particular bind value.
- Additional overhead for soft parse, since we have to compute the selectivity with the current bind value in order to decide which child cursor, if any, is right for that bind value.
- Additional hard parses, which may not actually generate different plans for different bind values.
- More child cursors, which may cause cursor cache contention.
Some of you might be wondering how you can use this hint for queries in a packaged application (where you cannot edit the query text). I will discuss this in my next post. You can get a copy of the script I used to generate this posthere.
- How do I force a query to use bind-aware cursor sharing?
- How a Cursor becomes Bind Aware?
- How to Use Cursor
- How do I use 'git reset --hard HEAD' to revert to a previous commit?
- How do I bind the values of an enum to a ComboBox?
- How do I find which phpize to use?
- How do I use software from a PPA?
- What is a cronjob and how do I use it?
- How do I use IStream?
- How do adaptive cursor sharing and SQL Plan Management interact?
- How do you force a java swt program to “move itself to the foreground”?
- How do I add a ProgressBar to a .NET StatusBar
- How can I force WebLogic to use third party jars that are included in my Ear?
- How to force NSLocalizedString to use a specific language iOS APP内国际化
- How do I autosize a button to fit its text
- How do I make games? A Path to Game Development
- [SVN]-How do I relocate a repository to another host?
- How do I make games? A Path to Game Development
- 元素的隐藏和显示(v-show指令)
- Android ZXing(二维码)库的全面使用解析
- maven整合springmvvc项目遇到的一些问题及解决方
- 【ORACLE】ORA-00845 MEMORY_TARGET not supported on this system
- Java进阶----EL表达式和JSTL
- How do I force a query to use bind-aware cursor sharing?
- Qt连接Sqlserv数据库设置端口号问题
- 寻找手机(本人自编题)
- PAT_A 1023. Have Fun with Numbers (20)
- Python实现冒泡排序
- Google要用机器学习检测恶意评论
- 深入理解C语言指针(四)之 函数
- 虚拟键盘弹出挡住textfield的分析以及解决办法
- so使用原则(要么不支持,要么全部支持) 固定的运行平台 指定目录:System.load("/data/data/<package-name>/mydir/libmath.so");