Oracle 10g 与 11g 绑定变量（Bind Variable） 区别 说明

一. Oracle 11gR2官网的说明

http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/optimops.htm

1.1 Bind Variable Peeking

Inbindvariable peeking (also known as bind peeking), the optimizer looksat the value in a bind variable when the database performsa hard parse of a statement.

Whena query uses literals, the optimizer can use theliteral values to find the best plan. However, when a query uses bindvariables, the optimizer must select the best plan without the presence ofliterals in the SQL text. This task can be extremely difficult. By peeking at bind values the optimizer can determine theselectivity of a WHERE clause condition as if literals had been used, therebyimproving the plan.

-- bind variable peeking 也称bindPeeking， 指在执行硬解析的时，优化器使用 绑定变量的确定值，来生成最优的执行计划。

Example11-1 Bind Peeking

Assumethat the following 100,000 row emp table exists in the database. The table hasthe following definition:

SQL> DESCRIBE emp

Name Null? Type

---------------------- -------- ----------------------------------

ENAME VARCHAR2(20)

EMPNO NUMBER

PHONE VARCHAR2(20)

DEPTNO NUMBER

Thedata is significantly skewed in the deptno column. The value 10 is found in99.9% of the rows. Each of the other deptno values (0 through 9) is found in 1%of the rows. You have gathered statistics for thetable, resulting in a histogram on the deptno column. You define a bindvariable and query emp using the bind value 9 as follows:

--对于严重倾斜的字段，Oracle 收集的统计信息存放在直方图里

VARIABLE deptno NUMBER

EXEC :deptno := 9

SELECT /*ACS_1*/ count(*), max(empno)

FROM emp

WHERE deptno = :deptno;

The query returns 10rows:

COUNT(*) MAX(EMPNO)

---------- ----------

 10 99

To generate the execution plan for the query, thedatabase peeked at the value 9 during the hard parse.The optimizer generated selectivity estimates as if the user had executed thefollowing query:

select /*ACS_1*/ count(*), max(empno)

from emp

where deptno = 9;

Whenchoosing a plan, the optimizer only peeks at the bind value during the hardparse. This plan may not be optimal for all possible values.

1.2 AdaptiveCursor Sharing

Theadaptive cursor sharing feature enables a singlestatement that contains bind variables to use multiple execution plans.Cursor sharing is "adaptive" because the cursor adapts its behaviorso that the database does not always use the same plan for each execution orbind variable value.

Forappropriate queries, the database monitors data accessed over time fordifferent bind values, ensuring the optimal choice of cursor for a specificbind value. For example, the optimizermight choose one plan for bind value 9 and a different plan for bind value 10.Cursor sharing is "adaptive" because the cursor adapts its behaviorso that the same plan is not always used for each execution or bind variablevalue.

Adaptive cursor sharing is enabled for the database bydefault and cannot be disabled. Note that adaptive cursor sharing does not apply to SQL statements containing more than 14 bindvariables.

Note:

Adaptivecursor sharing is independent of the CURSOR_SHARING initialization parameter(see"SharingCursors for Existing Applications"). Adaptive cursor sharing isequally applicable to statements that contain user-defined and system-generatedbind variables.

1.3 Bind-SensitiveCursors

A bind-sensitive cursor is a cursor whose optimal plan maydepend on the value of a bind variable. The database monitors thebehavior of a bind-sensitive cursor that uses different bind values todetermine whether a different plan is beneficial.

Thecriteria used by the optimizer to decide whether acursor is bind-sensitive include the following:

（1）The optimizer has peeked at the bind values to generate selectivity estimates.

（2）A histogram exists on the columncontaining the bind value.

Example11-2 Bind-Sensitive Cursors

InExample11-1you queried the emp table using the bind value 9 for deptno. Now yourun the DBMS_XPLAN.DISPLAY_CURSOR function to show the query plan:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

The output is as follows:

----------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 16 | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 16 | 2 (0)| 00:00:01|

|* 3 | INDEX RANGE SCAN | EMP_I1 | 1 | | 1 (0)| 00:00:01|

----------------------------------------------------------------------------------

Theplan indicates that the optimizer chose an index range scan, which is expected because of the selectivity (only 1%) of the value 9.You can query V$SQL to view statistics about the cursor:

COL BIND_SENSI FORMAT a10

COL BIND_AWARE FORMAT a10

COL BIND_SHARE FORMAT a10

SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

FROM V$SQL

WHERE SQL_TEXT LIKE 'select /*ACS_1%';

Asshown in the following output, one child cursor existsfor this statement and has been executed once. A small number of buffergets are associated with the child cursor. Because thedeptno data is skewed, the database created a histogram. This histogram led the database to mark the cursor asbind-sensitive (IS_BIND_SENSITIVE is Y).

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- ----------- ---------- ---------- ----------

 0 1 56 Y N Y

