DBAs are always encouraging developers to use bind variables, but when bind variables are used against columns containing skewed data they sometimes lead to less than optimum execution plans. This is because the optimizer peeks at the bind variable value during the hard parse of the statement, so the value of a bind variable when the statement is first presented to the server can affect every execution of the statement, regardless of the bind variable values.
Oracle 11g uses Adaptive Cursor Sharing to solve this problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement. This functionality requires no additional configuration. The following code provides an example of adaptive cursor sharing.
First we create and populate a test table.
DROP TABLE acs_test_tab;
CREATE TABLE acs_test_tab (
id NUMBER,
record_type NUMBER,
description VARCHAR2(50),
CONSTRAINT acs_test_tab_pk PRIMARY KEY (id)
);
CREATE INDEX acs_test_tab_record_type_i ON acs_test_tab(record_type);
DECLARE
TYPE t_acs_test_tab IS TABLE OF acs_test_tab%ROWTYPE;
l_tab t_acs_test_tab := t_acs_test_tab();
BEGIN
FOR i IN 1 .. 100000 LOOP
l_tab.extend;
IF MOD(i,2)=0 THEN
l_tab(l_tab.last).record_type := 2;
ELSE
l_tab(l_tab.last).record_type := i;
END IF;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).description := 'Description for ' || i;
END LOOP;
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO acs_test_tab VALUES l_tab(i);
COMMIT;
END;
/
EXEC DBMS_STATS.gather_table_stats(USER, 'acs_test_tab', method_opt=>'for
all indexed columns size skewonly', cascade=>TRUE);
The data in the RECORD_TYPE column is skewed, as shown by the presence of a
histogram against the column.
SQL>SELECT column_name, histogram FROM user_tab_cols WHERE table_name =
'ACS_TEST_TAB';
COLUMN_NAME HISTOGRAM
------------------------------ ---------------
ID NONE
RECORD_TYPE HEIGHT BALANCED
DESCRIPTION NONE
3 rows selected.
Next, we query the table and limit the rows returned based on the RECORD_TYPE
column with a literal value of "1".
SQL> SET LINESIZE 200
SQL> SELECT MAX(id) FROM acs_test_tab WHERE record_type = 1;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
MAX(ID)
----------
1
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID cgt92vnmcytb0, child number 0
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = 1
Plan hash value: 3987223107
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | 2 (100)| |
| 1 | SORT AGGREGATE | | 1
| 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB | 1
| 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 1
| | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
This query has used the index as we would expect. Now we repeat the query, but
this time use a bind variable.
SQL> VARIABLE l_record_type NUMBER;
SQL> EXEC :l_record_type := 1;
SQL> SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
MAX(ID)
----------
1
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 0
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 3987223107
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
So we ran what amounted to the same query, and got the same result and
execution plan. The optimizer picked an execution plan that it thinks is
optimium for query by peeking at the value of the bind variable. The only
problem is, this would be totally the wrong thing to do for other bind values.
SQL> VARIABLE l_record_type NUMBER;
SQL> EXEC :l_record_type := 2;
SQL> SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
MAX(ID)
----------
100000
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 0
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 3987223107
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB | 1 | 9 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
If we look at the V$SQL view entry for this query, we can see the IS_BIND_SENSITIVE column is marked as 'Y', so Oracle is aware this query may require differing execution plans depending on the bind variable values, but currently the IS_BIND_AWARE column is marked as 'N', so Oracle has not acted on this yet.
SQL>SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM v$sql
WHERE sql_text = 'SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type';
SQL_ID CHILD_NUMBER I I
------------- ------------ - -
9bmm6cmwa8saf 0 Y N
1 row selected.
If we run the statement using the second bind variable again, we can see that Oracle has decided to use an alternate, more efficient plan for this statement.
SQL> VARIABLE l_record_type NUMBER;
SQL> EXEC :l_record_type := 2;
SQL> SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
MAX(ID)
----------
100000
1 row selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 9bmm6cmwa8saf, child number 1
-------------------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 509473618
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 138 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| ACS_TEST_TAB | 48031 | 422K| 138 (2)| 00:00:02 |
--------------------------------------------------------------------------------------
This change in behavior is also reflected in the V$SQL view, which now has the IS_BIND_AWARE column maked as "Y".
SQL>SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM v$sql
WHERE sql_text = 'SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type';
SQL_ID CHILD_NUMBER I I
------------- ------------ - -
9bmm6cmwa8saf 0 Y N
9bmm6cmwa8saf 1 Y Y
2 rows selected.
Information about the cursor sharing histograms, statistics and selectivity is displayed using the V$SQL_CS_HISTOGRAM, V$SQL_CS_STATISTICS and V$SQL_CS_SELECTIVITY views respectively.
SQL> SELECT * FROM v$sql_cs_histogram WHERE sql_id = '9bmm6cmwa8saf';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
-------- ---------- ------------- ------------ ---------- ----------
319A4A1C 4171522382 9bmm6cmwa8saf 1 0 0
319A4A1C 4171522382 9bmm6cmwa8saf 1 1 1
319A4A1C 4171522382 9bmm6cmwa8saf 1 2 0
319A4A1C 4171522382 9bmm6cmwa8saf 0 0 1
319A4A1C 4171522382 9bmm6cmwa8saf 0 1 1
319A4A1C 4171522382 9bmm6cmwa8saf 0 2 0
6 rows selected.
SQL> SELECT * FROM v$sql_cs_statistics WHERE sql_id = '9bmm6cmwa8saf';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P
EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
-------- ---------- ------------- ------------ ------------------- -
---------- -------------- ----------- ----------
319A4A1C 4171522382 9bmm6cmwa8saf 1 2064090006 Y
1 50001 499 0
319A4A1C 4171522382 9bmm6cmwa8saf 0 2342552567 Y
1 3 3 0
2 rows selected.
SQL> SELECT * FROM v$sql_cs_selectivity WHERE sql_id = '9bmm6cmwa8saf';
ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE
RANGE_ID LOW HIGH
-------- ---------- ------------- ------------
---------------------------------------- ---------- ---------- ----------
319A4A1C 4171522382 9bmm6cmwa8saf 1 =L_RECORD_T
0 0.432283 0.528346
1 row selected.10g和11g绑定变量的区别
Oracle11g中引入了adaptive cursor sharing( 适应性游标共享) 、Bind-Sensitive Cursors、 Bind-Aware Cursors、和Cursor Merging 的新功能。
adaptive cursor sharing
这个特性允许一个使用绑定变量的SQL语句使用多个执行计划。 这个特性是默认启动的，并且不能disable。不过要注意的是，该特性只有对绑定变量的参数个数不超过14个的sql语句才有效。
Bind-Sensitive Cursor
根据绑定变量值得到的最优执行计划的一个cursor。Oracle会根据不同绑定变量值来进行判断，如果之前的cursor 已经不是最优的了，那么Oracle 会重新创建一个child cursor，并将bind-sensitive标价为Y.
当满足以下两个条件时，优化器会将对应的cursor判定为符合Bind-Senstive Cursor：
1、使用某个具体的变量值进行peek得到的估算选择性。
2、对应绑定变量列上存在直方图
Bind-Aware Cursors
如果一个 Bind-Senstive Cursor由于不同的绑定变量值需要使用不同的执行计划，那么他就会被标记为Bind-Aware Cursors。当一个cursor被标记为bind-aware， optimizer会根据Bind value 和selectivity estimate来选择新的执行计划。如果cursor的bind-aware标记为Y，那么在下次执行时，Oracle 做如下操作：
1、根据新的bind value 来生成新的执行计划
2、标记原来的cursor 为非共享，即V$SQL.IS_SHAREABLE 设置为 N，当这种cursor 长期不被使用时， 就会被移除shareed SQL area.
Cursor Merging
当optimizer为bind-aware cursor创建一个新的执行计划（plan）时，如果这个cursor和之前存在的cursor 一样，那么optimizer会进行cursor merging，以节省共享池空间，并扩大原有执行计划的选择性范围来保护新绑定变量的选择性范围。
如果新的绑定变量值产生的执行计划选择性不能和现有cursor的选择性匹配，那么Oracle 会进行一次硬解析来生成新的执行计划和cursor。如果这个新的执行计划被已经存在的cursor 使用，那么数据会将这2个cursor 进行合并并删除老的cursor。
总结
当我们第一去执行一个带有绑定变量的SQL时，Oracle 会进行硬解析，并对该变量进行Peek，即Peek执行计划的selectivity estimate值和直方图Oracle通过Adaptive Cursor Sharing特性允许同一个SQL可以使用多个执行计划。
在每次执行时，Oracle会根据Peek的selectivity estimate 值和直方图（如果存在）来判断已经存在的cursor是否是最优的，并将Bind-Sensitive 标记为Y。
如果Oracle在SQL每次执行时，根据之前peek得到的信息，需要产生更 优的执行计划，则就在创建一个child cursor，并将Bind-Aware 标记为Y。并根据新的bind value来生成新的plan和cursor，并将原来的cursor标记为非共享，即V$SQL.IS_SHAREABLE 设置为N，当这种cursor 长期不被使用时， 就会被移出shared SQL area.
当optimizer为bind-aware cursor创建一个新的执行计划（plan）时，如果这个cursor和之前存在的cursor 一样，那么optimizer会进行cursor merging，以节省共享池空间，并扩大原有执行计划的选择性范围来保护新绑定变量的选择性范围。
如果新的绑定变量值产生的执行计划选择性不能和现有cursor的选择性匹配，那么Oracle 会进行一次硬解析来生成新的执行计划和cursor。如果这个新的执行计划被已经存在的cursor使用，那么数据会将这2个cursor 进行合并并删除老的cursor。
Oracle11g中v$sql视图中新增下面这些字段：
is_bind_sensitive：不仅指出是否使用绑定变量窥测来生成执行计划，而且指出这个执行计划是否依赖于窥测到的值。如果是，这个字段会被设置为Y，否则会被设置为N。
is_bind_aware：表明游标是否使用了扩展的游标共享。如果是，这个字段会被设置为Y，如果不是，这个字段会被设置为N。如果是设置为N，这个游标将被废弃，不再可用。
is_shareable：表明游标能否被共享。如果可以，这个字段会被设置为Y，否则，会被设置为N。如果被设置为N，这个游标将被废弃，不再可用。
参考至：http://www.oracle-base.com/articles/11g/adaptive-cursor-sharing-11gr1.php
http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3047.htm
http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3048.htm
http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3049.htm
