前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
返回腾讯云官网
社区首页 >专栏 >【DB笔试面试582】在Oracle中，什么是绑定变量窥探（上）？

【DB笔试面试582】在Oracle中，什么是绑定变量窥探（上）？

作者头像
AiDBA宝典
发布于 2019-09-29 07:52:22
发布于 2019-09-29 07:52:22
1.6K00
代码可运行
举报
文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏
运行总次数：0
代码可运行
关联问题
换一批

♣

题目部分

在Oracle中，什么是绑定变量窥探（上）？

♣

答案部分

目标SQL若不使用绑定变量，则当具体输入值一旦发生了变化，目标SQL的SQL文本就会随之发生变化，这样Oracle就能很容易地计算出对应Selectivity和Cardinality的值，进而据此来选择执行计划。但对于使用了绑定变量的目标SQL而言，情况就完全不一样了，因为现在无论对应绑定变量的具体输入值是什么，目标SQL的SQL文本都是一模一样的。对于使用了绑定变量的目标SQL而言，Oracle可以选择如下两种方法来决定其执行计划：

l 使用绑定变量窥探（Bind Peeking）。

l 如果不使用绑定变量窥探，那么对于那些可选择率可能会随着具体输入值的不同而不同的谓词条件使用默认的可选择率（例如5%）

绑定变量窥探（Bind Peeking）是在Oracle 9i中引入的，是否启用绑定变量窥探受隐含参数“_OPTIM_PEEK_USER_BINDS”的控制，该参数的默认值是TRUE，表示在Oracle 9i及其后续的版本中，默认启用绑定变量窥探。

代码语言：javascript
代码运行次数：0
运行
AI代码解释
复制
SYS@orclasm > set pagesize 9999
SYS@orclasm > set line 9999
SYS@orclasm > col NAME format a40
SYS@orclasm > col KSPPDESC format a50
SYS@orclasm > col KSPPSTVL format a20
SYS@orclasm > SELECT a.INDX,
  2         a.KSPPINM NAME,
  3         a.KSPPDESC,
  4         b.KSPPSTVL 
  5  FROM   x$ksppi  a,
  6         x$ksppcv b
  7  WHERE  a.INDX = b.INDX
  8  and lower(a.KSPPINM) like  lower('%&parameter%');
Enter value for parameter: _OPTIM_PEEK_USER_BINDS
old   8: and lower(a.KSPPINM) like  lower('%&parameter%')
new   8: and lower(a.KSPPINM) like  lower('%_OPTIM_PEEK_USER_BINDS%')

      INDX NAME                                     KSPPDESC                                           KSPPSTVL
---------- ---------------------------------------- -------------------------------------------------- --------------------
      2050 _optim_peek_user_binds                   enable peeking of user binds                       TRUE

绑定变量窥探的优缺点如下所示：

①　优点：当绑定变量窥探被启用后，每当Oracle以硬解析的方式解析使用了绑定变量的目标SQL时，Oracle都会实际窥探（Peeking）一下对应绑定变量的具体输入值，并以这些具体输入值为标准，来决定这些使用了绑定变量的目标SQL的WHERE条件的Selectivity和Cardinality的值，并据此来选择该SQL的执行计划。需要注意的是，这里这个“窥探（Peeking）”的动作只有在硬解析的时候才会执行，当使用了绑定变量的目标SQL再次执行时（此时对应的是软解析/软软解析），即便此时对应绑定变量的具体输入值和之前硬解析时对应的值不同，Oracle也会沿用之前硬解析时所产生的解析树和执行计划，而不再重复执行上述“窥探”的动作。因为有了绑定变量窥探，所以，Oracle在计算目标SQL的WHERE条件的Selectivity和Cardinality的值时，就可以避免使用默认的可选择率，这样就有更大的可能性能得到该SQL准确的执行计划。