Foreach execution of the query with a new bind value, the database records theexecution statistics for the new value and compares them to the executionstatistics for the previous value. If execution statisticsvary greatly, then the database marks the cursor bind-aware.

1.4 Bind-Aware Cursors

A bind-aware cursor is a bind-sensitive cursor eligible touse different plans for different bind values. After a cursor has beenmade bind-aware, the optimizer chooses plans for futureexecutions based on the bind value and its selectivity estimate.

Whena statement with a bind-sensitive cursor executes, the database decides whetherto mark the cursor bind-aware. The decision depends on whether the cursor producessignificantly different data access patterns for different bind values. If the database marks the cursor bind-aware, then the nexttime that the cursor executes the database does the following:

（1）Generates a new plan based on the new bind value.

（2）Marks the original cursor generated for thestatement as not shareable (V$SQL.IS_SHAREABLE is N).This cursor is no longer usable and will be among thefirst to be aged out of the shared SQL area.

Example11-3 Bind-Aware Cursors

InExample11-1you queried emp using the bind value 9. Now you query emp using thebind value 10. The query returns 99,900 rows that contain the value 10:

COUNT(*) MAX(EMPNO)

---------- ----------

99900 100000

Becausethe cursor for this statement is bind-sensitive, the optimizer assumes that thecursor can be shared. Consequently, the optimizer uses the same index rangescan for the value 10 as for the value 9.

TheV$SQL output shows that the same bind-sensitive cursor was executed a secondtime (the query using 10) and required many more buffer gets than the firstexecution:

SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

FROM V$SQL

WHERE SQL_TEXT LIKE 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- ----------- ---------- ---------- ----------

 0 2 1010 Y N Y

Now you execute the query using the value 10 a second time.The database compares statistics for previous executions and marks the cursoras bind-aware. In this case, the optimizer decides that a new plan iswarranted, so it performs a hard parse of the statementand generates a new plan. The new plan uses a full table scan instead of anindex range scan:

---------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 208 (100)| |

| 1 | SORT AGGREGATE | | 1 | 16 | | |

|* 2 | TABLE ACCESS FULL| EMP | 95000 | 1484K| 208 (1)| 00:00:03 |

---------------------------------------------------------------------------

A query of V$SQL shows that the database created anadditional child cursor (child number 1) that represents the plan containingthe full table scan. This new cursor shows a lower number of buffer getsand is marked bind-aware:

SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

FROM V$SQL

WHERE SQL_TEXT LIKE 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- ----------- ---------- ---------- ----------

 0 2 1010 Y N Y

 1 2 1522 Y Y Y

Afteryou execute the query twice with value 10, you executeit again using the more selective value 9. Because of adaptive cursor sharing, theoptimizer "adapts" the cursor and chooses an index range scan rather than a full table scan for this value.

Aquery of V$SQL indicates that the database created a new child cursor (childnumber 2) for the execution of the query:

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- ----------- ---------- ---------- ----------

 0 2 1010 Y N N

 1 1 1522 Y Y Y

 2 1 7 Y Y Y

Becausethe database is now using adaptive cursor sharing, the database no longer usesthe original cursor (child 0), which is not bind-aware. The shared SQL area willage out the defunct cursor.

1.5 CursorMerging

If the optimizer creates a plan for abind-aware cursor, and if this plan is the same as an existing cursor, then theoptimizer can perform cursor merging. In this case, the database mergescursors to save space in the shared SQL area. The database increases theselectivity range for the cursor to include the selectivity of the new bind.

Suppose you execute a query with a bind value that does notfall within the selectivity ranges of the existing cursors. The databaseperforms a hard parse and generates a new plan and new cursor. If this new plan is the same plan used by an existing cursor,then the database merges these two cursors and deletes one of the old cursors.

1.6 ViewingBind-Related Performance Data

Youcan use the V$ views for adaptive cursor sharing to see selectivity ranges,cursor information (such as whether a cursor is bind-aware or bind-sensitive),and execution statistics:

（1）V$SQL shows whether acursor is bind-sensitive or bind-aware

（2）V$SQL_CS_HISTOGRAM showsthe distribution of the execution count across a three-bucket execution historyhistogram

（3）V$SQL_CS_SELECTIVITY showsthe selectivity ranges stored for every predicate containing a bind variable ifthe selectivity was used to check cursor sharing

（4）V$SQL_CS_STATISTICS summarizesthe information that the optimizer uses to determine whether to mark a cursorbind-aware.

二. Bind Peeking 相关说明

2.1 Bind Peek

Oracle 绑定变量 详解

http://blog.csdn.net/xujinyang/article/details/6832627

Oracle 绑定变量 示例

http://blog.csdn.net/xujinyang/article/details/6922381

bind variable peeking 也称bindpeeking，Bind Peeking是Oracle 9i中引入的新特性，当我们使用绑定变量，在进行硬解析时，为了得到最优的执行计划，Oracle 会把变量具体的值带进去，然后根据这个具体的值来查看执行计划。

