关于绑定变量关闭的情况，Oracle是如何工作的？

太过爱你忘了你带给我的痛 2022-06-17 14:41 150阅读 0赞

关于如果绑定变量窥探被关闭了，oracle 会怎么处理的呢？是每次都硬解析还是这样处理?

首先介绍下绑定变量窥探：  
使用SQL首次运行时的值来生成执行计划。后续再次运行该SQL语句则使用首次执行计划来执行。  
测试版本：Oracle 12.1.0.2  
对于绑定变量列中的特殊值或非均匀分布列上的绑定变量会造成非高效的执行计划被选择并执行。要注意的是，Bind Peeking只发生在硬分析的时候，即SQL被第一次执行的时候，之后的变量将不会在做peeking。我们可以看出，Bind peeking并不能最终解决不同谓词导致选择不同执行计划的问题，它只能让SQL第一次执行的时候，执行计划选择更加准确，在第一次解析SQL时，按照窥探变量的值生成执行计划，以后这样的SQL都按照这个执行。隐藏参数\_optim\_peek\_user\_binds=true则启用绑定变量窥探，否则CBO认为统计列是均匀的。  
然后就是对这个问题分析处理思路如下:  
如果绑定变量窥探关闭了，那么是否有直方图对执行计划就影响不大了，因为oracle都把它作为一个参数带入，而不会去查看他的值，  
连第一次都不查看。如果不窥探，那么按照正常的值进行成本分析，然后oracle不考虑具体的值，把这个值作为一个均匀分布带入。  
如果窥探了，仅仅是第一次会去窥探，因此，如果每次都窥探，就变成硬解析了，  
硬解析对oltp有多大的危害，不觉明历：  
下面用简单的实验说明下这个问题：

*  结论1

： 绑定变量窥探一定要有直方图的时候才会起效果，否则一样窥探没效果。  
创建一张测试表，并创建一个索引，然后插入一批50000行object\_id为0的数据，然后收集一下表的统计信息，然而并不收集它的直方图信息，操作如下：

SQL> create table objt as select object_id,object_name from t_objects;
    表已创建。
    SQL> create index idx_obj_id on objt(object_id);
    索引已创建。
    SQL> declare
      2  begin
      3     for i in 1..50000 loop
      4             insert into objt values(0,i);
      5     end loop;
      6  end;
      7  /
    PL/SQL 过程已成功完成。
    SQL>
    SQL>
    SQL>
    SQL> select count(*) from objt; COUNT(*)
    ----------
        104960
    SQL> select count(*) from objt where object_id=0; COUNT(*)
    ----------
         50000
    SQL> BEGIN
      2     DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',
      3                                   tabname => 'OBJT',
      4                                   estimate_percent => 100,
      5                                   method_opt => 'for all columns size 1',
      6                                   degree => DBMS_STATS.AUTO_DEGREE,
      7                                   cascade=>TRUE
      8                                   );
      9  END;
     10  /
    PL/SQL 过程已成功完成。
    SQL> SELECT TABLE_NAME,column_name,num_distinct,histogram from dba_tab_col_statistics where table_name='OBJT' and column_name='OBJECT_ID';
    TABLE_NAME
    --------------------------------------------------------------------------------
    COLUMN_NAME
    --------------------------------------------------------------------------------
    NUM_DISTINCT HISTOGRAM
    ------------ ------------------------------
    OBJT
    OBJECT_ID
           54961 NONE

可以看到，表objt对应的列上是没有直方图统计信息的。  
然后执行一个赋值查询如下：

SQL> var a number;
    SQL> execute :a := 0;
    PL/SQL 过程已成功完成。
    SQL> set linesi 200
    SQL> select count(object_name) from objt where object_id=:a;
    COUNT(OBJECT_NAME) ------------------
     50000
    SQL> select * from table(dbms_xplan.display_cursor());
    PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------
    SQL_ID fmsznp4v247sc, child number 0 -------------------------------------
    select count(object_name) from objt where object_id=:a
    Plan hash value: 909060252 ---------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                     |            |       |       |     2 (100)|          |
    |   1 |  SORT AGGREGATE                      |            |     1 |    19 |            |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| OBJT       |     2 |    38 |     2   (0)| 00:00:01 |
    |* 3 | INDEX RANGE SCAN | IDX_OBJ_ID | 2 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id): ---------------------------------------------------
     3 - access("OBJECT_ID"=:A)
    已选择 20 行。

虽然0值占了一半的数据，但是CBO依然走了索引范围扫描。这里是不是感觉很不合理，应该全表扫描才对。主要原因是虽然使用了绑定变量窥探，但是由于没有直方图，即使窥探了，也是采用均匀分布分析，走索引的执行计划。  
然后再次收集表的统计信息，并且收集object\_id列上的直方图信息。