②　缺点：对于那些执行计划可能会随着对应绑定变量具体输入值的不同而变化的目标SQL而言一旦启用了绑定变量窥探，其执行计划就会被固定下来，至于这个固定下来的执行计划到底是什么，则完全倚赖于该SQL在硬解析时传入的对应绑定变量的具体值。这意味着一旦启用了绑定变量窥探，目标SQL在后续执行时就只会沿用之前硬解析所产生的解析树和执行计划，即使当时的执行计划和解析树并不适合于新传入的值。

关于绑定变量窥探需要注意以下几点：

（1）在Oracle llg中引入自适应游标共享后，绑定变量窥探这种不管后续传入的绑定变量的具体输入值是什么而一直沿用之前硬解析时所产生的解析树和执行计划的缺点才有所缓解。

（2）在不改变SQL语句文本的情况下，让SQL语句重新进行硬解析的方法有：①对SQL语句涉及到的对象执行DDL操作（例如COMMENT语句）。②执行DBMS_SHARED_POOL.PURGE来删除共享池中的游标。③在重新收集统计信息时指定NO_INVALIDATE=>FALSE选项。

绑定变量窥探这种不管后续传入的绑定变量的具体输入值是什么而一直沿用之前硬解析时所产生的解析树和执行计划的特性一直饱受诟病（这种状况一直到Oracle llg中引入自适应游标共享后才有所缓解），因为绑定变量窥探可能使CBO在某些情况下（对应绑定变量的某些具体输入值）所选择的执行计划并不是目标SQL在当前情形下的最优执行计划，而且它可能会带来目标SQL执行计划的突然改变，进而直接影响应用系统的性能。

绑定变量窥探的副作用在于，一旦启用（默认情况下绑定变量窥探就己经被启用），使用了绑定变量的目标SQL就只会沿用之前硬解析时所产生的解析树和执行计划，即使这种沿用完全不适合当前的情形，即根据第一次传入的值然后固化执行计划。

下面给出绑定变量窥探的示例：

数据库版本为11.2.0.3，做如下的准备工作：

代码语言：javascript
代码运行次数：0
运行
AI代码解释
复制
CREATE TABLE T_BP_20170609_LHR AS SELECT * FROM DBA_OBJECTS;
CREATE INDEX IDX_OBJID_LHR ON  T_BP_20170609_LHR(OBJECT_ID);
SELECT COUNT(1) FROM T_BP_20170609_LHR;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_BP_20170609_LHR',ESTIMATE_PERCENT => 100,CASCADE => TRUE,METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',NO_INVALIDATE => FALSE);--不收集直方图
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102;  --3  5q51c7s4z0dp9
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000;   --59539  gpbkt45132r8x
LHR@orclasm > COL SQL_TEXT FORMAT A100
LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT FROM V$sqlarea A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(1) FROM T_BP_20170609_LHR T%';

SQL_TEXT                                                                                             SQL_ID        VERSION_COUNT
---------------------------------------------------------------------------------------------------- ------------- -------------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102                       5q51c7s4z0dp9             1
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000                     gpbkt45132r8x             1

--下面查看其执行计划：
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5q51c7s4z0dp9',0,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------
SQL_ID  5q51c7s4z0dp9, child number 0
-------------------------------------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102

Plan hash value: 1089369592

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE   |               |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_OBJID_LHR |     3 |    15 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T"@"SEL$1" ("T_BP_20170609_LHR"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."OBJECT_ID">=100 AND "T"."OBJECT_ID"<=102)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]


45 rows selected.

LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gpbkt45132r8x',0,'advanced'));

PLAN_TABLE_OUTPUT
---------------------------------------------
SQL_ID  gpbkt45132r8x, child number 0
-------------------------------------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100AND 60000

Plan hash value: 1768048749

---------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |       |       |    50 (100)|          |
|   1 |  SORT AGGREGATE       |               |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_OBJID_LHR | 28807 |   140K|    50   (2)| 00:00:01 |
---------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T_BP_20170609_LHR"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("T"."OBJECT_ID"<=60000 AND "T"."OBJECT_ID">=100))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

