11G以前ORACLE處理帶有繫結變數的SQL存在一些問題，產生出的執行計劃不是對所有值都好，取決於第一次硬解析時窺探的值，11GR1版本推出了adaptive cursor sharing，它主要的目的是提升對於帶有繫結變數的SQL的執行計劃效能。最佳化器在使用adaptive cursor sharing後，已經可以為一個帶有繫結變數的SQL產生出多個執行計劃。這個特性非常有用對於列上資料有傾斜，在列上收集了直方圖的SQL。
並不需要特別的方式來配置adaptive cursor sharing，預設它就是啟用的，當然你可以透過一些隱含引數來開啟或關閉adaptive cursor sharing,後面簡稱ACS。ACS的出現也引入了一些新檢視和新列。
兩個新列在V$SQL檢視裡，IS_BIND_SENSITIVE 和 IS_BIND_AWARE，IS_BIND_SENSITIVE為Y的CURSOR，代表這個CURSOR被監控了，而IS_BIND_AWARE為Y的，代表這個CURSOR每次被執行都要計算它謂詞的選擇率然後根據選擇率檢視是否當前共享池中是否有滿足要求的執行計劃，如果有重用，如果沒有，重新生成一個。
V$SQL_CS_HISTOGRAM檢視，主要記錄SQL處理的行數的直方圖，處理的行數驅動著ACS發揮作用。直方圖bucket_id 0-2在每次遊標執行的時候欄位count發生變化，每一個bucket代表著操作的資料量，在11.2.0.3版本，當返回的行數在0-1000時候，將會在bucket_id 為0的桶，1000-1000000，將會在bucket_id為1的桶，多餘1000000，將會在bucket_id為2的桶，請讀者不要死記數字，各個版本可能不同。
V$SQL_CS_SELECTIVITY，記錄遊標謂詞的選擇率範圍，最大值，最小值。
V$SQL_CS_STATISTICS 記錄遊標處理的行數、buffer gets等資訊
SQL命令的CURSOR首先會被標註為IS_BIND_SENSITIVE='Y'，在後面的執行過程中會決定是否這個CURSOR是bind aware的。
我們先來看一個測試示例。
CREATE TABLE test
AS
SELECT ROWNUM id,
DBMS_RANDOM.STRING('A', 12) name,
DECODE(MOD(ROWNUM, 500), 0, 'Inactive', 'Active') status
FROM all_objects
WHERE ROWNUM <= 50000;

CREATE INDEX test_id_ind ON test(status);
EXEC dbms_stats.gather_table_stats(user, 'test', method_opt => 'for columns status size 254', cascade => true);

SELECT COUNT (*) cnt, status
FROM test
GROUP BY status
/

CNT STATUS
---------- ----------------
14820 Active
29 Inactive

test@DLSP>var a varchar2(100)
test@DLSP>exec :a :='Inactive';

PL/SQL procedure successfully completed.

test@DLSP>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)
-----------
29

1 row selected.
test@DLSP>@findsql
Enter value for sql_text: %find_me%
Enter value for sql_id:

SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
-------------------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
a9cf9a1ky3bda 0 1950795681 1 .03 2,014 select /*+ find_me */ count(name) from test where status=:a

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 29 | 609 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TEST_ID_IND | 29 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

test@DLSP> -- Check ACS status
test@DLSP> SELECT child_number, executions, buffer_gets, is_bind_sensitive,
2 is_bind_aware
3 FROM v$sql
4 WHERE sql_id='a9cf9a1ky3bda';

CHILD EXECUTIONS BUFFER_GETS IS IS
------ ---------- ----------- -- --
0 1 32 Y N

1 row selected.

test@DLSP>-- Histogram
test@DLSP> SELECT hash_value, sql_id, child_number, bucket_id, COUNT
2 FROM v$sql_cs_histogram
3 WHERE sql_id='a9cf9a1ky3bda'
4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD BUCKET_ID COUNT
---------- -------------------------- ------ ---------- ----------
1709288874 a9cf9a1ky3bda 0 0 1
1709288874 a9cf9a1ky3bda 0 2 0
1709288874 a9cf9a1ky3bda 0 1 0

