ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 绑定变量和BIND PEEKING

绑定变量和BIND PEEKING

原创 Linux操作系统 作者:sxzhanghl 时间:2009-06-07 13:16:06 0 删除 编辑

使用绑定变量可以减少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
  • 对于使用不同绑定变量执行计划应该不同的情况,建议不要使用绑定变量,否则可能会产生随机的执行计划(硬分析后的所有执行都使用第一个执行计划,执行计划和第一次执行的参数有关)

关于非绑定变量情况在使用cursor_sharing=force,similar情况下的bind peeking,请参考另外一篇文章  绑定变量的一旦心得 :http://www.违规广告.cn/forum/showtopic.jsp?rootid=4739&CPages=1#

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/730796/viewspace-605371/,如需转载,请注明出处,否则将追究法律责任。

下一篇: histogram与10053(zt)
请登录后发表评论 登录
全部评论

注册时间:2009-03-19

  • 博文量
    31
  • 访问量
    58798