开博了。其实之前有的，只是不在pub上，最近实在忍受不了msn共享空间了。
关于绑定变量
上一篇 / 下一篇 2008-01-24 14:41:27 / 个人分类：读书笔记
关于绑定变量一直理解的不是很清晰，最近看文档，基本了解。
什么是绑定变量？
查询通常只是因为改变where子句中的内容而产生不同的结果。为了在这种情况下避免硬解析，需要使用绑定变量(bind variable)。它是用户放入查询中的占位符，它会告诉Oracle"我会随后为这个变量提供一个值，现在需要生成一个方案，但我实际执行语句的时 候，我会为您提供应该使用的实际值"。
select * from emp where ename='KING'; //不使用绑定变量
select * from emp where ename=:bv //使用绑定变量
一般在 procedure or function 中使用，可以优化共享池的使用。
一、Oracle SQL语句共享区的机制
1、SGA 区结构：
Oracle 数据库启动时，在内存中分配了一大片空间，为系统全局区(System Global Area)，其中包含 Sql 共享池及数据缓存器(Data Buffer Cache)。SGA 区的共享池部分主要由三个区域组成: 库缓存, 字典缓存, 控制结构。库缓存包括共享 SQL 区，私有SQL区，PL/SQL 过程及包, 及控制结构，如锁及库缓存handles。用户执行过的 Sql 语句存放于 Sql 共享池中，以便可以重用，提高其效率。
2、SQL语句在内存中的分布：
Oracle 将其执行的每一条 SQL 语句存于共享SQL 区及私有 SQL 区中。当Oracle 发现两个用户执行相同的SQL语句时，则为这些用户重用SQL共享区。但是，每一用户必须在私有SQL区中拥有该语句的一份独立拷贝。共享SQL 区包含单一SQL语句或相同的SQL语句的解析树及执行计划。通过为多个相同的DML语句使用一个共享SQL区，Oracle 节省了内存的使用, 特别是当许多用户使用同一应用时。 共享SQL区永远驻留在共享池中。
3、SQL语句解析时进行的内存分配操作：
当一个SQL语句被提交至Oracle 去执行时，Oracle 自动地执行以下内存分配步骤:
Oracle 检查共享池，看是否在共享SQL区中已存在相同的语句。 若有，则该共享SQL区被用于执行该语句的新实例的后续操作。 相应地，若在共享池中无该语句，则Oracle在共享池中分配一新的共享SQL区,其尺寸决定于该语句的复杂性。 若一个SQL语句要求新的共享SQL区而整个共享池已被分配完毕，则 Oracle 可通过一个最近最少修改机理从共享池中释放部分项目，直至可为新语句的共享SQL区提供足够的空间。 若 Oracle释放了一个共享 SQL 区，则与该区相关联的SQL语句在下次重执行时，须重新解析并重新分配至另一共享SQL区。 在两种情况下，用户专用SQL区与包含该语句的共享SQL区相关联。
因而，若能使语句得到共享，则其将减少内存的占用，同时，减少了cpu 的占用，加快了语句执行的速度。
即使一个光标仍处于打开状态，若其很久未被使用了，则其共享区也可能被从共享池中移出。若该光标以后又被用于执行其语句，则Oracle重解析该语句并且在共享池中分配一新的共享SQL区。
4、私有SQL区
私有SQL区包含绑定信息及运行时缓冲等数据。 每一个提交一个SQL语句的会话均有一个私有SQL区。 每一提交相同SQL语句的用户有其使用单一共享SQL区的私有SQL区。许多私有SQL区可以与同一共享SQL区相关联
一个私有SQL区包括一个永久区和一个运行时区:
一个永久区包含在执行过程中保持的绑定信息，数据类型转换的代码(在定义的数据类型与查询列的数据类型不一致时), 及其它状态信息(比如递归或远程光标数或并行查询的状态)。 永久区的尺寸决定于绑定变量的数目及语句中指定的列数。 例如, 若一个查询中指定了很多列，则永久区要大一些。
运行时区包含SQL语句被执行时使用的一些信息。 运行时区的尺寸信赖于被执行的SQL语句的类型及其复杂性及被该语句处理的行的尺寸。 一般而言, 用于INSERT, UPDATE, 及 DELETE 的语句其运行区要比 SELECT 语句所需的运行区尺寸要小。
绑定变量和BIND PEEKING
使用绑定变量可以减少SQL PARSE，但是使用绑定变量有一个不好的地方，就是对于访问具有倾斜的列，可能使用错误的执行计划。在Oracle 9i之前，如果WHERE 条件里面全部使用绑定变量，那么只能使用固定的选择性参数来确定执行计划。
=操作和>=操作的选择性为5%，范围扫描的选择性为25%。缺省值的方式可能生成不好的执行计划。所以 Oracle 9i就出现了一个新的技术，bind peeking。什么是bind peeking呢？当SQL第一次执行的时候，优化器会根据绑定变量来确定执行计划（如果存在柱状图）。BIND PEEKING只有当该SQL第一次执行的时候，进行HARD PARSE的时候才进行，第二次调用该SQL，就不会再次进行BIND PEEKING。这种情况下，就存在另外一个风险，如果某个列的倾斜性很厉害，那么使用BIND PEEKING就是不安全的，因为不同的参数代入，只能走第一次执行时的执行计划，那么执行计划就像掷色子一样，要靠运气了。碰到这种情况，应用就不应该使用绑定变量，而应该改为直接值了。
下面是一个实验：
1、创建一个表create table tt2 as select * from dba_objects; 也可以再执行几次，把记录数搞大一些
2、update tt2 set status='INVALID' where rownum<100;
3、update tt2 set status='IND' where status='VALID' and rownum<3;
4、经过上述操作，就会产生几万valid，89条 invalid和2条ind的记录。下面进行分析
5、create index ind_tt2_status on tt2 (status);
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','TT2',method_opt => 'for all indexed columns size skewonly');
PL/SQL 过程已成功完成。
SQL> select table_name,COLUMN_NAME,endpoint_number,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE from user_tab_histograms where table_name='T
T2' ORDER BY COLUMN_nAME;
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
------------------------------ ------------------------------ --------------- -------------- --------------------
TT2 STATUS 21 3.8063E+35
TT2 STATUS 29819 4.4786E+35
TT2 STATUS 2 3.8063E+35
6、执行：
var a varchar2(10);
exec :a:='VALID';
select object_id,status from tt2 where status=:a;
7、查看执行计划（这个时候最好不要用AUTOTRACE，由于BUG，AUTOTRACE显示的执行计划是错误的），可以通过v$sql_plan，或者干脆用sql_trace
查看sql_trace的时候，如果看裸文件，那么parse #1 。。。mis=1说明是硬解析，否则就是软解析
我们使用v$_sql_plan来看：
Plan Table
--------------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| TABLE ACCESS FULL |TT2 | 0 | 123K| 123K| 0 | 0 | 0 | 0 |
| SELECT STATEMENT | | 0 | | 123K| 0 | 0 | 0 | 0 |
------------------------------------------------------------------------------------------------------------------------
8、然后在执行：
exec :a:='INVALID';
select object_id,status from tt2 where status=:a;
查看执行计划和上面的相同
Plan Table
--------------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| TABLE ACCESS FULL |TT2 | 0 | 123K| 123K| 0 | 0 | 0 | 0 |
| SELECT STATEMENT | | 0 | | 123K| 0 | 0 | 0 | 0 |
------------------------------------------------------------------------------------------------------------------------
如果生成了TRACE文件，可以看到：
SELECT OBJECT_NAME,STATUS
FROM
TT2 WHERE STATUS=:A
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.01 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 8213 0.56 0.59 0 18352 0 123154
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8217 0.56 0.60 0 18352 0 123154
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
4 TABLE ACCESS FULL TT2
从这里可以看出执行了2次parse，其中misses in library cache during parse:1,说明其中一次是硬分析（从裸文件也可以看出mis=1的有一次）
9、刷新一下共享池alter system flush shared_pool;
10、然后再执行
exec :a:='INVALID';
select object_id,status from tt2 where status=:a;
看到的执行计划如下：
Plan Table
-------------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| INDEX RANGE SCAN |IDX_TT2_STATUS | 0 | 178 | 123K| 0 | 0 | 0 | 0 |
| TABLE ACCESS BY INDEX ROWID |TT2 | 0 | 178 | 123K| 0 | 0 | 0 | 0 |
| SELECT STATEMENT | | 0 | | 123K| 0 | 0 | 0 | 0 |
------------------------------------------------------------------------------------------------------------------------
11、再执行：
exec :a:='VALID';
select object_id,status from tt2 where status=:a;
看到的执行计划还是和上面的相同，走索引范围扫描。
12、这就是bind peeking的含义，使用绑定变量的时候，能够共享SQL，其执行计划是在第一次parse的时候做peeking生成的。根据当前的绑定变量的值。今后再次执行，就认为SQL是安全的，不再进行peeking，也不会生成新的执行计划
13、如果删除柱状图，那么还是否会产生PEEKING呢？
SQL> exec dbms_stats.delete_column_stats(ownname=>'SCOTT',TABNAME=>'TT2',COLNAME=>'STATUS');
PL/SQL 过程已成功完成。
SQL> exec :a:='VALID';
PL/SQL 过程已成功完成。
SQL> select object_id,status from tt2 where status=:a;
已选择123150行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1233 Bytes=22
194)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TT2' (Cost=2 Card=1233 B
ytes=22194)
2 1 INDEX (RANGE SCAN) OF 'IDX_TT2_STATUS' (NON-UNIQUE) (Cos
t=1 Card=493)
Statistics
----------------------------------------------------------
303 recursive calls
0 db block gets
18388 consistent gets
1955 physical reads
0 redo size
2185342 bytes sent via SQL*Net to client
90802 bytes received via SQL*Net from client
8211 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
123150 rows processed
Plan Table
---------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| INDEX RANGE SCAN |IDX_TT2_STATUS | | 493 | | | | | |
| TABLE ACCESS BY INDEX ROWID |TT2 | | 1K| | | | | |
| SELECT STATEMENT | | | | | | | | |
------------------------------------------------------------------------------------------------------------------------
在没有柱状图的情况下，优化器采用了缺省值，认为选择性是5%，毫不犹豫就走了索引。
14、以上测试是在CURSOR_SHARING=EXACT情况的，再来看看在CURSOR_SHARING=SIMILAR的情况下是什么样的？
SQL> exec :a:='VALID';
PL/SQL 过程已成功完成。
SQL> select object_id,status from tt2 where status=:a;
已选择123150行。
查看执行计划，发现是走全表扫描，再执行：
SQL> exec :a:='INVALID';
PL/SQL 过程已成功完成。
SQL> select object_id,status from tt2 where status=:a;
已选择123150行。
设置:a='INVALID';
执行还是全表扫描，说明第二次执行的时候BIND PEEKING没有出现。而第一次执行出现了bind peeking,再次确认：
Plan Table
------------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| INDEX RANGE SCAN |IDX_TT2_STATUS | | 178 | | | | | |
| TABLE ACCESS BY INDEX ROWID |TT2 | | 178 | | | | | |
| SELECT STATEMENT | | | | | | | | |
------------------------------------------------------------------------------------------------------------------------
走了索引，确认了第一次执行是进行了bind peeking的。
15、下面测试cursor_sharing=force的情况
SQL> alter session set cursor_sharing=force;
会话已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> exec :a:='INVALID';
PL/SQL 过程已成功完成。
SQL> select object_id,status from tt2 where status=:a;
已选择178行。
Plan Table
-----------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| INDEX RANGE SCAN |IDX_TT2_STATUS | | 178 | | | | | |
| TABLE ACCESS BY INDEX ROWID |TT2 | | 178 | | | | | |
| SELECT STATEMENT | | | | | | | | |
------------------------------------------------------------------------------------------------------------------------
第一次执行也是做了BIND PEEKING的，
16、结论：
* BIND PEEKING对于使用绑定变量的情况下，选择较优的执行计划有一定的作用
* BIND PEEKING只有在存在柱状图的情况下才能工作
* BIND PEEKING只在做HARD PARSE的时候才产生，随后的执行如果不需要HARD PARSE就不会进行BIND PEEKING，这种情况和CURSOR_SHARING无关
* 由于以上原因，使用绑定变量的时候可以有效的减少PARSE
* 对于使用不同绑定变量执行计划应该不同的情况，建议不要使用绑定变量，否则可能会产生随机的执行计划（硬分析后的所有执行都使用第一个执行计划，执行计划和第一次执行的参数有关）
什么是绑定变量？
查询通常只是因为改变where子句中的内容而产生不同的结果。为了在这种情况下避免硬解析，需要使用绑定变量(bind variable)。它是用户放入查询中的占位符，它会告诉Oracle"我会随后为这个变量提供一个值，现在需要生成一个方案，但我实际执行语句的时 候，我会为您提供应该使用的实际值"。
select * from emp where ename='KING'; //不使用绑定变量
select * from emp where ename=:bv //使用绑定变量
一般在 procedure or function 中使用，可以优化共享池的使用。
一、Oracle SQL语句共享区的机制
1、SGA 区结构：
Oracle 数据库启动时，在内存中分配了一大片空间，为系统全局区(System Global Area)，其中包含 Sql 共享池及数据缓存器(Data Buffer Cache)。SGA 区的共享池部分主要由三个区域组成: 库缓存, 字典缓存, 控制结构。库缓存包括共享 SQL 区，私有SQL区，PL/SQL 过程及包, 及控制结构，如锁及库缓存handles。用户执行过的 Sql 语句存放于 Sql 共享池中，以便可以重用，提高其效率。
2、SQL语句在内存中的分布：
Oracle 将其执行的每一条 SQL 语句存于共享SQL 区及私有 SQL 区中。当Oracle 发现两个用户执行相同的SQL语句时，则为这些用户重用SQL共享区。但是，每一用户必须在私有SQL区中拥有该语句的一份独立拷贝。共享SQL 区包含单一SQL语句或相同的SQL语句的解析树及执行计划。通过为多个相同的DML语句使用一个共享SQL区，Oracle 节省了内存的使用, 特别是当许多用户使用同一应用时。 共享SQL区永远驻留在共享池中。
3、SQL语句解析时进行的内存分配操作：
当一个SQL语句被提交至Oracle 去执行时，Oracle 自动地执行以下内存分配步骤:
Oracle 检查共享池，看是否在共享SQL区中已存在相同的语句。 若有，则该共享SQL区被用于执行该语句的新实例的后续操作。 相应地，若在共享池中无该语句，则Oracle在共享池中分配一新的共享SQL区,其尺寸决定于该语句的复杂性。 若一个SQL语句要求新的共享SQL区而整个共享池已被分配完毕，则 Oracle 可通过一个最近最少修改机理从共享池中释放部分项目，直至可为新语句的共享SQL区提供足够的空间。 若 Oracle释放了一个共享 SQL 区，则与该区相关联的SQL语句在下次重执行时，须重新解析并重新分配至另一共享SQL区。 在两种情况下，用户专用SQL区与包含该语句的共享SQL区相关联。
因而，若能使语句得到共享，则其将减少内存的占用，同时，减少了cpu 的占用，加快了语句执行的速度。
即使一个光标仍处于打开状态，若其很久未被使用了，则其共享区也可能被从共享池中移出。若该光标以后又被用于执行其语句，则Oracle重解析该语句并且在共享池中分配一新的共享SQL区。
4、私有SQL区
私有SQL区包含绑定信息及运行时缓冲等数据。 每一个提交一个SQL语句的会话均有一个私有SQL区。 每一提交相同SQL语句的用户有其使用单一共享SQL区的私有SQL区。许多私有SQL区可以与同一共享SQL区相关联
一个私有SQL区包括一个永久区和一个运行时区:
一个永久区包含在执行过程中保持的绑定信息，数据类型转换的代码(在定义的数据类型与查询列的数据类型不一致时), 及其它状态信息(比如递归或远程光标数或并行查询的状态)。 永久区的尺寸决定于绑定变量的数目及语句中指定的列数。 例如, 若一个查询中指定了很多列，则永久区要大一些。
运行时区包含SQL语句被执行时使用的一些信息。 运行时区的尺寸信赖于被执行的SQL语句的类型及其复杂性及被该语句处理的行的尺寸。 一般而言, 用于INSERT, UPDATE, 及 DELETE 的语句其运行区要比 SELECT 语句所需的运行区尺寸要小。
绑定变量和BIND PEEKING
使用绑定变量可以减少SQL PARSE，但是使用绑定变量有一个不好的地方，就是对于访问具有倾斜的列，可能使用错误的执行计划。在Oracle 9i之前，如果WHERE 条件里面全部使用绑定变量，那么只能使用固定的选择性参数来确定执行计划。
=操作和>=操作的选择性为5%，范围扫描的选择性为25%。缺省值的方式可能生成不好的执行计划。所以 Oracle 9i就出现了一个新的技术，bind peeking。什么是bind peeking呢？当SQL第一次执行的时候，优化器会根据绑定变量来确定执行计划（如果存在柱状图）。BIND PEEKING只有当该SQL第一次执行的时候，进行HARD PARSE的时候才进行，第二次调用该SQL，就不会再次进行BIND PEEKING。这种情况下，就存在另外一个风险，如果某个列的倾斜性很厉害，那么使用BIND PEEKING就是不安全的，因为不同的参数代入，只能走第一次执行时的执行计划，那么执行计划就像掷色子一样，要靠运气了。碰到这种情况，应用就不应该使用绑定变量，而应该改为直接值了。
下面是一个实验：
1、创建一个表create table tt2 as select * from dba_objects; 也可以再执行几次，把记录数搞大一些
2、update tt2 set status='INVALID' where rownum<100;
3、update tt2 set status='IND' where status='VALID' and rownum<3;
4、经过上述操作，就会产生几万valid，89条 invalid和2条ind的记录。下面进行分析
5、create index ind_tt2_status on tt2 (status);
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','TT2',method_opt => 'for all indexed columns size skewonly');
PL/SQL 过程已成功完成。
SQL> select table_name,COLUMN_NAME,endpoint_number,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE from user_tab_histograms where table_name='T
T2' ORDER BY COLUMN_nAME;
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALU
------------------------------ ------------------------------ --------------- -------------- --------------------
TT2 STATUS 21 3.8063E+35
TT2 STATUS 29819 4.4786E+35
TT2 STATUS 2 3.8063E+35
6、执行：
var a varchar2(10);
exec :a:='VALID';
select object_id,status from tt2 where status=:a;
7、查看执行计划（这个时候最好不要用AUTOTRACE，由于BUG，AUTOTRACE显示的执行计划是错误的），可以通过v$sql_plan，或者干脆用sql_trace
查看sql_trace的时候，如果看裸文件，那么parse #1 。。。mis=1说明是硬解析，否则就是软解析
我们使用v$_sql_plan来看：
Plan Table
--------------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| TABLE ACCESS FULL |TT2 | 0 | 123K| 123K| 0 | 0 | 0 | 0 |
| SELECT STATEMENT | | 0 | | 123K| 0 | 0 | 0 | 0 |
------------------------------------------------------------------------------------------------------------------------
8、然后在执行：
exec :a:='INVALID';
select object_id,status from tt2 where status=:a;
查看执行计划和上面的相同
Plan Table
--------------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| TABLE ACCESS FULL |TT2 | 0 | 123K| 123K| 0 | 0 | 0 | 0 |
| SELECT STATEMENT | | 0 | | 123K| 0 | 0 | 0 | 0 |
------------------------------------------------------------------------------------------------------------------------
如果生成了TRACE文件，可以看到：
SELECT OBJECT_NAME,STATUS
FROM
TT2 WHERE STATUS=:A
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.01 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 8213 0.56 0.59 0 18352 0 123154
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8217 0.56 0.60 0 18352 0 123154
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
4 TABLE ACCESS FULL TT2
从这里可以看出执行了2次parse，其中misses in library cache during parse:1,说明其中一次是硬分析（从裸文件也可以看出mis=1的有一次）
9、刷新一下共享池alter system flush shared_pool;
10、然后再执行
exec :a:='INVALID';
select object_id,status from tt2 where status=:a;
看到的执行计划如下：
Plan Table
-------------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| INDEX RANGE SCAN |IDX_TT2_STATUS | 0 | 178 | 123K| 0 | 0 | 0 | 0 |
| TABLE ACCESS BY INDEX ROWID |TT2 | 0 | 178 | 123K| 0 | 0 | 0 | 0 |
| SELECT STATEMENT | | 0 | | 123K| 0 | 0 | 0 | 0 |
------------------------------------------------------------------------------------------------------------------------
11、再执行：
exec :a:='VALID';
select object_id,status from tt2 where status=:a;
看到的执行计划还是和上面的相同，走索引范围扫描。
12、这就是bind peeking的含义，使用绑定变量的时候，能够共享SQL，其执行计划是在第一次parse的时候做peeking生成的。根据当前的绑定变量的值。今后再次执行，就认为SQL是安全的，不再进行peeking，也不会生成新的执行计划
13、如果删除柱状图，那么还是否会产生PEEKING呢？
SQL> exec dbms_stats.delete_column_stats(ownname=>'SCOTT',TABNAME=>'TT2',COLNAME=>'STATUS');
PL/SQL 过程已成功完成。
SQL> exec :a:='VALID';
PL/SQL 过程已成功完成。
SQL> select object_id,status from tt2 where status=:a;
已选择123150行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1233 Bytes=22
194)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TT2' (Cost=2 Card=1233 B
ytes=22194)
2 1 INDEX (RANGE SCAN) OF 'IDX_TT2_STATUS' (NON-UNIQUE) (Cos
t=1 Card=493)
Statistics
----------------------------------------------------------
303 recursive calls
0 db block gets
18388 consistent gets
1955 physical reads
0 redo size
2185342 bytes sent via SQL*Net to client
90802 bytes received via SQL*Net from client
8211 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
123150 rows processed
Plan Table
---------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| INDEX RANGE SCAN |IDX_TT2_STATUS | | 493 | | | | | |
| TABLE ACCESS BY INDEX ROWID |TT2 | | 1K| | | | | |
| SELECT STATEMENT | | | | | | | | |
------------------------------------------------------------------------------------------------------------------------
在没有柱状图的情况下，优化器采用了缺省值，认为选择性是5%，毫不犹豫就走了索引。
14、以上测试是在CURSOR_SHARING=EXACT情况的，再来看看在CURSOR_SHARING=SIMILAR的情况下是什么样的？
SQL> exec :a:='VALID';
PL/SQL 过程已成功完成。
SQL> select object_id,status from tt2 where status=:a;
已选择123150行。
查看执行计划，发现是走全表扫描，再执行：
SQL> exec :a:='INVALID';
PL/SQL 过程已成功完成。
SQL> select object_id,status from tt2 where status=:a;
已选择123150行。
设置:a='INVALID';
执行还是全表扫描，说明第二次执行的时候BIND PEEKING没有出现。而第一次执行出现了bind peeking,再次确认：
Plan Table
------------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| INDEX RANGE SCAN |IDX_TT2_STATUS | | 178 | | | | | |
| TABLE ACCESS BY INDEX ROWID |TT2 | | 178 | | | | | |
| SELECT STATEMENT | | | | | | | | |
------------------------------------------------------------------------------------------------------------------------
走了索引，确认了第一次执行是进行了bind peeking的。
15、下面测试cursor_sharing=force的情况
SQL> alter session set cursor_sharing=force;
会话已更改。
SQL> alter system flush shared_pool;
系统已更改。
SQL> exec :a:='INVALID';
PL/SQL 过程已成功完成。
SQL> select object_id,status from tt2 where status=:a;
已选择178行。
Plan Table
-----------------------------------------------------------------------------------------------------------------------------
--------
| Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads | Writes | E-Time |
------------------------------------------------------------------------------------------------------------------------
| INDEX RANGE SCAN |IDX_TT2_STATUS | | 178 | | | | | |
| TABLE ACCESS BY INDEX ROWID |TT2 | | 178 | | | | | |
| SELECT STATEMENT | | | | | | | | |
------------------------------------------------------------------------------------------------------------------------
第一次执行也是做了BIND PEEKING的，
16、结论：
* BIND PEEKING对于使用绑定变量的情况下，选择较优的执行计划有一定的作用
* BIND PEEKING只有在存在柱状图的情况下才能工作
* BIND PEEKING只在做HARD PARSE的时候才产生，随后的执行如果不需要HARD PARSE就不会进行BIND PEEKING，这种情况和CURSOR_SHARING无关
* 由于以上原因，使用绑定变量的时候可以有效的减少PARSE
* 对于使用不同绑定变量执行计划应该不同的情况，建议不要使用绑定变量，否则可能会产生随机的执行计划（硬分析后的所有执行都使用第一个执行计划，执行计划和第一次执行的参数有关）
导入论坛 引用链接 收藏 分享给好友 推荐到圈子 管理 举报
TAG:
