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

最新推荐文章于 2023-05-05 16:55:10 发布

Silencecxq

最新推荐文章于 2023-05-05 16:55:10 发布

阅读量606

点赞数

分类专栏： Oracle 文章标签： oracle

本文链接：https://blog.csdn.net/chen1511xiqiang/article/details/70919170

版权

Oracle 专栏收录该内容

9 篇文章 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 行。