若启用了绑定变量窥探且WHERE条件为“目标列 BETWEEN X AND Y”的selectivity计算公式为：
1selectivity=((Y-X)/(HIGH_VALUE-LOW_VALUE)+2/NUM_DISTINCT)*Null_Adjust
2Null_Adjust=(NUM_ROWS-NUM_NULLS)/NUM_ROWS
3
4--总公式：cardinality=((Y-X)/(HIGH_VALUE-LOW_VALUE)+2/NUM_DISTINCT)*(NUM_ROWS-NUM_NULLS)
5
6--查询相关的值：
7LHR@orclasm > SELECT D.NUM_ROWS FROM DBA_TABLES D WHERE D.TABLE_NAME='T_BP_20170609_LHR';
8
9 NUM_ROWS
10----------
11 78170
12
13LHR@orclasm > col LOW_VALUE format a6
14LHR@orclasm > col high_VALUE format a10
15LHR@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';
16
17LOW_VA HIGH_VALUE LOW_VALUE1 HIGH_VALUE1 NUM_DISTINCT NUM_NULLS HISTOGRAM
18------ ---------- ---------- ----------- ------------ ---------- ---------------
19C103 C3111A1F 2 162530 78158 12 NONE
20
21--当X和Y的值分别为100和102时，以及X和Y的值分别为100和60000时，基数Cardinality的值为：
22LHR@orclasm > SELECT ROUND(((102-100)/(162530-2)+2/78158)*(78170-12)),ROUND(((60000-100)/(162530-2)+2/78158)*(78170-12)) FROM DUAL;
23
24ROUND(((102-100)/(162530-2)+2/78158)*(78170-12)) ROUND(((60000-100)/(162530-2)+2/78158)*(78170-12))
25------------------------------------------------ --------------------------------------------------
26 3 28807
27
28--可以看到结果和上述直接计划中的结果一致。
29--下面禁用绑定变量窥探：
30LHR@orclasm > ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS"=FALSE;
31
32Session altered.
33
34LHR@orclasm > EXEC :Y :=102;
35
36PL/SQL procedure successfully completed.
37
38LHR@orclasm > SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y;
39
40 COUNT(1)
41----------
42 3
43
44LHR@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%';
45
46SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
47---------------------------------------------------------------------------------------------------- ------------- ------------- ----------
48SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102 5q51c7s4z0dp9 1 1
49SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y gya9jjznchps5 2 2
50SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000 gpbkt45132r8x 1 1
51
52
53LHR@orclasm > SELECT A.PLAN_HASH_VALUE,A.CHILD_NUMBER,A.EXECUTIONS FROM V$SQL A WHERE A.SQL_ID='gya9jjznchps5';
54
55PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS
56--------------- ------------ ----------
57 2066501558 0 1
58 196260839 1 1
59
60--可以看到目标SQL对应的列VERSION_COUNT和EXECUTIONS的值均由之前的1变为了现在的2，这说明Oracle在第四次执行该SQL时用的还是硬解析。当把绑定变量窥探关闭后再次执行目标SQL时所对应的解析树和执行计划应该存储在CHILD_NUMBER为1的Child Cursor中。CHILD_NUMBER为1的Child Cursor所对应的执行计划为如下所示：
61LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gya9jjznchps5',1,'advanced'));
62
63PLAN_TABLE_OUTPUT
64-----------------------------------------------------------------------
65SQL_ID gya9jjznchps5, child number 1
66-------------------------------------
67SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y
68
69Plan hash value: 196260839
70
71------------------------------------------------------------------------------------
72| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
73------------------------------------------------------------------------------------
74| 0 | SELECT STATEMENT | | | | 2 (100)| |
75| 1 | SORT AGGREGATE | | 1 | 5 | | |
76|* 2 | FILTER | | | | | |
77|* 3 | INDEX RANGE SCAN| IDX_OBJID_LHR | 195 | 975 | 2 (0)| 00:00:01 |
78------------------------------------------------------------------------------------
79
80Query Block Name / Object Alias (identified by operation id):
81-------------------------------------------------------------
82
83 1 - SEL$1
84 3 - SEL$1 / T@SEL$1
85
86Outline Data
87-------------
88
89 /*+
90 BEGIN_OUTLINE_DATA
91 IGNORE_OPTIM_EMBEDDED_HINTS
92 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
93 DB_VERSION('11.2.0.3')
94 OPT_PARAM('_optim_peek_user_binds' 'false')
95 ALL_ROWS
96 OUTLINE_LEAF(@"SEL$1")
97 INDEX(@"SEL$1" "T"@"SEL$1" ("T_BP_20170609_LHR"."OBJECT_ID"))
98 END_OUTLINE_DATA
99 */
100
101Predicate Information (identified by operation id):
102---------------------------------------------------
103
104 2 - filter(:X<=:Y)
105 3 - access("T"."OBJECT_ID">=:X AND "T"."OBJECT_ID"<=:Y)
106
107Column Projection Information (identified by operation id):
108-----------------------------------------------------------
109
110 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来删除共享池中的该游标：
1LHR@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%';
2
3SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ADDRESS HASH_VALUE
4---------------------------------------------------------------------------------------------------- ------------- ------------- ---------- ---------------- ----------
5SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102 5q51c7s4z0dp9 1 1 00000000A9490728 166737577
6SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y gya9jjznchps5 2 2 00000000A937CD78 3905443589
7SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000 gpbkt45132r8x 1 1 00000000A948FCA8 1110531357
8
9LHR@orclasm > EXEC SYS.DBMS_SHARED_POOL.PURGE('00000000A937CD78,3905443589','C');
10
11PL/SQL procedure successfully completed.
12
13LHR@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%';
14
15SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ADDRESS HASH_VALUE
16---------------------------------------------------------------------------------------------------- ------------- ------------- ---------- ---------------- ----------
17SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102 5q51c7s4z0dp9 1 1 00000000A9490728 166737577
18SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000 gpbkt45132r8x 1 1 00000000A948FCA8 1110531357
19
20--可以看到目标SQL的确被删除了，现在保持X的值不变，修改Y的的值为60000：
21LHR@orclasm > EXEC :Y :=60000;
22
23PL/SQL procedure successfully completed.
24
25LHR@orclasm > SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y;
26
27 COUNT(1)
28----------
29 59539
30
31LHR@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%';
32
33SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
34---------------------------------------------------------------------------------------------------- ------------- ------------- ----------
35SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 102 5q51c7s4z0dp9 1 1
36SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y gya9jjznchps5 2 1
37SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN 100 AND 60000 gpbkt45132r8x 1 1
38
39LHR@orclasm > SELECT A.PLAN_HASH_VALUE,A.CHILD_NUMBER,A.EXECUTIONS FROM V$SQL A WHERE A.SQL_ID='gya9jjznchps5';
40
41PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS
42--------------- ------------ ----------
43 196260839 0 1
44
45--可以看到，EXECUTIONS的值为1说明Oracle在执行此SQL时确实是在用硬解析，但这里VERSION COUNT的值为什么会是2？应该是1才对。从V$SQL查询结果可以看到，上述目标SQL对应的Parent Cursor下确实只挂了一个Child Cursor，所以VERSION_COUNT的值应该是1而不是2（这里也许是Oracle的Bug，不再深究）。
46--执行计划为：
47LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gya9jjznchps5',0,'advanced'));
48
49PLAN_TABLE_OUTPUT
50-------------------------------------------------------------------------
51SQL_ID gya9jjznchps5, child number 0
52-------------------------------------
53SELECT COUNT(1) FROM T_BP_20170609_LHR T WHERE T.OBJECT_ID BETWEEN :X AND :Y
54
55Plan hash value: 196260839
56
57------------------------------------------------------------------------------------
58| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
59------------------------------------------------------------------------------------
60| 0 | SELECT STATEMENT | | | | 2 (100)| |
61| 1 | SORT AGGREGATE | | 1 | 5 | | |
62|* 2 | FILTER | | | | | |
63|* 3 | INDEX RANGE SCAN| IDX_OBJID_LHR | 195 | 975 | 2 (0)| 00:00:01 |
64------------------------------------------------------------------------------------
65
66Query Block Name / Object Alias (identified by operation id):
67-------------------------------------------------------------
68
69 1 - SEL$1
70 3 - SEL$1 / T@SEL$1
71
72Outline Data
73-------------
74
75 /*+
76 BEGIN_OUTLINE_DATA
77 IGNORE_OPTIM_EMBEDDED_HINTS
78 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
79 DB_VERSION('11.2.0.3')
80 OPT_PARAM('_optim_peek_user_binds' 'false')
81 ALL_ROWS
82 OUTLINE_LEAF(@"SEL$1")
83 INDEX(@"SEL$1" "T"@"SEL$1" ("T_BP_20170609_LHR"."OBJECT_ID"))
84 END_OUTLINE_DATA
85 */
86
87Predicate Information (identified by operation id):
88---------------------------------------------------
89
90 2 - filter(:X<=:Y)
91 3 - access("T"."OBJECT_ID">=:X AND "T"."OBJECT_ID"<=:Y)
92
93Column Projection Information (identified by operation id):
94-----------------------------------------------------------
95
96 1 - (#keys=0) COUNT(*)[22]
97
98
9948 rows selected.
从上述显示内容可以看出，Oracle此时选择的执行计划还是走对索引IDX_OBJID_LHR的索引范围扫描，而且Oracle评估出来执行这个索引范围扫描所返回结果集的Cardinality的值依然是195。这意味着当把绑定变量窥探关闭后，无论对绑定变量X和Y传入的值是多少，都不会影响Oracle对于目标SQL执行计划的选择。如果不使用绑定变量窥探，那么对于那些可选择率可能会随着具体输入值的变化而变化的谓词条件而言，Oracle会使用默认的可选择率（5％）。
下面来计算一下cardinality，
若禁用了绑定变量窥探且WHERE条件为“目标列 BETWEEN X AND Y”的selectivity和Cardinality的计算公式为如下所示：
1Cardinality=NUM ROWS*Selectivity
2Selectivity=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。
1LHR@orclasm > SELECT ROUND(78170*0.0025) FROM DUAL;
2
3ROUND(78170*0.0025)
4-------------------
5 195
可见和执行计划中的一致。
最后修改时间：2020-01-10 19:48:34
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