3 rows selected.

test@DLSP>-- Statistics
test@DLSP> SELECT hash_value, sql_id, child_number, bind_set_hash_value, executions,
2 rows_processed, buffer_gets, cpu_time
3 FROM v$sql_cs_statistics
4 WHERE sql_id='a9cf9a1ky3bda'
5 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD BIND_SET_HASH_VALUE EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- -------------------------- ------ ------------------- ---------- -------------- ----------- ----------
1709288874 a9cf9a1ky3bda 0 2588756875 1 59 32 0

1 row selected.

test@DLSP>-- Selectivity
test@DLSP> SELECT hash_value, sql_id, child_number, predicate, range_id, low, high
2 FROM v$sql_cs_selectivity
3 WHERE sql_id='a9cf9a1ky3bda'
4 ORDER BY sql_id, child_number;

no rows selected

最佳化器參考直方圖資訊後，使用了索引掃描INDEX RANGE SCAN，這是一個正確的決定，注意v$sql的輸出和其他V$檢視的輸出，ORACLE已經標註此SQL為“IS_BIND_SENSITIVE = Y”。

test@DLSP>exec :a :='Active'

PL/SQL procedure successfully completed.

test@DLSP>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)
-----------
475456

1 row selected.

test@DLSP>select * from table(dbms_xplan.display_cursor('a9cf9a1ky3bda',null));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a9cf9a1ky3bda, child number 0
-------------------------------------
select /*+ find_me */ count(name) from test where status=:a

Plan hash value: 2948918962

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 29 | 609 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TEST_ID_IND | 29 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("STATUS"=:A)

test@DLSP> -- Check ACS status
test@DLSP> SELECT child_number, executions, buffer_gets, is_bind_sensitive,
2 is_bind_aware
3 FROM v$sql
4 WHERE sql_id='a9cf9a1ky3bda';

CHILD EXECUTIONS BUFFER_GETS IS IS
------ ---------- ----------- -- --
0 2 3889 Y N

1 row selected.

test@DLSP>
test@DLSP>
test@DLSP>-- Histogram
test@DLSP> SELECT hash_value, sql_id, child_number, bucket_id, COUNT
2 FROM v$sql_cs_histogram
3 WHERE sql_id='a9cf9a1ky3bda'
4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD BUCKET_ID COUNT
---------- -------------------------- ------ ---------- ----------
1709288874 a9cf9a1ky3bda 0 0 1
1709288874 a9cf9a1ky3bda 0 2 0
1709288874 a9cf9a1ky3bda 0 1 1

3 rows selected.

test@DLSP>
test@DLSP>
test@DLSP>-- Statistics
test@DLSP> SELECT hash_value, sql_id, child_number, bind_set_hash_value, executions,
2 rows_processed, buffer_gets, cpu_time
3 FROM v$sql_cs_statistics
4 WHERE sql_id='a9cf9a1ky3bda'
5 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD BIND_SET_HASH_VALUE EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- -------------------------- ------ ------------------- ---------- -------------- ----------- ----------
1709288874 a9cf9a1ky3bda 0 2588756875 1 59 32 0

1 row selected.

test@DLSP>
test@DLSP>
test@DLSP>
test@DLSP>-- Selectivity
test@DLSP> SELECT hash_value, sql_id, child_number, predicate, range_id, low, high
2 FROM v$sql_cs_selectivity
3 WHERE sql_id='a9cf9a1ky3bda'
4 ORDER BY sql_id, child_number;

no rows selected

