bind peeking--绑定变量窥视

ゝ一世哀愁。 2022-08-11 03:27 150阅读 0赞

绑定变量窥视，就是指oracle在第一次解析SQL语句的时候（也就是说该SQL第一次传入shared pool），会将你输入的绑定变量的值带入SQL语句里，从而参考你的字面值来猜测该SQL大概会返回多少条记录，从而得到优化的执行计划。然后，以后再次执行相同的SQL语句时，不再考虑你所输入的绑定变量的值，直接取出第一次生成的绑定变量。但是，很可惜的是，使用绑定变量从而共享游标与SQL优化是两个矛盾的目标。Oracle使用绑定变量的前提，是oracle认为大部分的列的数据都是分布比较均匀的。从而，使用第一次的绑定变量的值所得到的执行计划，大多数情况下都能适用于该绑定变量的其他的值。很明显，如果第一次传入的绑定变量的值恰好占整个数据量的百分比较高，从而导致全表扫描的执行计划。而后来传入的绑定变量的值都占整个数据量的百分比都很低，则应该走索引扫描会更好的，但是由于使用了绑定变量，从而oracle并不会再去看你的绑定变量的值，而是直接拿全表扫描的执行计划来用。这时，由于使用了绑定变量，虽然我们达到了游标共享，从而节省CPU的目的，但是SQL的执行计划却不够优化了。  
  
实验

SCOTT@ fyl>create table t1 as select * from dba_objects;
    SCOTT@ fyl>create index t1_id on t1(object_id);
    SCOTT@ fyl>execute dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'T1' ,method_opt => 'for all indexed columns' ,cascade => true);
    
    SCOTT@ fyl>var id number;
    SCOTT@ fyl>exec :id :=100;
    SCOTT@ fyl>set autotrace traceonly;
    SCOTT@ fyl>select * from t1 where object_id<:id;
    98 rows selected.
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2288890262
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       |  3272 |   313K|    17   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1    |  3272 |   313K|    17   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | T1_ID |   589 |       |     3   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("OBJECT_ID"<TO_NUMBER(:ID))
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             18  consistent gets
              0  physical reads
              0  redo size
          10120  bytes sent via SQL*Net to client
            485  bytes received via SQL*Net from client
              8  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             98  rows processed
    
    SCOTT@ fyl>exec :id :=50000;
    SCOTT@ fyl>select * from t1 where object_id<:id;
    49588 rows selected.
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2288890262
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       |  3272 |   313K|    17   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T1    |  3272 |   313K|    17   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | T1_ID |   589 |       |     3   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("OBJECT_ID"<TO_NUMBER(:ID))
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
           7887  consistent gets
              0  physical reads
              0  redo size
        5495043  bytes sent via SQL*Net to client
          36774  bytes received via SQL*Net from client
           3307  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
          49588  rows processed
    
    由于使用了绑定变量导致两次执行计划一样，Plan hash value: 2288890262
    SCOTT@ fyl>select /*+full(t1) */ * from t1 where object_id<:50000;
    49588 rows selected.
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3617692013
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |  3272 |   313K|   262   (1)| 00:00:04 |
    |*  1 |  TABLE ACCESS FULL| T1   |  3272 |   313K|   262   (1)| 00:00:04 |
    --------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter("OBJECT_ID"<TO_NUMBER(:ID))
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
           4198  consistent gets
              0  physical reads
              0  redo size
        2349310  bytes sent via SQL*Net to client
          36774  bytes received via SQL*Net from client
           3307  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
          49588  rows processed
    走索引时    object_id<:50000--  7887  consistent gets
    全表扫描时  object_id<:50000--  4198  consistent gets

OPT\_PARAM('\_optim\_peek\_user\_binds' 'false')

select /\*+opt\_param('\_optim\_peek\_user\_binds', 'false')\*/ id,name from test1 where id=:fid;

11G--ACS其实就是根据不同绑定变量的值为同一个SQL生成更多更优的执行计划，来适应data skew的不同情况  
具体可参见 http://blog.itpub.net/15415488/viewspace-621535