SQL> BEGIN
     2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TEST',
     3 tabname => 'OBJT',
     4 estimate_percent => 100,
     5 method_opt => 'for all columns size auto',
     6 degree => DBMS_STATS.AUTO_DEGREE,
     7 cascade=>TRUE
     8 );
     9 END;
     10 /
    PL/SQL 过程已成功完成。
    SQL> SELECT TABLE_NAME,column_name,num_distinct,histogram from dba_tab_col_statistics where table_name='OBJT' and column_name='OBJECT_ID';
    TABLE_NAME -------------------------
    COLUMN_NAME --------------------------
    NUM_DISTINCT HISTOGRAM ------------ ------------------------------ OBJT OBJECT_ID
     54961 HEIGHT BALANCED
    SQL> var b number;
    SQL> execute :b := 0;
    PL/SQL 过程已成功完成。
    SQL> select count(object_name) from objt where object_id=:b;
    COUNT(OBJECT_NAME) ------------------
     50000
    SQL> select * from table(dbms_xplan.display_cursor());
    PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID 4k53s1bkc30ph, child number 0 -------------------------------------
    select count(object_name) from objt where object_id=:b
    Plan hash value: 4162353065 ---------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |       |   106 (100)|          |
    |   1 |  SORT AGGREGATE    |      |     1 |    19 |            |          |
    |* 2 | TABLE ACCESS FULL| OBJT | 49587 | 920K| 106 (1)| 00:00:01 | ---------------------------------------------------------------------------
    Predicate Information (identified by operation id): ---------------------------------------------------
     2 - filter("OBJECT_ID"=:B)
    已选择 19 行。

再次查看，确实走了全表扫描，这就是绑定变量窥探的作用就体现了出来。

*  结论2

：关闭绑定变量窥探，以后每次也是软解析，而且关闭了绑定变量窥探之后，即使你分析了直方图还是执行均匀分布，按照cost计算出成本，然后走oracle认为最优的执行计划:

SQL> set linesi 200
    SQL> col name for a50
    SQL> col value for a20
    SQL> select
     2 x.ksppinm name,
     3 y.ksppstvl value,
     4 y.ksppstdf isdefault,
     5 decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
     6 decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
     7 from
     8 sys.x$ksppi x,
     9 sys.x$ksppcv y
     10 where
     11 x.inst_id = userenv('Instance') and
     12 y.inst_id = userenv('Instance') and
     13 x.indx = y.indx
     14 and x.ksppinm like '%_optim_peek_user_binds%'
     15 order by
     16 translate(x.ksppinm, ' _', ' ');
    NAME                                               VALUE                ISDEFAULT          ISMOD                ISADJ
    -------------------------------------------------- -------------------- ------------------ -------------------- ----------
    _optim_peek_user_binds                             TRUE                 TRUE               FALSE                FALSE
    SQL> alter session set "_optim_peek_user_binds"=false;
    会话已更改。
    SQL> var d number;
    SQL> exec :d := 0;
    PL/SQL 过程已成功完成。
    SQL> select count(object_name) from objt where object_id = :d;
    COUNT(OBJECT_NAME) ------------------
     50000
    SQL> col value for 99999999
    SQL> select a.value,b.value from v$sysstat a,v$sysstat b where a.name='parse count (hard)' and b.name='parse count (total)';
     VALUE VALUE
    --------- ---------
     14186 184689
    SQL> select count(object_name) from objt where object_id = :d;
    COUNT(OBJECT_NAME) ------------------
     50000
    SQL> select a.value,b.value from v$sysstat a,v$sysstat b where a.name='parse count (hard)' and b.name='parse count (total)';
     VALUE VALUE
    --------- ---------
     14186 184691 -----可以看到硬解析已经不再发生变化，但是total解析数一直在随着查询的增加而增加即为软解析或软软解析
    SQL> SELECT TABLE_NAME,column_name,num_distinct,histogram from dba_tab_col_statistics where table_name='OBJT' and column_name='OBJECT_ID';
    TABLE_NAME -------------------------------
    COLUMN_NAME -------------------------------
    NUM_DISTINCT HISTOGRAM ------------ ------------------------------ OBJT OBJECT_ID
     54961 HEIGHT BALANCED ----这里是有直方图的
    SQL> select count(object_name) from objt where object_id = :d;
    COUNT(OBJECT_NAME) ------------------
     50000
    SQL> select * from table(dbms_xplan.display_cursor());
    PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID d56agappx6ds9, child number 0 -------------------------------------
    select count(object_name) from objt where object_id = :d
    Plan hash value: 909060252 ---------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                     |            |       |       |     2 (100)|          |
    |   1 |  SORT AGGREGATE                      |            |     1 |    19 |            |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| OBJT       |     2 |    38 |     2   (0)| 00:00:01 |
    |* 3 | INDEX RANGE SCAN | IDX_OBJ_ID | 2 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id): ---------------------------------------------------
     3 - access("OBJECT_ID"=:D)
    已选择 20 行。

可以没有，即使是一半数量的0，还是走索引操作，可见这个时候的绑定变量窥探功能关闭了，Oracle把这个0当成是带入参数了并且采用所认为的数据分布均衡的方式来估算的。

*  总结：

绑定变量窥探功能要在有直方图的列分析里面才能起到第一次的窥探作用，但是如果你是倾斜列，也会导致执行计划走错，关闭了绑定变量窥探功能，会导致连第一次窥探都没有，后续所有的sql依然会软解析。