假设我们的一个谓词字段上存在严重的数据倾斜，这些倾斜数据，通过数据字段上的histogram（直方图），优化器可以计算出相应的cost，如果peek 的具体值是这个倾斜数据中的少部分，那么会选择走索引，如果倾斜中的大部分，这种情况走索引的代价会很大，所以Oracle 会选择走全表扫描。

当执行计划确定以后，以后的查询都会使用这个执行计划，这个也称为软解析。

可以通过隐含的参数来调整数据库默认的bind peeking行为, 注意，这是个隐含参数，Oracle 是不建议修改的，而且Peeking 对执行计划的选择来说也确实很重要。所以，默认就好。这里仅做为知识点，了解一下。

SYS@anqing2(rac2)> SELECT ksppinm,ksppstvl, ksppdesc FROM x$ksppi x, x$ksppcv y WHEREx.indx = y.indx AND TRANSLATE (ksppinm, '_', '#') LIKE'#optim_peek_user_binds';

KSPPINM KSPPSTVL KSPPDESC

---------------------- --------------------------------------------------------

_optim_peek_user_binds TRUE enable peeking of user binds

关于参数的分类和隐含参数的查看，参考我的blog：

Oracle 参数分类 和 参数的查看方法

http://blog.csdn.net/xujinyang/article/details/6829538

修改方法：

SYS@anqing2(rac2)> alter session set "_optim_peek_user_binds"=false;

Session altered.

SYS@anqing2(rac2)> alter session set "_optim_peek_user_binds"=true;

Session altered.

不是所有的硬解析都会需要Peeking。 bind peeking 的全称是bind variable peeking，从这个定义，也可以看到只有在使用绑定变量的情况下，才会使用Peeking。

如果没有使用绑定变量，CBO 可以很快的根据相关的统计信息来确定执行计划，但是如果使用了绑定变量，Oracle就需要使用Peeking 来确定最优的执行计划。

2.2 Oracle 10g 下的绑定变量和Peeking说明

在Oracle 10g下的绑定变量相对比较简单，当使用绑定变量的SQL 第一次执行时，会进行硬解析，生成plan 和cursor。 在这个过程中，Oracle 会使用bind peeking，即将绑定变量的值带入，从而选择最优的一个plan。 以后每次执行都使用这个plan。

在以后的执行时，如果因为其他原因导致cursor 不可重用，那么就会生成一个child_cursor. 这个cursor 不可重用的原因可以查看：v$sql_shared_cursor视图。

那么这就有一个问题。如果列上有列上有严重的数据倾斜，某个字段中99%是值1，1%是值0. 当我们用0 来进行peeking的时候，这时候会走索引，并且以后的所有plan 都是使用这个。 如果我们的绑定值变成了1. 这个时候，明显走全表扫描比索引划算。

但是Oracle 10g 下还是会使用第一次的plan，即使这个plan 不是最优的。所以在Oracle 10g下，如果数据存在数据倾斜，那么最好不要使用绑定变量。

2.3 Oracle 11g下的绑定变量和Peeking说明

在Oracle 11g 以后在绑定变量这块有所以改变，会生成一个范围值的执行计划。 然后每次传变量进去就对比范围，选择最优的执行计划。与这个功能相关的参数保存在v$sql视图中：is_bind_sensitive,is_bind_aware,is_shareable。 这几个字段，在Oracle 10g的v$sql 视图里是没有的。

2.3.1 Adaptive Cursor Sharing

AdaptiveCursor Sharing 特性允许一个使用绑定变量的SQL语句使用多个执行计划。

对于同一个SQL, 为了得到合适的查询，oracle 会监控使用不同绑定变量的情况，已确保对不同绑定变量值的cursor（执行计划）都是最优的。比如因为数据倾斜的原因对绑定变量值A 使用执行计划A，对绑定变量值B 使用执行计划B。 虽然他们的SQL 是相同的，但执行计划不同。

AdaptiveCursor Sharing 默认启动的，并且不能disable。 不过要注意的是，该特性只有在绑定变量的参数个数不超过14个的情况才有效。

2.3.2 Bind-Sensitive Cursors

Bind-SensitiveCursor是根据绑定变量值得到的最优执行计划的一个cursor。Oracle 会根据不同绑定变量值来进行判断，如果之前的cursor 已经不是最优的了，那么Oracle 会重新创建一个child cursor，并将bind-sensitive标价为Y.

优化器会根据以下两个条件来判断对应的cursor 符合Bind-Sentive Cursor：

（1）使用某个具体的变量值进行peek得到的估算选择性。

（2）如果对应绑定列上存在直方图，则使用直方图信息。

当对应字段上的数据有倾斜时，Oracle 会创建对对应的直方图。 并创建新的cursor，并将IS_BIND_SENSITIVE 标记为Y。

当每次查询新的绑定变量值时，数据库会记录每次执行的统计信息，然后与之前的进行比较，如果这次的统计信息比上次好，那么数据库会重新创建一个child cursor并将bind-aware标记为Y。