--可见，SQL1选择的是索引范围扫描，SQL2选择的是索引快速全扫描，下面使用绑定变量的形式，定义两个绑定变量X和Y，并赋值100和102：
LHR@orclasm > VAR X NUMBER;
LHR@orclasm > VAR Y NUMBER;
LHR@orclasm > EXEC :X :=100;

PL/SQL procedure successfully completed.

LHR@orclasm > EXEC :Y :=102;

PL/SQL procedure successfully completed.

LHR@orclasm > SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y;

  COUNT(1)
----------
         3
LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT FROM V$sqlarea A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(1) FROM T_BP_20170609_LHR T%';

SQL_TEXT                                                                                             SQL_ID        VERSION_COUNT
---------------------------------------------------------------------------------------------------- ------------- -------------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102                       5q51c7s4z0dp9             1
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y                         gya9jjznchps5             1
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000                     gpbkt45132r8x             1

LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gya9jjznchps5',0,'advanced'));

PLAN_TABLE_OUTPUT
-------------------------------------------------
SQL_ID  gya9jjznchps5, child number 0
-------------------------------------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y

Plan hash value: 196260839

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |               |     1 |     5 |            |          |
|*  2 |   FILTER           |               |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| IDX_OBJID_LHR |     3 |    15 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T"@"SEL$1" ("T_BP_20170609_LHR"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :X (NUMBER): 100
   2 - :Y (NUMBER): 102

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:X<=:Y)
   3 - access("T"."OBJECT_ID">=:X AND "T"."OBJECT_ID"<=:Y)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

从上述显示内容可以看出，Oracle此时选择的执行计划是走对索引IDX_OBJID_LHR的索引范围扫描，而且Oracle评估出来执行这个索引范围扫描所返回结果集的Cardinality的值为3。注意到“Peeked Binds”部分的内容为“1 - :X (NUMBER): 100”和“2 - :Y (NUMBER): 102”，这说明Oracle在硬解析上述SQL的过程中确实使用了绑定变量窺探，且做“窥探”这个动作时看到的绑定变量X和Y的具体输入值分别为100和102。

现在保持X的值不变，将Y的值修改为60000，如下所示：

代码语言：javascript
代码运行次数：0
运行
AI代码解释
复制
LHR@orclasm > EXEC :Y :=60000;

PL/SQL procedure successfully completed.

LHR@orclasm > SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y;

  COUNT(1)
----------
     59539

LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT,A.EXECUTIONS FROM V$SQLAREA A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(1) FROM T_BP_20170609_LHR T%';

SQL_TEXT                                                                                             SQL_ID        VERSION_COUNT EXECUTIONS
---------------------------------------------------------------------------------------------------- ------------- ------------- ----------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102                       5q51c7s4z0dp9             1          1
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y                         gya9jjznchps5             1          2
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000                     gpbkt45132r8x             1          1

从查询结果可以看到上述SQL对应的列VERSION_COUNT的值为1，列EXECUTIONS的值为2，这说明Oracle在第二次执行该SQL时用的是软解析。此时SQL的执行计划为：

代码语言：javascript
代码运行次数：0
运行
AI代码解释
复制
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gya9jjznchps5',0,'advanced'));

PLAN_TABLE_OUTPUT
-------------------------------------------------
SQL_ID  gya9jjznchps5, child number 0
-------------------------------------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X
AND :Y

Plan hash value: 196260839

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |               |     1 |     5 |            |          |
|*  2 |   FILTER           |               |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| IDX_OBJID_LHR |     3 |    15 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX(@"SEL$1" "T"@"SEL$1" ("T_BP_20170609_LHR"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :X (NUMBER): 100
   2 - :Y (NUMBER): 102

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:X<=:Y)
   3 - access("T"."OBJECT_ID">=:X AND "T"."OBJECT_ID"<=:Y)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]