我們看到最佳化器採用了跟status='Inactive'一樣的執行計劃，這個執行計劃是很糟糕的，因為status為Inactive的只有29個，而status為Active的有475456個，表裡的大部分資料都是status為Active的。但是你是否注意到兩次v$sql_cs_histogram的輸出差異。第一次輸出的值，bucket_id為0的count為1，第二次輸出的值，bucket_id為0的保持不變，bucket_id為1的的count為1，這代表了最佳化器已經意識到了這個SQL的返回的行數跟第一次已經大大的不同了。
HASH_VALUE SQL_ID CHILD BUCKET_ID COUNT
---------- -------------------------- ------ ---------- ----------
1709288874 a9cf9a1ky3bda 0 0 1
1709288874 a9cf9a1ky3bda 0 2 0
1709288874 a9cf9a1ky3bda 0 1 0

HASH_VALUE SQL_ID CHILD BUCKET_ID COUNT
---------- -------------------------- ------ ---------- ----------
1709288874 a9cf9a1ky3bda 0 0 1
1709288874 a9cf9a1ky3bda 0 2 0
1709288874 a9cf9a1ky3bda 0 1 1

test@DLSP>exec :a :='Active'

PL/SQL procedure successfully completed.

test@DLSP>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)
-----------
475456

select * from table(dbms_xplan.display_cursor('a9cf9a1ky3bda',null));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a9cf9a1ky3bda, child number 0
-------------------------------------
select /*+ find_me */ count(name) from test where status=:a

Plan hash value: 2948918962

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 29 | 609 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TEST_ID_IND | 29 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("STATUS"=:A)

SQL_ID a9cf9a1ky3bda, child number 1
-------------------------------------
select /*+ find_me */ count(name) from test where status=:a

Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 452 (100)| |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
|* 2 | TABLE ACCESS FULL| TEST | 475K| 9750K| 452 (3)| 00:00:06 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("STATUS"=:A)

test@DLSP> -- Check ACS status
test@DLSP> SELECT child_number, executions, buffer_gets, is_bind_sensitive,
2 is_bind_aware
3 FROM v$sql
4 WHERE sql_id='a9cf9a1ky3bda';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS
------------ ---------- ----------- -- --
0 2 3889 Y N
1 1 1969 Y Y

test@DLSP>
test@DLSP>
test@DLSP>-- Histogram
test@DLSP> SELECT hash_value, sql_id, child_number, bucket_id, COUNT
2 FROM v$sql_cs_histogram
3 WHERE sql_id='a9cf9a1ky3bda'
4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- -------------------------- ------------ ---------- ----------
1709288874 a9cf9a1ky3bda 0 1 1
1709288874 a9cf9a1ky3bda 0 0 1
1709288874 a9cf9a1ky3bda 0 2 0
1709288874 a9cf9a1ky3bda 1 1 1
1709288874 a9cf9a1ky3bda 1 0 0
1709288874 a9cf9a1ky3bda 1 2 0

6 rows selected.

test@DLSP>
test@DLSP>
test@DLSP>-- Statistics
test@DLSP> SELECT hash_value, sql_id, child_number, bind_set_hash_value, executions,
2 rows_processed, buffer_gets, cpu_time
3 FROM v$sql_cs_statistics
4 WHERE sql_id='a9cf9a1ky3bda'
5 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- -------------------------- ------------ ------------------- ---------- -------------- ----------- ----------
1709288874 a9cf9a1ky3bda 0 2588756875 1 59 32 0
1709288874 a9cf9a1ky3bda 1 2944692738 1 475457 1969 0

test@DLSP>
test@DLSP>
test@DLSP>
test@DLSP>-- Selectivity
test@DLSP> SELECT hash_value, sql_id, child_number, predicate, range_id, low, high
2 FROM v$sql_cs_selectivity
3 WHERE sql_id='a9cf9a1ky3bda'
4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------- -------------------------- ------------ --------------- ---------- -------------------- --------------------
1709288874 a9cf9a1ky3bda 1 =A 0 0.899945 1.099933