2.3.3 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.

2.3.4 Cursor Merging

当optimizer 为bind-aware cursor创建一个新的执行计划（plan）时，如果这个cursor 和之前存在的cursor 一样，那么optimizer 会进行cursor merging。

如果绑定变量值对应的plan 不能和已经存在的plan匹配，那么Oracle 会进行一次硬解析来生成新的执行计划和cursor。如果这个新的执行计划被已经存在的cursor 使用，那么数据会将这2个cursor 进行合并并删除老的cursor。

2.2.5 总结

当我们第一去执行一个带有绑定变量的SQL时，Oracle 会进行硬解析，但是硬解析不能确定最优的执行计划，所以这时候有了Peek。 也可以说是偷窥，即把实际值带入，来生成一个selectivity estimate。 然后来选择最优的一个执行计划来执行。

这是第一次执行SQL语句。以后执行时就会使用已经存在的plan和cursor。 Oracle 通过Adaptive Cursor Sharing特性允许同一个SQL 可以使用多个执行计划。

在每次执行时，Oracle会根据Peek 的selectivity estimate 值和直方图（如果存在）来判断已经存在的cursor 是否是最优的，如果不是，就重新创建一个child cursor，并讲Bind-Sensitive 标记为Y。

而且Oracle在SQL 每次执行时，都会收集相关的统计信息，然后根据统计信息进行判断，如果比上次的更好，就在创建一个child cursor，并将Bind-Aware 标记为Y。

当标记为bind-aware cursor 的cursor在下次执行时，Oracle根据新的bind value 来生成新的plan和cursor，并将原来的cursor标记为非共享，即V$SQL.IS_SHAREABLE 设置为 N，当这种cursor 长期不被使用时， 就会被移出shared SQL area.

在bind-aware cursor创建新的cursor 之后，如果这个cursor 和之前某个存在的cursor一样，那么Oracle 会对他们进行合并。

如果在cache里不能找到bind-aware对应的plan，那么就会重新进行一次硬解析，来生成plan 和cursor，如果这个plan 以后被新的cursor 使用，那么Oracle 会将这2个cursor 进行合并。

上面就是sql 解析和执行期间cursor和plan的变化过程。下面通过一些测试来验证上面的理论。

三. 测试

3.1 Oracle 10g 下的绑定变量测试

--查看版本

SYS@anqing2(rac2)> select * fromv$version where rownum=1;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise EditionRelease 10.2.0.4.0 - Prod

--创建表bind_a，字段数据倾斜

SYS@anqing2(rac2)> create tablebind_a(id number,name varchar2(20));

Table created.

SYS@anqing2(rac2)> begin

2 fori in 1..10000 loop

3 ifi<=9900 then

4insert into bind_a values(i,'Dave');

5 else

6insert into bind_a values(i,'DBA');

7 endif;

8commit;

9 endloop;

10 end;

11 /

PL/SQL procedure successfully completed.

在bind_a的Name 字段创建索引

SYS@anqing2(rac2)> create index idx_ba_name on bind_a(name);

Index created.

--查看bind_a的直方图信息，因为现在没有做统计信息，为空

SYS@anqing2(rac2)> SELECT column_name,num_distinct, num_buckets, histogramFROM DBA_TAB_COL_STATISTICS WHEREtable_name = 'BIND_A' AND column_name = 'NAME';

no rows selected

-- 对bind_a 和 bind_b进行统计信息收集

SYS@anqing2(rac2)> exec dbms_stats.gather_table_stats('SYS','BIND_A');

PL/SQL procedure successfully completed.

SYS@anqing2(rac2)> exec dbms_stats.gather_table_stats('SYS','BIND_B');

PL/SQL procedure successfully completed.

--查看直方图信息

SYS@anqing2(rac2)> SELECT column_name,num_distinct, num_buckets, histogramFROM DBA_TAB_COL_STATISTICS WHEREtable_name = 'BIND_A' ;

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM

--------------- ------------ --------------------------

ID 10000 1 NONE

NAME 22 FREQUENCY

--对于Bind_a,存在严重的数据倾斜，在进行统计信息收集之后，Oracle 创建了FrequencyHistograms. 这个条件是就是distinct的值小于等于bucket的数量，因为这个是我们模拟的环境，一般FrequencyHistograms 很少。大部分都是Height-Balanced Histograms。

--查询bind_a，name 字段使用DBA值。

SYS@anqing2(rac2)> var t varchar2(10);

SYS@anqing2(rac2)> exec :t:='DBA';

PL/SQL procedure successfully completed.

SYS@anqing2(rac2)> select count(*) from bind_a where name=:t;

COUNT(*)

----------

100

--在v$sql 是查看对应的cursor

SYS@anqing2(rac2)> selectsql_text,child_number, executions, buffer_gets from gv$sql wheresql_text like 'select count(*)from bind_a where name=:t';