从上述显示内容可以看出，Oracle此时依然选择的执行计划是走对索引IDX_OBJID_LHR的索引范围扫描，而且“Peeked Binds”部分的内容依然为“1 - :X (NUMBER): 100”和“2 - :Y (NUMBER): 102”，这说明Oracle在第2次执行使用绑定变量的SQL时不再重复做“窥探”动作。

此时若想Oracle进行“窥探”动作，则只能让以前的共享游标失效，重复执行一次硬解析即可。让Oracle再次执行目标SQL时使用硬解析的方法有很多，其中很常见的一种方法是对目标SQL中所涉及的表执行DDL操作。因为一旦对某个表执行了DDL操作，库缓存中所有在SQL文本中包含了这个表的Shared Cursor都会被Oracle标记为失效（INVALID），这意味着这些Shared Cursor中存储的解析树和执行计划将不再能被重用，所以当Oracle再次执行与这个表相关的SQL时就会使用硬解析。

所以，现在只需要对表T_BP_20170609_LHR执行一个DDL操作，就可以让Oracle再次执行上述SQL时使用硬解析了。DDL操作有很多种，通常选择添加注释的COMMENT语句，因为使用COMMENT添加注释也是DDL操作，但同时它的杀伤力和对生产环境的影响又微乎其微。

代码语言：javascript
代码运行次数：0
运行
AI代码解释
复制
LHR@orclasm > COMMENT ON TABLE T_BP_20170609_LHR IS 'TEST BIND PEEKING';

Comment created.

LHR@orclasm > SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y;

  COUNT(1)
----------
     59539

LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT,A.EXECUTIONS FROM V$SQLAREA A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(1) FROM T_BP_20170609_LHR T%';

SQL_TEXT                                                                                             SQL_ID        VERSION_COUNT EXECUTIONS
---------------------------------------------------------------------------------------------------- ------------- ------------- ----------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102                       5q51c7s4z0dp9             1          1
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y                         gya9jjznchps5             1          1
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000                     gpbkt45132r8x             1          1

从查询结果可以看到上述SQL对应的列VERSION_COUNT的值为1，列EXECUTIONS的值由之前的2变为了现在的1，这说明Oracle在第三次执行上述SQL时用的是硬解析（EXECUTIONS的值为1，是因为Oracle在这里重新生成了一对Parent Cursor和Child Cursor，原先EXECUTIONS的值为2所对应的Shared Cursor己经被Oracle标记为INVALID，相当于被废弃了）。

代码语言：javascript
代码运行次数：0
运行
AI代码解释
复制
LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gya9jjznchps5',0,'advanced')); 

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID  gya9jjznchps5, child number 0
-------------------------------------
SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y

Plan hash value: 2066501558

----------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |               |       |       |    50 (100)|          |
|   1 |  SORT AGGREGATE        |               |     1 |     5 |            |          |
|*  2 |   FILTER               |               |       |       |            |          |
|*  3 |    INDEX FAST FULL SCAN| IDX_OBJID_LHR | 28807 |   140K|    50   (2)| 00:00:01 |
----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T_BP_20170609_LHR"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Peeked Binds (identified by position):
--------------------------------------

   1 - :X (NUMBER): 100
   2 - :Y (NUMBER): 60000

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:X<=:Y)
   3 - filter(("T"."OBJECT_ID"<=:Y AND "T"."OBJECT_ID">=:X))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]


53 rows selected.

LHR@orclasm >

从上述显示内容可以看出，Oracle此时选择的执行计划己经从之前的走对索引IDX_OBJID_LHR的索引范围扫描变为索引快速全扫描，而且Oracle评估出来执行这个索引快速全扫描所返回结果集的Cardinality的值为28807。注意到“Peeked Binds”部分的内容为“1 - :X (NUMBER): 100”和“2 - :Y (NUMBER): 60000”，这说明Oracle在执行上述SQL的过程中确实又一次使用了绑定变量窥探，且做“窥探”这个动作时看到的绑定变量和X和Y的具体输入值分别为100和60000。

