下面我们来看看bind_aware的语句是如何工作的，为了简化操作，我们直接使用BIND_AWARE hint。关于该hint的使用，有如下解释：

From 11.1.0.7 onward it is possible to skip the monitoring that is required to detect bind-sensitive queries by using the BIND_AWARE hint. In the following example, the presence of the hint tells the optimizer that we believe the query is bind-sensitive, so it should use bind-aware cursor sharing from the first execution.

SELECT /*+ BIND_AWARE */ MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;

The hint will only work if the query uses bind variables in WHERE clause predicates referencing columns with histograms.

There is also a NO_BIND_AWARE hint that tells the optimizer to ignore bind-sensitive queries, effectively hiding the query from the adaptive cursor sharing functionality.

Bind-aware cursor sharing has a small overhead associated with it, which is why Oracle use the "adaptive" approach to identifying queries that would benefit from bind-aware cursor sharing. Adding the hint to queries that will not benefit from it is a waste.

在进一步实验之前，创建表t1

SQL> create table t1 as select * from t2 where 1 =2;

表已创建。

SQL> alter table t1 modify rtype number;

表已更改。

SQL> insert into t1 select * from t2;

已创建 131071 行。

SQL> commit;

提交完成。

SQL> create index i1 on t1(rtype);

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,estimate_percent=>null,method_opt=>'for all columns size auto,for columns rtype size 40');

PL/SQL 过程已成功完成。

SQL> select table_name,column_name,endpoint_number,to_char(endpoint_value),endpoint_actual_value from user_histograms where table_name='T1';

TABLE_NAME COLUMN_NAME		ENDPOINT_NUMBER TO_CHAR(ENDPOINT_VALUE) 		 ENDPOINT_ACTUAL_VALUE
---------- -------------------- --------------- ---------------------------------------- ------------------------------
T1	   RTYPE			      1 1
T1	   RTYPE			      3 2
T1	   RTYPE			      7 3
T1	   RTYPE			     15 4
T1	   RTYPE			     31 5
T1	   RTYPE			     63 6
T1	   RTYPE			    127 7
T1	   RTYPE			    255 8
T1	   RTYPE			    511 9
T1	   RTYPE			   1023 10
T1	   RTYPE			   2047 11

TABLE_NAME COLUMN_NAME		ENDPOINT_NUMBER TO_CHAR(ENDPOINT_VALUE) 		 ENDPOINT_ACTUAL_VALUE
---------- -------------------- --------------- ---------------------------------------- ------------------------------
T1	   RTYPE			   4095 12
T1	   RTYPE			   8191 13
T1	   RTYPE			  16383 14
T1	   RTYPE			 131071 17
T1	   ID				      0 1
T1	   SEL				      0 .00000762951094834821
T1	   ID				      1 131071
T1	   SEL				      1 .87501335164416

已选择19行。

SQL> select rtype,count(1),min(sel),max(sel) from t1 group by rtype order by 3;

     RTYPE   COUNT(1)	MIN(SEL)   MAX(SEL)
---------- ---------- ---------- ----------
	 1	    1 7.6295E-06 7.6295E-06
	 2	    2 .000015259 .000015259
	 3	    4 .000030518 .000030518
	 4	    8 .000061036 .000061036
	 5	   16 .000122072 .000122072
	 6	   32 .000244144 .000244144
	 7	   64 .000488289 .000488289
	 8	  128 .000976577 .000976577
	 9	  256 .001953155 .001953155
	10	  512  .00390631  .00390631
	11	 1024 .007812619 .007812619

     RTYPE   COUNT(1)	MIN(SEL)   MAX(SEL)
---------- ---------- ---------- ----------
	12	 2048 .015625238 .015625238
	13	 4096 .031250477 .031250477
	14	 8192 .062500954 .062500954
	17     114688 .875013352 .875013352