SQL_TEXT CHILD_NUMBER EXECUTIONSBUFFER_GETS

--------------------------------------------------------- ---------- -----------

select count(*) from bind_a wherename=:t 01 2

--child_number 为0. 执行了1次

--在select 一次

SYS@anqing2(rac2)> select count(*) frombind_a where name=:t;

COUNT(*)

----------

100

SYS@anqing2(rac2)> selectsql_text,child_number, executions, buffer_gets from gv$sql wheresql_text like 'select count(*)from bind_a where name=:t';

SQL_TEXT CHILD_NUMBER EXECUTIONS BUFFER_GETS

--------------------------------------------------------- ---------- -----------

select count(*) from bind_a wherename='DBA' 0 2 4

--这里的child_number还是0，执行了2次

--查看一下对应的执行计划

SYS@anqing2(rac2)> selectsql_id,child_number from gv$sqlwhere sql_text like 'select count(*) from bind_a where name=:t';

SQL_ID CHILD_NUMBER

------------- ------------

6qxa1kpbp9bw9 0

--格式

select * fromtable(dbms_xplan.display_cursor('sql_id',sql_child_number));

SYS@anqing2(rac2)> set linesize 80

SYS@anqing2(rac2)> select * fromtable(dbms_xplan.display_cursor('6qxa1kpbp9bw9',0));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID6qxa1kpbp9bw9, child number 0

-------------------------------------

select count(*) from bind_a where name=:t

Plan hash value: 1084430089

--------------------------------------------------------------------------------

| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

| 0| SELECT STATEMENT | | || 1 (100)|

| 1| SORT AGGREGATE || 1 | 5 | |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|* 2 |INDEX RANGE SCAN| IDX_BA_NAME |100 | 500 | 1(0)| 00:00:01

--------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

2- access("NAME"=:T)

19 rows selected.

--在这个执行计划里是采用了index Range scan。因为我们的Name 为dba 的很少。 现在我们把查询值改成DAVE.

SYS@anqing2(rac2)> exec :t:='Dave';

PL/SQL procedure successfully completed.

SYS@anqing2(rac2)> select count(*) from bind_a where name=:t;

COUNT(*)

----------

9900

--查看cursor

SYS@anqing2(rac2)> selectsql_text,child_number, executions, buffer_gets from gv$sql wheresql_text like 'select count(*)from bind_a where name=:t';

SQL_TEXT CHILD_NUMBER EXECUTIONSBUFFER_GETS

--------------------------------------------------------- ---------- -----------

select count(*) from bind_a wherename=:t 03 28

--这个cursor执行3次，说明换了值之后，还是使用的这个cursor

--查看执行计划

SYS@anqing2(rac2)> select * fromtable(dbms_xplan.display_cursor('6qxa1kpbp9bw9',0));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID6qxa1kpbp9bw9, child number 0

-------------------------------------

select count(*) from bind_a where name=:t

Plan hash value: 1084430089

--------------------------------------------------------------------------------

| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

| 0| SELECT STATEMENT | | || 1 (100)|

| 1| SORT AGGREGATE || 1 | 5 | |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|* 2 |INDEX RANGE SCAN| IDX_BA_NAME |100 | 500 | 1(0)| 00:00:01

--------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

2- access("NAME"=:T)

19 rows selected.

在这里，还是采用之前的plan和cursor，在我们用Dave 查询时，明显走全索引扫描的效率要比索引高。 但是在Oracle 10g不能处理这种问题。 在10g的绑定变量会一直采用第一次产生的plan。 即使这个plan不是最优的。

所以在Oracle10g里，对有严重数据倾斜的字段，最好不要采用绑定变量。

3.2 Oracle 11g 下的绑定变量测试

--查看数据库版本

SQL> select * from v$version whererownum=1;

BANNER

-----------------------------------------------------------------------------

Oracle Database 11g Enterprise EditionRelease 11.2.0.1.0 - Production

--创建表

SQL>create table bind_a(id number,name varchar2(20));

Table created.

--插入数据

SQL> begin

2 for i in 1..10000 loop

3 if i<=9900 then

4 insert into bind_avalues(i,'Dave');

5 else

6 insert into bind_avalues(i,'DBA');

7 end if;

8 commit;

9 end loop;

10 end;

11 /

PL/SQL procedure successfully completed.

--创建索引

SQL>create index idx_ba_name on bind_a(name);

Index created.

--收集统计信息

SQL> execdbms_stats.gather_table_stats('SYS','BIND_A');

PL/SQL procedure successfully completed.

--查看直方图信息

SQL> SELECT column_name, num_distinct,num_buckets, histogram FROMDBA_TAB_COL_STATISTICS WHERE table_name= 'BIND_A' ;

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM

------------------------------ ----------------------- ---------------

NAME 2 1NONE

ID 10000 1NONE

--使用默认的参数没有创建直方图，重新收集一下统计信息，并指定参数