在这里，Oracle评估出来对索引IDX_OBJID_LHR执行索引范围扫描所返回结果集的Cardinality的值为3，对执行索引快速全扫描所返回结果集的Cardinality的值为28807。下面来解释一下这两个值是如何计算出来的：

参考下一篇。

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。

本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2019-06-23，如有侵权请联系 cloudcommunity@tencent.com 删除
sql
oracle
编程算法
css

本文分享自 DB宝 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ，欢迎热爱写作的你一起参与！

sql
oracle
编程算法
css
评论
登录后参与评论
暂无评论
登录 后参与评论
推荐阅读
编辑精选文章
换一批
万字详解高可用架构设计
1046
Go 开发者必备：Protocol Buffers 入门指南
467
10分钟带你彻底搞懂分布式链路跟踪
207
多租户的 4 种常用方案
394
亿级月活的社交 APP，陌陌如何做到 3 分钟定位故障？
363
60页PPT全解：DeepSeek系列论文技术要点整理
778
【DB笔试面试582】在Oracle中，什么是绑定变量窥探（下）？
oracle编程算法sql
若启用了绑定变量窥探且WHERE条件为“目标列 BETWEEN X AND Y”的selectivity计算公式为：
AiDBA宝典
2019/09/29
1.2K0
【DB笔试面试586】在Oracle中，什么是自适应游标共享（1）？
sqlcssoracle编程算法
绑定变量窥探的副作用就在于，使用了绑定变量的目标SQL只会沿用之前硬解析时所产生的解析树和执行计划，即使这种沿用完全不适合于当前的情形。在Oracle 10g及其后续的版本中，Oracle会自动收集直方图统计信息，这意味着与之前的版本相比，在Oracle 10g及其后续的版本中Oracle有更大的概率会知道目标列实际数据的分布情况，也就是说绑定变量窥探的副作用将会更加明显。当Oracle执行绑定变量窥探操作时绑定变量所对应的输入值是否具有代表性就至关重要了（这里“代表性”是指该输入值所对应的执行计划和该SQL在大多数情况下的执行计划相同），因为这会直接决定此目标SQL在硬解析时所选择的执行计划，进而决定后续以软解析/软软解析重复执行时所沿用的执行计划。
AiDBA宝典
2019/09/29
7080
【DB笔试面试586】在Oracle中，什么是自适应游标共享（2）？
csssqloracle编程算法存储
另外，在目标列有Frequency类型直方图的前提条件下，如果对目标列施加等值查询条件，且该查询条件的输入值等于该列的某个实际值时，则该谓词条件的可选择率的计算公式为如下所示：
AiDBA宝典
2019/09/29
5410
【DB笔试面试586】在Oracle中，什么是自适应游标共享（3）？
sqlcssoracle编程算法
从如下查询结果可以看到目标SQL对应的列VERSION_COUNT的值从之前的5变为了现在的6，列EXECUTIONS的值为7，说明Oracle在第7次执行目标SQL时依然用的是硬解析。从查询结果可以看到，Oracle此时新生成了一个CHILD_NUMBER为5的Child Cursor，并且把存储相同执行计划的CHILD_NUMBER为4的原有Child Cursor标记为非共享。
AiDBA宝典
2019/09/29
3610
【DB笔试面试598】在Oracle中，如何得到真实的执行计划？
sqloracle编程算法css
在Oracle数据库中判断得到的执行计划是否准确，就是看目标SQL是否被真正执行过，真正执行过的SQL所对应的执行计划就是准确的，反之则有可能不准，因此，通过10046事件及如下的几种方式得到的执行计划是最准确的，而从其它方式获取到的执行计划都有可能不准确。
AiDBA宝典
2019/09/29
7190
【DB笔试面试585】在Oracle中，什么是常规游标共享？
oracle编程算法csssql
游标共享（Cursor Sharing）是指共享游标（Shared Cursor）之间的共享，游标共享可以实现重用存储在子游标（Child Cursor）中的解析树和执行计划而不用从头开始做硬解析，从而提高系统性能。特别对于同一类型的目标SQL更应该实现游标共享，而使用绑定变量就可以实现游标共享。
AiDBA宝典
2019/09/29
8970
【DB笔试面试642】在Oracle中，什么是基数反馈（Cardinality Feedback）？
oraclesql
基数反馈（Cardinality Feedback，CFB）是Oracle 11gR2出现的一个新特性，它的出现是为了帮助Oracle优化器依据更精准的基数生成更加优秀的执行计划。基数的评估准确与否，对于优化器异常重要，直接影响到后续的JOIN COST等重要的成本计算评估。若评估不当则会造成CBO选择不当的执行计划。此技术对于仅执行一次的SQL无效，在SQL第一次执行时，记录存储实际的基数和评估的基数之间的差异，如果差异较大，在第二次执行时，优化器会依据实际的基数重新决策生成执行计划，但是需要注意的是，当使用更准确的基数重新生成执行计划时，生成的执行计划与第一次时使用的执行计划完全有可能是相同的。这个技术的出现是由于优化器在一些情况下不能很好的去计算基数的数值，比如：统计信息缺失或陈旧、多谓词、直方图缺失等等。
AiDBA宝典
2019/09/29
7840
【DB笔试面试570】在Oracle中，SQL优化在写法上有哪些常用的方法？
sqloracle云推荐引擎
一般在书写SQL时需要注意哪些问题，如何书写可以提高查询的效率呢？可以从以下几个方面去考虑：
AiDBA宝典
2019/09/29
3.6K0
【DB笔试面试583】在Oracle中，什么是绑定变量分级？
sql编程算法oraclecss
绑定变量分级（Bind Graduation）是指Oracle在PL/SQL代码中会根据文本型绑定变量的定义长度而将这些文本型绑定变量分为四个等级，不同等级分配的内存大小不同，如下表所示：
AiDBA宝典
2019/09/29
7320
【DB笔试面试586】在Oracle中，什么是自适应游标共享（4）？
sqlcssoracle编程算法
从上述计算结果可以看出，现在计算出的可选择率范围为[0.014172，0.017322]，在CHILD_NUMBER为5的原有Child Cursor对应的可选择率范围[0.000023，0.052317]之内，所以刚才Oracle在执行上述SQL时（即第8次执行目标SQL）用的就是软解析/软软解析，并且此时重用的就是CHILD_NUMBER为5的Child Cursor中存储的解析树和执行计划。
AiDBA宝典
2019/09/29
5740
Oracle面对“数据倾斜列使用绑定变量”场景的解决方案
sqlcss编程算法oraclelinux
    我们知道，Oracle在传统的OLTP(在线事务处理)类系统中，强烈推荐使用绑定变量，这样可以有效的减少硬解析从而增加系统的并发处理能力。甚至在有些老旧系统，由于在开始开发阶段缺乏认识没有使用到绑定变量，后期并发量增长且无法改造程序时，运维DBA还会不得已去设置cursor_sharing=force来强制使用系统的绑定变量（这是一个万不得已的方案，并不是最佳实践）。