最佳化器終於代替了index range scan的執行計劃，採用了full table scan。 V$SQL已經產生了新遊標，IS_BIND_AWARE的值也已經變成了Y，檢視v$SQL_CS_HISTOGRAM額外的多了3行，為了記錄在status為Active時候的執行計劃資訊。檢視V$SQL_CS_STATISTICS也新增了一行記錄新遊標的行處理資訊和buffer gets資訊。$SQL_CS_SELECTIVITY記錄了新遊標謂詞的選擇率。
截止到目前，這個SQL已經是bind aware的了。
test@DLSP>var a varchar2(100)
test@DLSP>exec :a :='Inactive'

PL/SQL procedure successfully completed.

test@DLSP>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)
-----------
29

test@DLSP>col PREDICATE for a15
test@DLSP> -- Check ACS status
test@DLSP> SELECT child_number, executions, buffer_gets, is_bind_sensitive,
2 is_bind_aware
3 FROM v$sql
4 WHERE sql_id='a9cf9a1ky3bda';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS
------------ ---------- ----------- -- --
0 2 3889 Y N
1 1 1969 Y Y
2 1 32 Y Y

test@DLSP>
test@DLSP>
test@DLSP>-- Histogram
test@DLSP> SELECT hash_value, sql_id, child_number, bucket_id, COUNT
2 FROM v$sql_cs_histogram
3 WHERE sql_id='a9cf9a1ky3bda'
4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- -------------------------- ------------ ---------- ----------
1709288874 a9cf9a1ky3bda 0 1 1
1709288874 a9cf9a1ky3bda 0 0 1
1709288874 a9cf9a1ky3bda 0 2 0
1709288874 a9cf9a1ky3bda 1 0 0
1709288874 a9cf9a1ky3bda 1 1 1
1709288874 a9cf9a1ky3bda 1 2 0
1709288874 a9cf9a1ky3bda 2 1 0
1709288874 a9cf9a1ky3bda 2 0 1
1709288874 a9cf9a1ky3bda 2 2 0

9 rows selected.

test@DLSP>
test@DLSP>
test@DLSP>-- Statistics
test@DLSP> SELECT hash_value, sql_id, child_number, bind_set_hash_value, executions,
2 rows_processed, buffer_gets, cpu_time
3 FROM v$sql_cs_statistics
4 WHERE sql_id='a9cf9a1ky3bda'
5 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- -------------------------- ------------ ------------------- ---------- -------------- ----------- ----------
1709288874 a9cf9a1ky3bda 0 2588756875 1 59 32 0
1709288874 a9cf9a1ky3bda 1 2944692738 1 475457 1969 0
1709288874 a9cf9a1ky3bda 2 2588756875 1 59 32 0

test@DLSP>
test@DLSP>
test@DLSP>
test@DLSP>-- Selectivity
test@DLSP> SELECT hash_value, sql_id, child_number, predicate, range_id, low, high
2 FROM v$sql_cs_selectivity
3 WHERE sql_id='a9cf9a1ky3bda'
4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------- -------------------------- ------------ --------------- ---------- -------------------- --------------------
1709288874 a9cf9a1ky3bda 1 =A 0 0.899945 1.099933
1709288874 a9cf9a1ky3bda 2 =A 0 0.000054 0.000066

由於這個SQL已經是bind aware的了，當再次執行status='Inactive'時，ORACLE參考直方圖資訊後，重新產生了一個新的遊標，這個遊標的is_bind_aware欄位為'Y'，產生了index range scan的執行計劃，非常棒！檢視v$sql_cs_histogram，v$sql_cs_statistics，v$sql_cs_selectivity也都有相應的變化。

如何關閉ACS的特性：
alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both;
如果你的系統關閉了繫結變數窺探的功能也會自動關閉ACS。
alter system set "_optim_peek_user_binds"=false scope=both;

直方圖與ACS
test@DLSP>begin
2 dbms_stats.delete_column_stats(ownname => 'test',
3 tabname => 'test',
4 colname => 'status',
5 col_stat_type => 'HISTOGRAM');
6 end;
7 /

PL/SQL procedure successfully completed.

test@DLSP>ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

test@DLSP>exec :a :='Inactive'

PL/SQL procedure successfully completed.

test@DLSP>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)
-----------
29