SQL> execdbms_stats.gather_table_stats('SYS','BIND_A',METHOD_OPT=> 'FOR COLUMNS SIZE 10 NAME');

PL/SQL procedure successfully completed.

--这里指定收集Name列的信息，bucket为10个。当distinct 值小于bucket时，就会创建FrequentlyHistogram。

SQL> SELECT column_name, num_distinct,num_buckets, histogram FROMDBA_TAB_COL_STATISTICS WHERE table_name= 'BIND_A' ;

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM

------------------------------ ----------------------- ---------------

NAME 2 2 FREQUENCY

ID 10000 1NONE

--实际只使用了2个bucket。

--使用绑定变量查询，这里选择较少的Name=DBA 来查询，这样会走索引。

SQL> var t varchar2(10);

SQL> exec:t:='DBA';

PL/SQL procedure successfully completed.

SQL>select count(*) from bind_a where name=:t;

COUNT(*)

----------

100

--在v$sql 是查看对应的cursor

SQL> COL BIND_SENSI FORMAT a10

SQL> COL BIND_AWARE FORMAT a10

SQL> COL BIND_SHARE FORMAT a10

SQL> SELECT CHILD_NUMBER, EXECUTIONS,BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

2 IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

3 FROM V$SQL

4 WHERE sql_text like'select count(*) from bind_a%';

child_number executions buffer_gets bind_sensi bind_aware bind_share

------------ ---------- --------------------- ---------- ----------

01 23 NN Y

--cursor显示执行一次，bind_share为Y。这个是默认值。

--第二次查询

SQL> select count(*) from bind_a wherename=:t;

COUNT(*)

----------

100

--查看cursor信息

SQL> SELECT CHILD_NUMBER, EXECUTIONS,BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

2 IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

3 FROM V$SQL

4 WHERE sql_text like'select count(*) from bind_a%';

child_number executions buffer_gets bind_sensi bind_aware bind_share

------------ ---------- --------------------- ---------- ----------

0 1 23 NN Y

1 1 2 Y N Y

--在第二次执行时，创建了一个child cursor，并且该游标被标记为了Bind-Sensitive Cursor。

--第三次查询

SQL> select count(*) from bind_a wherename=:t;

COUNT(*)

----------

100

SQL> SELECT CHILD_NUMBER, EXECUTIONS,BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

2 IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

3 FROM V$SQL

4 WHERE sql_text like'select count(*) from bind_a%';

CHILD_NUMBER EXECUTIONS BUFFER_GETSBIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- --------------------- ---------- ----------

0 1 23 N N Y

1 2 4 Y N Y

--child_number 为1的执行了2次

--查看执行计划

SQL> select sql_id,child_numberfrom gv$sql where sql_text like'select count(*) from bind_a%';

SQL_ID CHILD_NUMBER

------------- ------------

6qxa1kpbp9bw9 0

6qxa1kpbp9bw9 1

--查看child_number 为0 的执行计划

SQL> select *from table(dbms_xplan.display_cursor('6qxa1kpbp9bw9',0));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID6qxa1kpbp9bw9, child number 0

-------------------------------------

select count(*) from bind_a where name=:t

Plan hash value: 3761517753

-----------------------------------------------------------------------------

| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0| SELECT STATEMENT | || | 7 (100)| |

| 1| SORT AGGREGATE || 1 | 5 | | |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|* 2 |TABLE ACCESS FULL| BIND_A | 5000| 25000 | 7 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

2- filter("NAME"=:T)

19 rows selected.

--产生的第一个cursor（child_number=0）的执行计划是全表扫描，我们查询的数据占整个数据的1%, 走索引比较划算，所以Oracle 创建了child cursor。 并将Bind-Sensitive标记为Y。

--查看child_number为1的执行计划

SQL> select * fromtable(dbms_xplan.display_cursor('6qxa1kpbp9bw9',1));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID6qxa1kpbp9bw9, child number 1

-------------------------------------

select count(*) from bind_a where name=:t

Plan hash value: 1084430089

--------------------------------------------------------------------------------

| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time|

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0| SELECT STATEMENT | | || 1 (100)|

| 1| SORT AGGREGATE || 1 | 5 | |

|* 2 |INDEX RANGE SCAN| IDX_BA_NAME |100 | 500 | 1(0)| 00:00:01

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

2- access("NAME"=:T)

19 rows selected.

SQL>

-- 如果我们现在去查占数据99%的Dave时，在10g里还是使用之前的plan和cursor，现在我们看11g里怎么处理的。

--第一次查询99%的Dave。

SQL> exec:t:='Dave';

PL/SQL procedure successfully completed.

SQL> selectcount(*) from bind_a where name=:t;

COUNT(*)

----------

9900

--查看cursor

SQL> SELECT CHILD_NUMBER, EXECUTIONS,BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI", IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQLWHERE sql_text like 'select count(*) from bind_a%';

