­

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

  • 2019 年 10 月 10 日
  • 筆記

若启用了绑定变量窥探且WHERE条件为“目标列 BETWEEN X AND Y”的selectivity计算公式为：

selectivity=((Y-X)/(HIGH_VALUE-LOW_VALUE)+2/NUM_DISTINCT)*Null_Adjust  Null_Adjust=(NUM_ROWS-NUM_NULLS)/NUM_ROWS    --总公式：cardinality=((Y-X)/(HIGH_VALUE-LOW_VALUE)+2/NUM_DISTINCT)*(NUM_ROWS-NUM_NULLS)    --查询相关的值：  LHR@orclasm > SELECT D.NUM_ROWS FROM DBA_TABLES D WHERE D.TABLE_NAME='T_BP_20170609_LHR';      NUM_ROWS  ----------       78170    LHR@orclasm > col LOW_VALUE format a6  LHR@orclasm > col high_VALUE format a10  LHR@orclasm > SELECT D.LOW_VALUE,D.HIGH_VALUE,UTL_RAW.CAST_TO_NUMBER(D.LOW_VALUE) LOW_VALUE1,UTL_RAW.CAST_TO_NUMBER(D.HIGH_VALUE) HIGH_VALUE1,D.NUM_DISTINCT,D.NUM_NULLS,D.HISTOGRAM FROM DBA_TAB_COL_STATISTICS D WHERE D.TABLE_NAME='T_BP_20170609_LHR' AND D.COLUMN_NAME='OBJECT_ID';    LOW_VA HIGH_VALUE LOW_VALUE1 HIGH_VALUE1 NUM_DISTINCT  NUM_NULLS HISTOGRAM  ------ ---------- ---------- ----------- ------------ ---------- ---------------  C103   C3111A1F            2      162530        78158         12 NONE    --当X和Y的值分别为100和102时，以及X和Y的值分别为100和60000时，基数Cardinality的值为：  LHR@orclasm > SELECT ROUND(((102-100)/(162530-2)+2/78158)*(78170-12)),ROUND(((60000-100)/(162530-2)+2/78158)*(78170-12)) FROM DUAL;    ROUND(((102-100)/(162530-2)+2/78158)*(78170-12)) ROUND(((60000-100)/(162530-2)+2/78158)*(78170-12))  ------------------------------------------------ --------------------------------------------------                                                 3                                              28807    --可以看到结果和上述直接计划中的结果一致。  --下面禁用绑定变量窥探：  LHR@orclasm > ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS"=FALSE;    Session altered.    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,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             2          2  SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000                     gpbkt45132r8x             1          1      LHR@orclasm > SELECT A.PLAN_HASH_VALUE,A.CHILD_NUMBER,A.EXECUTIONS FROM V$SQL A WHERE A.SQL_ID='gya9jjznchps5';    PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS  --------------- ------------ ----------       2066501558            0          1        196260839            1          1    --可以看到目标SQL对应的列VERSION_COUNT和EXECUTIONS的值均由之前的1变为了现在的2，这说明Oracle在第四次执行该SQL时用的还是硬解析。当把绑定变量窥探关闭后再次执行目标SQL时所对应的解析树和执行计划应该存储在CHILD_NUMBER为1的Child Cursor中。CHILD_NUMBER为1的Child Cursor所对应的执行计划为如下所示：  LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gya9jjznchps5',1,'advanced'));    PLAN_TABLE_OUTPUT  -----------------------------------------------------------------------  SQL_ID  gya9jjznchps5, child number 1  -------------------------------------  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 |   195 |   975 |     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')        OPT_PARAM('_optim_peek_user_binds' 'false')        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 - 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评估出来执行这个索引范围扫描所返回结果集的Cardinality的值为195。注意，上述执行计划中的Outline Data部分有“OPT_PARAM('_optim_peek_user_binds' 'false')”，而且执行计划中并没有之前启用绑定窥探时出现的“Peeked Binds”部分的内容，这都说明此时Oracle确实己经禁用了绑定变量窥探。

