为了改善从包含绑定变量的查询中选择的执行计划 ，在Oracle Database 11g中，Oracle引入了一个名为自适应游标共享（ACS)的新特性。该特性会导致相同的，包含绑定变量的查询上有多个游标。在本文中，我们将解释为何会这样。在开始详细介绍前，让我们稍微回顾一下历史。
在Oracle 9i时，Oracle引入了绑定变量窥探，利用绑定变量窥探，优化器在第一次游标调用时，窥探用户定义的绑定变量的值。这就允许优化器明确WHERE子句的选择性，就好象绑定变量被常量所替代了，这会改善使用绑定变量的语句的执行计划的生成质量。
但是，当WHERE子句中使用绑定变量的列上存在数据倾斜时，使用该方法时有一个问题。假设有个列上存在数据倾斜，那么很可能在收集统计信息时会在其上创建直方图。当优化器窥探用户定义的绑定变量的值并选择执行计划时，它不能保证该执行计划对绑定变量上可能的所用值，都是正确的。换句话说，该执行计划对窥探到的绑定变量的值是最优的，但不是对所有可能的值都是最优的。
从Oracle Database 11g起，对于使用了绑定变量的单个语句，优化器允许其使用多个执行计划。基于绑定变量的值，来确保最优的执行计划被使用。让我们通过一个示例了解其到底是如何工作的。
假设我有一个EMP表，包含有100,000行，并且在DEPTNO列上有一个名为EMP_I1的索引。
SQL> desc emp Name Null? Type ---------------------- -------- ---------------------------------- ENAME VARCHAR2(20) EMPNO NUMBER PHONE VARCHAR2(20) DEPTNO NUMBER复制
在DEPTNO列上，存在数据倾斜，因此，当我在EMP表上收集统计信息时，Oracle会自动在该列上创建直方图。
SQL> select table_name, column_name, histogram from user_tab_cols; TABLE_NAME COLUMN_NAME HISTOGRAM ------------------ ------------------ --------------- EMP DEPTNO HEIGHT BALANCED EMP EMPNO NONE EMP ENAME NONE EMP PHONE NONE复制
现在，我将在我的EMP表上执行一个简单的SELECT语句，在WHERE子句中只有一个在DEPTNO列上的谓词（译者注：指过滤条件）。该谓词包含绑定变量。我们从为该绑定变量使用值9开始。在该表上，值9共出现了10次，即占总行数的0.0001。
SQL> exec :deptno := 9 SQL> select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno; COUNT(*) MAX(EMPNO) ---------- ---------- 10 99复制
考虑到值9的选择性，我们期望为该查询得到一个使用索引范围扫描的计划。让我们看看执行计划。
SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------- SQL_ID 272gr4hapc9w1, child number 0 ------------------------------------------------------------------------ select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno Plan hash value: 3184478295 ------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| ------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | 1 | SORT AGGREGATE | | 1| 16 | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)| | 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)| ------------------------------------------------------------------------复制
如我们所愿，使用了索引范围扫描。让我们看一下该语句的执行数据。
SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select /*ACS_1%'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE ------------ ---------- ----------- ----------------- ------------- 0 1 53 Y N复制
你会看到我们有一个子游标且已执行1次，并且有少量的buffer gets（译者注：从db buffer cache中读取的数据块次量，即逻辑读)。我们还看到该游标已经被标记为绑定敏感的。如果优化器认为最优的执行计划可能取决于绑定变量的值，就会标记游标为绑定敏感的。当游标被标记为绑定敏感后，Oracle监控该游标使用不同绑定变量值时的行为，来决定是否为不同的绑定变量值使用不同的执行计划。而本游标被标记为绑定敏感，是因为DEPTNO列上的直方图被用于了计算谓词"where deptno = :deptno"的选择性。由于直方图的存在，表示该列是倾斜的，不同的绑定变量的值可能应使用不同的执行计划。现在，让我们改变绑定变量的值为10，它是DEPTNO列上最常见的值，它在表中出现了99900次，即占到了总行数的99.9%。
SQL> exec :deptno := 10 SQL> select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno; COUNT(*) MAX(EMPNO) ---------- ---------- 99900 100000复制
我们预计会得到和之前相同的执行计划，因为Oracle最初会假定该执行计划是可以共享的。让我们检查一下：
SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- SQL_ID 272gr4hapc9w1, child number 0 --------------------------------------------------------------------- select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno Plan hash value: 3184478295 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | 1 | SORT AGGREGATE | | 1| 16 | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)| | 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)| ---------------------------------------------------------------------复制
该执行计划仍然是之前的索引范围扫描，但是，如果我们看一下执行数据，我们会看到两次执行，在buffer gets的量上比我们之前看到的，有一个大的跳变。
SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select /*ACS_1%'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE ------------ ---------- ----------- ----------------- ------------- 0 2 1007 Y N复制
你还应注意到，该游标仍然仅被标记为绑定敏感，但此时并不是绑定感知的。因此，让我们再次使用同样的常见值10，再次执行。
SQL> exec :deptno := 10 SQL> select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno; COUNT(*) MAX(EMPNO) ---------- ----------- 99900 100000复制
在前两次执行的幕后，Oracle监控着查询的行为，并确定不同的绑定变量值，引起的查询所操作的数据量有了显著不同，基于该不同，Oracle“适应”该行为，以便同样的执行计划不会总是用于该查询。所以，一个新的执行计划会基于当前的绑定变量值10而生成。让我们检查一下新的执行计划是什么。
SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT -------------------------------------------------------------------- SQL_ID 272gr4hapc9w1, child number 1 -------------------------------------------------------------------- select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno Plan hash value: 2083865914 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 240 (100)| | 1 | SORT AGGREGATE | | 1 | 16 | | |* 2 | TABLE ACCESS FULL | EMP | 95000 | 1484K | 240 (1)| --------------------------------------------------------------------复制
考虑到值10在表上是如此的没有选择性，我们并不奇怪新的执行计划是全表扫描。现在，如果我们显示执行数据，我们应看到额外的子游标（1号）已经被创建了。1号游标应该会显示buffer gets的量小于0号游标，并且，它被标记为绑定敏感和绑定感知。一个绑定感知的游标可以为不同的绑定变量值使用不同的执行计划，其取决于包含绑定变量的谓词的选择性。让我们看一下执行数据：
SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select /*ACS_1%'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE ------------ ---------- ----------- ----------------- ------------- 0 2 1007 Y N 1 1 821 Y Y复制
我们看到有一个新的游标，其表示使用了全表扫描的执行计划。但是，如果我们使用更有选择性的绑定值，再次执行该查询，我们应看到使用索引的执行计划：
SQL> exec :deptno := 9 SQL> select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno; COUNT(*) MAX(EMPNO) ---------- ---------- 10 99 SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------ SQL_ID 272gr4hapc9w1, child number 2 ------------------------------------------------------------------------ select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno Plan hash value: 3184478295 ------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| ------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | 1 | SORT AGGREGATE | | 1| 16 | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)| | 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)| ------------------------------------------------------------------------复制
基于当前绑定变量值产生的选择性，适合的执行计划被选择了。而关于此最后一个有意思的事情是，如果我们再次查看执行数据，现在有三个游标了。
SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select /*ACS_1%'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_B_SENS IS_B_AWAR IS_SHAR ------------ ---------- ----------- --------- --------- ---------- 0 2 957 Y N N 1 1 765 Y Y Y 2 2 6 Y Y Y复制
当游标被切换到绑定感知模式时，初始的游标被废弃了。这是个一次性的开销。请注意游标被标记为非共享（is_shareable 为“N”），这意味着，该游标会是游标缓存中第一个被老化的，并且不再被使用。换句话说，它将等待做为垃圾而回收。在11g中，你注意到该查询上的额外的游标的另一个原因是，当一个新的绑定变量值被使用，基于近似的绑定变量值的选择性，优化器尝试寻找一个它认为适合的游标。如果它不能找到这样的一个游标，就会创建一个新的游标（就类似于上面为无选择性的10创建的1号游标，和为高选择性的9创建的2号游标）。如果新游标的执行计划与其中的一个已经存在的游标是相同的，则这两个游标会合并，以节省游标缓存中的空间。这会导致有一个被丢弃，即处于非共享状态。如果游标缓存存在拥挤时，这个游标会首先被排出，并且不会在将来的执行中被使用。
为了不在评论中逐一回答问题，我计划将问题汇总并提供我的解答：
问：是否该行为由11g优化器自动管理，而我们不再需要设置 cursor_sharing了？
答：出于向后兼容的目的，我们并没有改变cursor_sharing参数的行为。所以，如果你设置它为similar,自适应游标共享将仅用于常量被替换为绑定变量的那些查询。我们希望在未来，这一功能将说服人们将cursor_sharing设置为force。
问：它有何负面影响，比如持有library cache latch较长的时间，来扫描适合的子游标？
答：任何匹配游标的额外开销总是令人担忧的。但我们努力最小化该影响。为了匹配一个绑定感知的游标，在代码上肯定有所增加，因为这需要更智能的检测。但是，这个特性不应该影响那些还没有被标记为绑定感知的游标。
问：是什么会触发游标被标记为“绑定敏感”？
答：我们的目标是那些当绑定变量的值改变，会改变谓词选择性的各种谓词。
问：听起来，似乎优化器是使用返回的行数来决定是否需要一个新的执行计划？
答：我们并不想详细讨论我们是如何决定标记一个游标为绑定感知的内幕。处理的行数是其中的一个条件。
Why are there many cursors for queries containing bind variables?
December 3, 2020 | 8 minute read
Maria Colgan
Distinguished Product Manager
Oracle introduced a new feature called adaptive cursor sharing (ACS) in Oracle Database 11g, to improve the plans that are selected for queries containing bind variables. This feature can result in more cursors for the same query containing bind variables. We’ll explain why in this article. Before we get into the details, let’s review a little history.
Oracle introduced the bind peeking feature in Oracle 9i. With bind peeking, the Optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This allows the optimizer to determine the selectivity of any WHERE clause condition as if literals have been used instead of bind variables, thus improving the quality of the execution plan generated for statements using bind variables.
However, there was a problem with this approach, when the column used in the WHERE clause with the bind contained a data skew. If there is data skew in the column, it is likely that a histogram has been created on this column during statistics gathering. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, it is not guaranteed that this plan will be good for all possible values for the bind variable. In other words, the plan is optimized for the peeked value of the bind variable, but not for all possible values.
From Oracle Database 11g the optimizer allows multiple execution plans to be used for a single statement that uses bind variables. This ensures that the best execution plan will be used depending on the bind value. Let’s look at an example to see exactly how this works.
Assume I have simple table EMP, which has 100,000 rows and has one index called EMP_I1 on the DEPTNO column.
SQL> desc emp Name Null? Type ---------------------- -------- ---------------------------------- ENAME VARCHAR2(20) EMPNO NUMBER PHONE VARCHAR2(20) DEPTNO NUMBER复制
There is a data skew in the DEPTNO column, so when I gathered statistics on the EMP table, Oracle automatically created a histogram on the DEPTNO column.
SQL> select table_name, column_name, histogram from user_tab_cols; TABLE_NAME COLUMN_NAME HISTOGRAM ------------------ ------------------ --------------- EMP DEPTNO HEIGHT BALANCED EMP EMPNO NONE EMP ENAME NONE EMP PHONE NONE复制
Now I will execute a simple select on my EMP table, which has a single WHERE clause predicate on the DEPTNO column. The predicate contains a bind variable. We will begin by using the value 9 for this bind variable. The value 9 occurs 10 times in the table, i.e. in 0.0001% of the rows.
SQL> exec :deptno := 9 SQL> select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno; COUNT(*) MAX(EMPNO) ---------- ---------- 10 99复制
Given how selective the value 9 is, we should expect to get an index range scan for this query. Lets check the execution plan.
SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------- SQL_ID 272gr4hapc9w1, child number 0 ------------------------------------------------------------------------ select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno Plan hash value: 3184478295 ------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| ------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | 1 | SORT AGGREGATE | | 1| 16 | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)| | 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)| ------------------------------------------------------------------------复制
So we got the index range scan that we expected. Now let’s look at the execution statistics for this statement
SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select /*ACS_1%'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE ------------ ---------- ----------- ----------------- ------------- 0 1 53 Y N复制
You can see we have one child cursor that has been executed once and has a small number of buffer gets. We also see that the cursor has been marked bind sensitive. A cursor is marked bind sensitive if the optimizer believes the optimal plan may depend on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to determine if a different plan for different bind values is called for. This cursor was marked bind sensitive because the histogram on the DEPTNO column was used to compute the selectivity of the predicate “where deptno = :deptno”. Since the presence of the histogram indicates that the column is skewed, different values of the bind variable may call for different plans. Now let’s change the value of the bind variable to 10, which is the most popular value for the DEPTNO column. It occurs 99900 times in the table, i.e in 99.9% of the rows.
SQL> exec :deptno := 10 SQL> select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno; COUNT(*) MAX(EMPNO) ---------- ---------- 99900 100000复制
We expect to get the same plan as before for this execution because Oracle initially assumes it can be shared. Let’s check:
SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- SQL_ID 272gr4hapc9w1, child number 0 --------------------------------------------------------------------- select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno Plan hash value: 3184478295 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | 1 | SORT AGGREGATE | | 1| 16 | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)| | 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)| ---------------------------------------------------------------------复制
The plan is still an index range scan as before, but if we look at the execution statistics, we should see two executions and a big jump in the number of buffer gets from what we saw before.
SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select /*ACS_1%'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE ------------ ---------- ----------- ----------------- ------------- 0 2 1007 Y N复制
You should also note that the cursor is still only marked bind sensitive and not bind aware at this point. So let’s re-execute the statement using the same popular value, 10.
SQL> exec :deptno := 10 SQL> select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno; COUNT(*) MAX(EMPNO) ---------- ----------- 99900 100000复制
Behind the scenes during the first two executions, Oracle was monitoring the behavior of the queries, and determined that the different bind values caused the data volumes manipulated by the query to be significantly different. Based on this difference, Oracle “adapts” its behavior so that the same plan is not always shared for this query. Hence a new plan is generated based on the current bind value, 10. Let’s check what the new plan is.
SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT -------------------------------------------------------------------- SQL_ID 272gr4hapc9w1, child number 1 -------------------------------------------------------------------- select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno Plan hash value: 2083865914 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 240 (100)| | 1 | SORT AGGREGATE | | 1 | 16 | | |* 2 | TABLE ACCESS FULL | EMP | 95000 | 1484K | 240 (1)| --------------------------------------------------------------------复制
Given how unselective the value 10 is in the table, it’s not surprising that the new plan is a full table scan. Now if we display the execution statistics we should see an additional child cursor (#1) has been created. Cursor #1 should show a number of buffers gets lower than cursor #0 and it is marked both bind sensitive and bind aware. A bind aware cursor may use different plans for different bind values, depending on how selective the predicates containing the bind variable are. Looking at the execution statistics:
SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select /*ACS_1%'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE ------------ ---------- ----------- ----------------- ------------- 0 2 1007 Y N 1 1 821 Y Y复制
We see that there is a new cursor, which represents the plan which uses a table scan. But if we execute the query again with a more selective bind value, we should use the index plan:
SQL> exec :deptno := 9 SQL> select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno; COUNT(*) MAX(EMPNO) ---------- ---------- 10 99 SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------ SQL_ID 272gr4hapc9w1, child number 2 ------------------------------------------------------------------------ select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno Plan hash value: 3184478295 ------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| ------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 2 (100)| | 1 | SORT AGGREGATE | | 1| 16 | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)| | 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)| ------------------------------------------------------------------------复制
The proper plan was chosen, based on the selectivity produced by the current bind value. There is one last interesting thing to note about this. If we look at the execution statistics again, there are three cursors now:
SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select /*ACS_1%'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_B_SENS IS_B_AWAR IS_SHAR ------------ ---------- ----------- --------- --------- ---------- 0 2 957 Y N N 1 1 765 Y Y Y 2 2 6 Y Y Y复制
The original cursor was discarded when the cursor switched to bind aware mode. This is a one-time overhead. Note that the cursor is marked as not shareable (is_shareable is “N”), which means that this cursor will be among the first to be aged out of the cursor cache, and that it will no longer be used. In other words, it is just waiting to be garbage collected. There is one other reason that you may notice additional cursors for such a query in 11g. When a new bind value is used, the optimizer tries to find a cursor that it thinks will be a good fit, based on similarity in the bind value’s selectivity. If it cannot find such a cursor, it will create a new one (like above, when one (#1) was created for unselective “10” and one (#2) was created for highly-selective “9”). If the plan for the new cursor is the same as one of the existing cursors, the two cursors will be merged, to save space in the cursor cache. This will result in one being left behind that is in a not shareable state. This cursor will be aged out first if there is crowding in the cursor cache, and will not be used for future executions.
Instead of answering the questions in your comments one by one, I am going to summarize the questions and provide my answers here.
Q: Is this behavior managed by 11g optimizer automatically and we don’t need cursor_sharing anymore?
A: We have not changed the behavior of the cursor_sharing parameter yet, for backwards compatibility purposes. So if you set it to similar, adaptive cursor sharing will only kick in for queries where the literals are replace with binds. We hope that in the future, this feature will persuade people to set cursor_sharing to force.
Q: Would it have any impact like holding library cache latches for longer time to search for appropriate child cursor.
A: Any additional overhead in matching a cursor is always a concern, and we strive to minimize the impact. There is of course some increase in the code path to match a bind-aware cursor, since it requires more intelligent checks. This feature should not, however, impact cursors which are not yet marked bind-aware.
Q: What triggers a cursor to be marked “bind sensitive”?
A: Our goal is to consider many types of predicates where the selectivity can change when the bind value changes.
Q: Also it sounds like the optimizer is using the number of rows returned to decided that it’s time for a new plan…
A: I am not going to go into the details of the “special sauce” for how we decide to mark a cursor bind-aware. The number of rows processed is one input.