CHILD_NUMBER EXECUTIONS BUFFER_GETSBIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- --------------------- ---------- ----------

0 1 23 N N Y

1 3 28 Y NY

--这里还是使用childcursor 1.

--第二次查询99%的Dave。

SQL> select count(*) from bind_a wherename=:t;

COUNT(*)

----------

9900

SQL> SELECT CHILD_NUMBER, EXECUTIONS,BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI", IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQLWHERE sql_text like 'select count(*) from bind_a%';

CHILD_NUMBER EXECUTIONS BUFFER_GETSBIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- --------------------- ---------- ----------

0 1 23 N N Y

1 4 52 Y N Y

--这个地方并没有bind-aware并没有标记为Y，可能我们的数据太少，Oracle 没有正确判断。

--增加表的数据，并重新收集统计信息信息

SQL> begin

2 for i in 1..100000 loop

3 insert into bind_a values(i,'Dave');

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> execdbms_stats.gather_table_stats('SYS','BIND_A',METHOD_OPT => 'FOR COLUMNS SIZE 10 NAME');

PL/SQL procedure successfully completed.

--添加数据后第一次查询99%数据Dave

SQL>select count(*) from bind_a where name=:t;

COUNT(*)

----------

109900

--查看cursor

SQL> SELECT CHILD_NUMBER, EXECUTIONS,BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI", IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQLWHERE sql_text like 'select count(*) from bind_a%';

CHILD_NUMBER EXECUTIONS BUFFER_GETSBIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- --------------------- ---------- ----------

0 1 23 N N Y

1 4 52 Y N Y

--添加数据后第二次查询99%数据Dave

SQL> select count(*) frombind_a where name=:t;

COUNT(*)

----------

109900

SQL> SELECT CHILD_NUMBER, EXECUTIONS,BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI", IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQLWHERE sql_text like 'select count(*) from bind_a%';

CHILD_NUMBER EXECUTIONS BUFFER_GETSBIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- --------------------- ---------- ----------

0 1 23 N N Y

1 4 52 Y N Y

2 1 246 Y Y Y

--注意这里创建了第三个child cursor。并且表标记为bind-aware了。

在本章的2.3.3 节讲过，当cursor表标记为bind-aware后，在下次执行时会重新创建plan和cursor，如果cursor 重复，就合并，如果要创建的plan在cache不存在，就需要重新进行硬解析。

并且将原来的cursor 的V$SQL.IS_SHAREABLE 设置为 N。

--添加数据后第三次查询99%数据Dave

SQL> select count(*) from bind_a where name=:t;

COUNT(*)

----------

109900

SQL> SELECT CHILD_NUMBER,EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"FROM V$SQL WHEREsql_text like 'select count(*)from bind_a%';

CHILD_NUMBER EXECUTIONS BUFFER_GETSBIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- --------------------- ---------- ----------

0 1 23 N N Y

1 4 52 Y N Y

2 2 480 Y Y Y

--这里的childcusor 执行了2次

--说明，这里遇到一个很奇怪的问题，就是执行select 以后，v$sql 里的executions 没有变化，然后又重复执行了几次，还是没有变，等了N长时间，v$sql 里的值该有变化，不知道是bug还是11g里的机制变了。

SQL> SELECT CHILD_NUMBER, EXECUTIONS,BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI", IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE" FROM V$SQLWHERE sql_text like 'select count(*) from bind_a%';

child_number executions buffer_gets bind_sensi bind_aware bind_share

------------ ---------- --------------------- ---------- ----------

0 123 N N Y

1 4 52 Y N N

2 10 2352 Y Y Y

31 2 Y Y Y

--不过这个结果已经出现了。 child cursor 变成为shared。 并且重新创建了child cursor 3. 原来的cursor（1） 被标记为非共享。

--现在我们查看一下cursor2 和 cursor 3 的执行计划。

SQL> select *from table(dbms_xplan.display_cursor('6qxa1kpbp9bw9',2));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID6qxa1kpbp9bw9, child number 2

-------------------------------------

select count(*) from bind_a where name=:t

Plan hash value: 1910314130

--------------------------------------------------------------------------------

| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 0| SELECT STATEMENT | | || 63 (100)| |

| 1| SORT AGGREGATE | | 1 |5 | | |

|* 2 |INDEX FAST FULL SCAN| IDX_BA_NAME |109K| 536K| 63 (2)| 00:00

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

:01 |

--------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

2- filter("NAME"=:T)

19 rows selected.

SQL> select * fromtable(dbms_xplan.display_cursor('6qxa1kpbp9bw9',3));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID6qxa1kpbp9bw9, child number 3

-------------------------------------

select count(*) from bind_a where name=:t

Plan hash value: 1084430089

--------------------------------------------------------------------------------

| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time|

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

| 0| SELECT STATEMENT | | || 1 (100)||

| 1| SORT AGGREGATE || 1 | 5 | |