Alfred Zhao
2019/08/27
1.8K0
Oracle 绑定变量窥探
oraclesql数据库
    Bind Peeking是Oracle 9i中引入的新特性，一直持续到Oracle 10g R2。它的作用就是在SQL语句硬分析的时候，查看一下当前SQL谓词的值 ，以便生成最佳的执行计划。而在oracle 9i之前的版本中，Oracle 只根据统计信息来做出执行计划。
Leshami
2018/08/14
1.7K0
【DB笔试面试613】在Oracle中，和子查询相关的查询转换有哪些？
oraclesql
和NOT EXISTS类似，也选择了哈希连接，只不过是HASH JOIN ANTI NA。这里的NA，实际表示Null-Aware的意思，在11g及以后的版本中，Oracle增加了对空值敏感的反关联的支持。
AiDBA宝典
2019/09/29
4.7K0
【DB笔试面试584】在Oracle中，如何得到已执行的目标SQL中的绑定变量的值？
oracle编程算法sqlapt-get
当Oracle解析和执行含有绑定变量的目标SQL时，如果满足如下两个条件之一，那么该SQL中的绑定变量的具体输入值就会被Oracle捕获：
AiDBA宝典
2019/09/29
3.1K0
【DB笔试面试677】在Oracle中，对于一个NUMBER(1)的列，若WHERE条件是大于3和大于等于4，这二者是否等价？
oraclesql
在Oracle中，对于一个NUMBER(1)的列，如果查询中的WHERE条件分别是大于3和大于等于4，那么这二者是否等价？
AiDBA宝典
2019/11/05
2.4K0
【DB笔试面试677】在Oracle中，对于一个NUMBER(1)的列，若WHERE条件是大于3和大于等于4，这二者是否等价？
【DB笔试面试517】在Oracle中，什么是临时表？它有哪些分类？有关临时表需要注意什么？
oracle数据库sql
目前所有使用Oracle作为数据库支撑平台的应用，大部分是数据量比较庞大的系统，即表的数据量级一般情况下都是在百万级以上。当然，在Oracle中创建分区是一种不错的选择，但是当发现应用有多张表关联的时候，并且这些表大部分都比较庞大，而关联的时候发现其中的某一张或者某几张表关联之后得到的结果集非常小，并且查询得到这个结果集的速度非常快，那么这个时候考虑在Oracle中创建“临时表”。
AiDBA宝典
2019/09/29
1.3K0
【DB笔试面试611】在Oracle中，什么是结果集缓存？
oraclesql数据库缓存
结果集缓存（Result Cache）是Oracle 11g的新特性，用于存储经常使用的SQL语句和函数的查询结果。当相同语句再次执行的时候，Oracle就不用再次重复执行（包括扫描索引、回表、计算、逻辑读、物理读等操作），而是直接访问内存得到结果。结果集缓存可以将SQL语句查询的结果缓存在内存（SGA的Shared Pool）中，从而显著地改进需要多次执行和查询相同结果的SQL语句的性能。
AiDBA宝典
2019/09/29
2.1K0
【DB笔试面试581】在Oracle中，绑定变量是什么？绑定变量有什么优缺点？
编程算法压力测试oraclesql数据库
通常在高并发的OLTP系统中，可能会出现这样的现象，单个SQL的写法、执行计划、性能都是没问题的，但整个系统的性能就是很差，这表现在当系统并发的数量增加时，整个系统负载很高，CPU占用率接近100%。其实，这种系统性能随着并发量的递增而显著降低的现象，往往是因为这些系统没有使用绑定变量而产生了大量的硬解析所致。因为同一条SQL语句仅仅由于谓词部分变量的不同而在执行的时候就需要重新进行一次硬解析，造成SQL执行计划不能共享，这极大地耗费了系统时间和系统CPU资源。那么怎样才能降低OLTP应用系统的硬解析的数量呢？答案就是使用绑定变量。高并发的OLTP系统若没有使用绑定变量则会导致硬解析很大，这在AWR中的Load Profile部分可以很容易的看出来。
AiDBA宝典
2019/09/29
2.7K0
【DB笔试面试581】在Oracle中，绑定变量是什么？绑定变量有什么优缺点？
【DB笔试面试593】在Oracle中，表的访问方式有哪几种？
oraclesql
访问表的方式也叫优化器访问路径，主要有3种访问路径：全表扫描（FULL TABLE SCAN，FTS）、索引扫描（INDEX SCAN）和ROWID访问。
AiDBA宝典
2019/09/29
1.2K0
【DB笔试面试593】在Oracle中，表的访问方式有哪几种？
【DB笔试面试258】在Oracle中，执行计划里的access和filter有什么区别（上）？
accessnesthttp编程算法
一般而言，access表示这个谓词条件的值将会影响数据的访问路径（表还是索引）；filter表示谓词条件的值不会影响数据的访问路劲，只起到过滤的作用。NOT IN或MIN函数等容易产生filter操作。
AiDBA宝典
2019/09/30
1.3K0
【DB笔试面试258】在Oracle中，执行计划里的access和filter有什么区别（上）？
推荐阅读
编辑精选文章
万字详解高可用架构设计Go 开发者必备：Protocol Buffers 入门指南10分钟带你彻底搞懂分布式链路跟踪
相关讨论
对于 看着电脑百度出来的面试题 去面试技术 的技术面试官 大家怎么看？面试过程中，面试官是如何考察Rust工程师的水平？Python编码如何解决面试问题？
相关课程
AI绘画-StableDiffusion图像生成前端腾讯云向量数据库快速上手训练营
【DB笔试面试582】在Oracle中，什么是绑定变量窥探（下）？
1.2K0
【DB笔试面试586】在Oracle中，什么是自适应游标共享（1）？
7080
【DB笔试面试586】在Oracle中，什么是自适应游标共享（2）？
5410
【DB笔试面试586】在Oracle中，什么是自适应游标共享（3）？
3610
【DB笔试面试598】在Oracle中，如何得到真实的执行计划？
7190
【DB笔试面试585】在Oracle中，什么是常规游标共享？
8970
【DB笔试面试642】在Oracle中，什么是基数反馈（Cardinality Feedback）？
7840
【DB笔试面试570】在Oracle中，SQL优化在写法上有哪些常用的方法？
3.6K0
【DB笔试面试583】在Oracle中，什么是绑定变量分级？
7320
【DB笔试面试586】在Oracle中，什么是自适应游标共享（4）？
5740
Oracle面对“数据倾斜列使用绑定变量”场景的解决方案
1.8K0
Oracle 绑定变量窥探
1.7K0
【DB笔试面试613】在Oracle中，和子查询相关的查询转换有哪些？
4.7K0
【DB笔试面试584】在Oracle中，如何得到已执行的目标SQL中的绑定变量的值？
3.1K0
【DB笔试面试677】在Oracle中，对于一个NUMBER(1)的列，若WHERE条件是大于3和大于等于4，这二者是否等价？
2.4K0
【DB笔试面试517】在Oracle中，什么是临时表？它有哪些分类？有关临时表需要注意什么？
1.3K0
【DB笔试面试611】在Oracle中，什么是结果集缓存？
2.1K0
【DB笔试面试581】在Oracle中，绑定变量是什么？绑定变量有什么优缺点？
2.7K0
【DB笔试面试593】在Oracle中，表的访问方式有哪几种？
1.2K0
【DB笔试面试258】在Oracle中，执行计划里的access和filter有什么区别（上）？
1.3K0
相关推荐
【DB笔试面试582】在Oracle中，什么是绑定变量窥探（下）？
更多 >
AiDBA宝典0
LV.2
中软DBA
文章
927
获赞
2.1K
专栏
1
作者相关精选
换一批
加入讨论
的问答专区 >
不惑
1产品KOL擅长5个领域
相关课程
一站式学习中心 >
AI绘画-StableDiffusion图像生成
1668人在学
大模型图像创作引擎
高性能应用服务
前端
1242人在学
前端
腾讯云向量数据库快速上手训练营
1011人在学
向量数据库
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2025 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论
2
本文部分代码块支持一键运行，欢迎体验
本文部分代码块支持一键运行，欢迎体验