test@DLSP>exec :a:='Active'

PL/SQL procedure successfully completed.

test@DLSP>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)
-----------
475456

test@DLSP>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)
-----------
475456

test@DLSP>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)
-----------
475456

test@DLSP>select /*+ find_me */ count(name) from test where status=:a;

COUNT(NAME)
-----------
475456

test@DLSP>select * from table(dbms_xplan.display_cursor('a9cf9a1ky3bda',null));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a9cf9a1ky3bda, child number 0
-------------------------------------
select /*+ find_me */ count(name) from test where status=:a

Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 452 (100)| |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
|* 2 | TABLE ACCESS FULL| TEST | 237K| 4875K| 452 (3)| 00:00:06 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("STATUS"=:A)

SQL_ID a9cf9a1ky3bda, child number 1
-------------------------------------
select /*+ find_me */ count(name) from test where status=:a

Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 452 (100)| |
| 1 | SORT AGGREGATE | | 1 | 21 | | |
|* 2 | TABLE ACCESS FULL| TEST | 237K| 4875K| 452 (3)| 00:00:06 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("STATUS"=:A)

38 rows selected.

test@DLSP>col PREDICATE for a15
test@DLSP> -- Check ACS status
test@DLSP> SELECT child_number, executions, buffer_gets, is_bind_sensitive,
2 is_bind_aware
3 FROM v$sql
4 WHERE sql_id='a9cf9a1ky3bda';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS
------------ ---------- ----------- -- --
0 2 3983 Y N
1 3 5907 Y Y

test@DLSP>
test@DLSP>
test@DLSP>-- Histogram
test@DLSP> SELECT hash_value, sql_id, child_number, bucket_id, COUNT
2 FROM v$sql_cs_histogram
3 WHERE sql_id='a9cf9a1ky3bda'
4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BUCKET_ID COUNT
---------- -------------------------- ------------ ---------- ----------
1709288874 a9cf9a1ky3bda 0 1 1
1709288874 a9cf9a1ky3bda 0 0 1
1709288874 a9cf9a1ky3bda 0 2 0
1709288874 a9cf9a1ky3bda 1 1 3
1709288874 a9cf9a1ky3bda 1 0 0
1709288874 a9cf9a1ky3bda 1 2 0

6 rows selected.

test@DLSP>
test@DLSP>
test@DLSP>-- Statistics
test@DLSP> SELECT hash_value, sql_id, child_number, bind_set_hash_value, executions,
2 rows_processed, buffer_gets, cpu_time
3 FROM v$sql_cs_statistics
4 WHERE sql_id='a9cf9a1ky3bda'
5 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME
---------- -------------------------- ------------ ------------------- ---------- -------------- ----------- ----------
1709288874 a9cf9a1ky3bda 0 2588756875 1 30 2014 0
1709288874 a9cf9a1ky3bda 1 2944692738 1 475457 1969 0

test@DLSP>
test@DLSP>
test@DLSP>
test@DLSP>-- Selectivity
test@DLSP> SELECT hash_value, sql_id, child_number, predicate, range_id, low, high
2 FROM v$sql_cs_selectivity
3 WHERE sql_id='a9cf9a1ky3bda'
4 ORDER BY sql_id, child_number;

HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH
---------- -------------------------- ------------ --------------- ---------- -------------------- --------------------
1709288874 a9cf9a1ky3bda 1 =A 0 0.450000 0.550000

我們可以看到雖然最佳化器識別到了這個SQL是bind aware的，但是由於缺少直方圖資訊，不能產生執行計劃為index range scan的執行計劃了。因此使用ACS的功能一定要結合直方圖一起使用。

bind_aware的用法和作用
使用了ACS的功能後，一個遊標從bind sensitive 大 bind aware，中間有著不穩定器，如果在遊標中使用bind_aware hint後，將會使遊標的狀態直接進入bind aware，而不會經歷bind sensitive狀態。我們來透過一個例子看一看：

11GR1 新特性 adaptive cursor sharing

相關文章