|* 2 |INDEX RANGE SCAN| IDX_BA_NAME |79 | 395 | 1(0)| 00:00:01

Predicate Information (identified byoperation id):

---------------------------------------------------

2- access("NAME"=:T)

19 rows selected.

现在我们小结一下：

selectcount(*) from bind_a where name=:t; 语句对应了多个游标，我这里是2个，child cursor2和child cursor3. 并且在oracle11g里面，这些cursor 是共享的。 这个也就是我们之前讲的，oracle 会生成一个范围的执行计划。

当绑定变量值不一样的时候，Oracle 就会选择对应的cursor 和 plan 来执行。

--为了验证上面的结论，我们在查看2个不同的变量值，我们直接看对应的执行计划。

SQL> exec:t:='DBA';

PL/SQL procedure successfully completed.

SQL> select count(*) from bind_a wherename=:t;

COUNT(*)

----------

100

Execution Plan

----------------------------------------------------------

Plan hash value: 1910314130

--------------------------------------------------------------------------------

| Id| Operation |Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0| SELECT STATEMENT | | 1 |5 | 63 (2)| 00:00:01 |

| 1| SORT AGGREGATE | | 1 |5 |

|* 2| INDEX FAST FULL SCAN| IDX_BA_NAME |55000 | 268K| 63(2)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

2- filter("NAME"=:T)

SQL> exec:t:='Dave';

PL/SQL procedure successfully completed.

SQL> select count(*) from bind_a wherename=:t;

COUNT(*)

----------

109900

Execution Plan

----------------------------------------------------------

Plan hash value: 1910314130

--------------------------------------------------------------------------------

| Id| Operation | Name| Rows | Bytes | Cost (%CPU)|Time

--------------------------------------------------------------------------------

| 0| SELECT STATEMENT | | 1 |5 | 63 (2)| 00:00:01 |

| 1| SORT AGGREGATE || 1 |5 |

|* 2| INDEX FAST FULL SCAN| IDX_BA_NAME |55000 | 268K| 63(2)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

2- filter("NAME"=:T)

--我们直接查看执行计划，他们是一样的。 至于为什么这2个执行计划是一样的，稍后在解释。先通过v$sql 视图验证一下。

--在执行之前，查看一下v$sql 里的信息。

SQL> SELECT sql_id,CHILD_NUMBER,EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

2 IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

3 FROM V$SQL WHEREsql_text like 'select count(*)from bind_a%';

sql_idchild_number executions buffer_gets bind_sensi bind_aware bind_share

------------- ------------ --------------------- ---------- ---------- ----------

6qxa1kpbp9bw9 01 23 N N Y

6qxa1kpbp9bw9 14 52 Y N N

6qxa1kpbp9bw9 214 3288 Y Y Y

6qxa1kpbp9bw9 38 16 Y Y Y

--用'Dave' 值进行查询，这时候，应该选择cursor 2. Index Full Scan。

SQL> exec :t:='Dave';

PL/SQL procedure successfully completed.

SQL> select count(*) from bind_a wherename=:t;

COUNT(*)

----------

109900

SQL> SELECT sql_id,CHILD_NUMBER,EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

2 IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

3 FROM V$SQL WHEREsql_text like 'select count(*)from bind_a%';

sql_idchild_number executions buffer_gets bind_sensi bind_aware bind_share

------------- ------------ --------------------- ---------- ---------- ----------

6qxa1kpbp9bw9 0 1 23 N N Y

6qxa1kpbp9bw9 14 52 Y N N

6qxa1kpbp9bw9 215 3522 Y Y Y

6qxa1kpbp9bw9 38 16 Y Y Y

--注意，cursor变成了15，执行了一次。

--使用'DBA' 进行查询，这个时候应该选择 cursor 3. index range scan。

SQL> exec :t:='DBA';

PL/SQL procedure successfully completed.

SQL>select count(*) from bind_a where name=:t;

COUNT(*)

----------

100

SQL> SELECT sql_id,CHILD_NUMBER,EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

2 IS_BIND_AWARE AS"BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

3 FROM V$SQL WHEREsql_text like 'select count(*)from bind_a%';

sql_idchild_number executions buffer_gets bind_sensi bind_aware bind_share

------------- ------------ --------------------- ---------- ---------- ----------

6qxa1kpbp9bw9 01 23 N N Y

6qxa1kpbp9bw9 14 52 Y N N

6qxa1kpbp9bw9 215 3522 Y Y Y

6qxa1kpbp9bw9 3 9 18 Y Y Y

--注意，这里的cursor3执行了一次。

这个就和我们上面的结论保持一致。

最后说明一下为什么2次查询的执行计划是一样的。 因为我们看到的执行计划并不是真正的执行计划。 有关查看真正的执行计划，参考我的Blog：

Oracle 从缓存里面查找真实的执行计划

http://blog.csdn.net/xujinyang/article/details/6838159

-------------------------------------------------------------------------------------------------------