使用DDL操作可以让Oracle再次执行目标SQL时使用硬解析，但这种方法的弊端在于其影响范围还是太广了，因为一旦对某个表执行了DDL操作，再次执行与这个表相关的所有SQL时就会全部使用硬解析。这是很不好的，特别是对于OLTP类型的应用系统而言，因为这可能会导致短时间内的硬解析数量剧增，进而影响系统的性能。

下面使用DBMS_SHARED_POOL.PURGE来删除共享池中的该游标：

LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT,A.EXECUTIONS,A.ADDRESS,A.HASH_VALUE 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 ADDRESS          HASH_VALUE  ---------------------------------------------------------------------------------------------------- ------------- ------------- ---------- ---------------- ----------  SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102                       5q51c7s4z0dp9             1          1 00000000A9490728  166737577  SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y                         gya9jjznchps5             2          2 00000000A937CD78 3905443589  SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000                     gpbkt45132r8x             1          1 00000000A948FCA8 1110531357    LHR@orclasm > EXEC SYS.DBMS_SHARED_POOL.PURGE('00000000A937CD78,3905443589','C');    PL/SQL procedure successfully completed.    LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT,A.EXECUTIONS,A.ADDRESS,A.HASH_VALUE 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 ADDRESS          HASH_VALUE  ---------------------------------------------------------------------------------------------------- ------------- ------------- ---------- ---------------- ----------  SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102                       5q51c7s4z0dp9             1          1 00000000A9490728  166737577  SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000                     gpbkt45132r8x             1          1 00000000A948FCA8 1110531357    --可以看到目标SQL的确被删除了，现在保持X的值不变，修改Y的的值为60000：  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             2          1  SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000                     gpbkt45132r8x             1          1    LHR@orclasm > SELECT A.PLAN_HASH_VALUE,A.CHILD_NUMBER,A.EXECUTIONS FROM V$SQL A WHERE A.SQL_ID='gya9jjznchps5';    PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS  --------------- ------------ ----------        196260839            0          1    --可以看到，EXECUTIONS的值为1说明Oracle在执行此SQL时确实是在用硬解析，但这里VERSION COUNT的值为什么会是2？应该是1才对。从V$SQL查询结果可以看到，上述目标SQL对应的Parent Cursor下确实只挂了一个Child Cursor，所以VERSION_COUNT的值应该是1而不是2（这里也许是Oracle的Bug，不再深究）。  --执行计划为：  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 |   195 |   975 |     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')        OPT_PARAM('_optim_peek_user_binds' 'false')        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 - 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]      48 rows selected.

从上述显示内容可以看出，Oracle此时选择的执行计划还是走对索引IDX_OBJID_LHR的索引范围扫描，而且Oracle评估出来执行这个索引范围扫描所返回结果集的Cardinality的值依然是195。这意味着当把绑定变量窥探关闭后，无论对绑定变量X和Y传入的值是多少，都不会影响Oracle对于目标SQL执行计划的选择。如果不使用绑定变量窥探，那么对于那些可选择率可能会随着具体输入值的变化而变化的谓词条件而言，Oracle会使用默认的可选择率（5％）。

下面来计算一下cardinality，

若禁用了绑定变量窥探且WHERE条件为“目标列 BETWEEN X AND Y”的selectivity和Cardinality的计算公式为如下所示：

Cardinality=NUM ROWS*Selectivity  Selectivity=0.05*0.05

注意，WHERE条件为“目标列 BETWEEN X AND Y"，相当于“目标列 >= X AND目标列 <= Y"。对于“目标列 >=X ”和“目标列 <= Y”而言，Oracle均会使用5%的可选择率，所以“目标列 >= X AND目标列 <= Y”的总的可选择率就是0.05*0.05=0.0025。

LHR@orclasm > SELECT ROUND(78170*0.0025) FROM DUAL;    ROUND(78170*0.0025)  -------------------                  195

可见和执行计划中的一致。

VirMach 便宜 VPS

QNews

QNews