绑定变量和BIND PEEKING

2021-10-09 21:19:28

http://www.oraclefans.cn/forum/showtopic.jsp?rootid=5467&CPages=1

http://blog.csdn.net/tianlesoftware/article/details/5544307

使用绑定变量可以减少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;

查看执行计划和上面的相同

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 Optimizer=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

在没有柱状图的情况下，优化器采用了缺省值，认为选择性是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,再次确认：

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行。

16、结论：

BIND PEEKING对于使用绑定变量的情况下，选择较优的执行计划有一定的作用
BIND PEEKING只有在存在柱状图的情况下才能工作
BIND PEEKING只在做HARD PARSE的时候才产生，随后的执行如果不需要HARD PARSE就不会进行BIND PEEKING，这种情况和CURSOR_SHARING无关
由于以上原因，使用绑定变量的时候可以有效的减少PARSE
对于使用不同绑定变量执行计划应该不同的情况，建议不要使用绑定变量，否则可能会产生随机的执行计划（硬分析后的所有执行都使用第一个执行计划，执行计划和第一次执行的参数有关）

关于非绑定变量情况在使用cursor_sharing=force,similar情况下的bind peeking，请参考另外一篇文章绑定变量的一旦心得

码农公寓

相关文章