subject part
stay Oracle in , What is adaptive cursor sharing ？
Answer section
The side effect of bound variable snooping is , The target of the bound variable is used SQL Only the parse tree and execution plan generated by previous hard parsing will be used , Even if it’s totally inappropriate for the current situation . stay Oracle 10g And subsequent versions of ,Oracle Histogram statistics are automatically collected , This means that compared to the previous version , stay Oracle 10g And subsequent versions of Oracle There is a greater probability of knowing the distribution of the actual data in the target column , In other words, the side effects of binding variable snooping will be more obvious . When Oracle Whether the input value corresponding to the bound variable is representative or not is very important when performing the bound variable snooping operation （ here “ Representative ” It refers to the execution plan corresponding to the input value and the SQL In most cases, the execution plan is the same ）, Because it directly determines the goal SQL The execution plan selected in hard parsing , And then decided to follow up with soft analysis / Soft parsing the execution plan used in repeated execution .
In order to solve the problem caused by the above binding variable snooping ,Oracle stay l1g Adaptive cursor sharing is introduced in （Adaptive Cursor Sharing,ACS）. Adaptive cursor sharing allows targets that use bound variables to SQL With bound variable snooping enabled , It is no longer necessary to rigidly use only the parse tree and execution plan generated by previous hard parsing , It’s about making the goal SQL Between its possible multiple execution plans “ The adaptive ” Make a good choice . The core of adaptive cursor sharing is that it can “ The adaptive ” Choose the execution plan carefully , Thus, the side effect of binding variable snooping is avoided to a certain extent .Oracle Just before it thinks the goal SQL When the implementation plan of the project may change , Trigger this SQL Just do a hard analysis again . Because once the hard parse action is triggered ,Oracle It’s going to take the target SQL Let’s do it again , Among them is the investigation of the SQL Do another peek at bound variables . obviously , After another peek of bound variables, the corresponding execution plan is in the current situation CBO Think of the best execution plan , This implementation plan is likely to be related to SQL The execution plan generated by hard parsing is different . in other words , A simple timely trigger target SQL Another hard parsing will alleviate the side effect of bound variable snooping to a certain extent .
that Oracle When the above hard parsing action will be triggered ？ Or here “ Timely trigger ” What is the specific meaning of ？ in general ,Oracle According to the implementation goals SQL It’s the same as runtime Statistics （ For example, the cost of logical reading and CPU Time , The number of rows corresponding to the result set, etc ） The change of , And the binding rate of the current predicate , To comprehensively determine whether the target needs to be triggered SQL The hard analytic action of .
Let’s first introduce Oracle Some basic concepts related to adaptive cursor sharing in database .
|
Bind Sensitive |
Bind Aware |
|
|
step |
1 |
2 |
|
brief introduction |
The first thing adaptive cursor sharing does is called extended cursor sharing （Extended Cursor Sharing）, And the main thing extended cursor sharing does is to target SQL The corresponding Child Cursor Marked as Bind Sensitive.Bind Sensitive Refer to Oracle Think of a target with bound variables SQL The execution plan of may change with the input value of the passed in binding variable . For those marked as Bind Sensitive Of Child Cursor,Oracle Will carry out the SQL It’s the same as runtime Statistics are additionally stored in the SQL The corresponding Child Cursor in . |
The second thing adaptive cursor sharing has to do is target SQL The corresponding Child Cursor Marked as Bind Aware.Bind Aware Refer to Oracle A target with bound variables has been identified SQL The execution plan of will change with the input value of the passed in binding variable . |
|
The conditions of marking |
When satisfied with the following 3 When there are two conditions , The goal is SQL The corresponding Child Cursor Will be Oracle Marked as Bind Sensitive： ① Enabled bound variable snooping . ② The SQL Using bound variables （ Whether it’s time to SQL The built-in binding variable , Or the binding variable generated by the system after the common cursor sharing is turned on ）. ③ The SQL Unsafe predicate conditions are used （ For example, range query , Equivalent query with histogram statistical information on the target column ）. |
When the following two conditions are satisfied , The goal is SQL The corresponding Child Cursor Will be Oracle Marked as Bind Aware： ① The SQL The corresponding Child Cursor Has been marked as before Bind Sensitive. ② The SQL On the next two consecutive runs , The corresponding runtime And the statistics SQL Before hard parsing corresponding to runtime Statistical information is quite different . |
|
Ban |
The hidden parameter “_OPTIMIZER_EXTENDED_CURSOR_SHARING” and “_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL” The values of are set to NONE. It should be noted that , If the number of bound variables exceeds 14, Then SQL The corresponding child cursor will not be marked as Bind Sensitive. |
The hidden parameter “_OPTIMIZER_ADAPTIVE_CURSOR_SHARING” The value of the set FALSE. |
|
Field |
V$SQL.IS_BIND_SENSITIVE |
V$SQL.IS_BIND_AWARE |
The first thing adaptive cursor sharing does is called extended cursor sharing （Extended Cursor Sharing）, And the main thing extended cursor sharing does is to target SQL The corresponding Child Cursor Marked as Bind Sensitive.Bind Sensitive Refer to Oracle Think of a target with bound variables SQL The execution plan of may change with the input value of the passed in binding variable .
When the following three conditions are satisfied , The goal is SQL The corresponding Child Cursor Will be Oracle Marked as Bind Sensitive：
l Enabled bound variable snooping .
l The SQL Using bound variables （ Whether it’s time to SQL The built-in binding variable , Or the binding variable generated by the system after the common cursor sharing is turned on ）.
l The SQL Unsafe predicate conditions are used （ For example, range query , Equivalent query with histogram statistical information on the target column ）.
The second thing adaptive cursor sharing has to do is target SQL The corresponding Child Cursor Marked as Bind Aware.Bind Aware Refer to Oracle A target with bound variables has been identified SQL The execution plan of will change with the input value of the passed in binding variable .
When the following two conditions are satisfied , The goal is SQL The corresponding Child Cursor Will be Oracle Marked as Bind Aware：
l The SQL The corresponding Child Cursor Has been marked as before Bind Sensitive.
l The SQL On the next two consecutive runs , The corresponding runtime And the statistics SQL Before hard parsing corresponding to runtime Statistical information is quite different .
For adaptive cursor sharing ,V$SQL Column in IS_BIND_SENSITIVE、IS_BIND_AWARE and IS_SHAREABLE They are used to express Child Cursor Whether it is Bind Sensitive、Bind Aware And shared . here “ share ” The meaning of is stored in the Child Cursor Whether the parse tree and execution plan in can be reused , A non shared Child Cursor The execution plan and parse tree stored in can’t be reused , And it’s time to Child Cursor Will also be the first time from Shared Pool To be cleared out of the room .
Two important views related to adaptive cursor sharing are V$SQL_CS_STATISTICS and V$SQL_CS_SELECTIVITY：
l V$SQL_CS_STATISTICS Used to display the specified Child Cursor Stored in the runtime Statistics .
l V$SQL_CS_SELECTIVITY Used to display the specified 、 Has been marked as Bind Aware Of Child Cursor The range of the selectivity rate corresponding to the predicate conditions with bound variables stored in . When one is marked as Bind Aware Of Child Cursor The corresponding goal SQL When executed again ,Oracle It compares the selectivity of the predicate condition in which the value of the binding variable is currently passed in , And it’s time to SQL Before hard parsing, the predicate condition of the same name is V$SQL_CS_SELECTIVITY The range of the selectivity corresponding to , And then we can decide whether to use hard parsing or soft parsing ／ Soft analysis .
After introducing the above basic concepts , Now we can introduce the whole process of adaptive cursor sharing .Oracle The overall execution process of adaptive cursor sharing in database is as follows ：
（1） When the target SQL When first executed ,Oracle Can use hard parsing , meanwhile Oracle According to a series of conditions （ If the SQL Do you use binding variables , Parameters CURSOR_SHARING What’s the value of , Does the column of bound variable have histogram , The SQL Of WHERE The condition is equivalent query or range query, etc ） To determine whether the SQL The corresponding Child Cursor Marked as Bind Sensitive. For those marked as Bind Sensitive Of Child Cursor,Oracle Will carry out the SQL It’s the same as runtime Statistics are additionally stored in the SQL The corresponding Child Cursor in .
（2） When the target SQL The second time it was executed ,Oracle Can use soft parsing , And will reuse the SQL What happens when you first execute Child Cursor The parse tree and execution plan stored in .
（3） When the target SQL The third time it was executed , If it’s time to SQL The corresponding Child Cursor Has been marked as Bind Sensitive, meanwhile Oracle In the second and third execution of the SQL Recorded at runtime Statistics and the SQL What was recorded in the first hard parsing runtime Statistical information is quite different , Then the SQL Hard parsing is used on the third execution ,Oracle At this point, a new Child Cursor（ This new Child Cursor It will hang on the original Parent Cursor Next ）, also Oracle I’ll take this new Child Cursor Marked as Bind Aware.
（4） For those marked as Bind Aware Of Child Cursor The corresponding goal SQL, When it’s time to SQL When executed again ,Oracle According to the selectivity of the predicate condition corresponding to the current value of the binding variable , To decide whether to use hard parsing or soft parsing at this time / Soft analysis . The principle of judgment here is , If the optional rate of the predicate condition in which the current passed in binding variable value is located is in this SQL Before hard parsing, the predicate condition of the same name is V$SQL_CS_STATISTICS Within the range of selectivity recorded in , So at this time Oracle You’ll use soft parsing ／ Soft analysis , And reuse related Child Cursor The parse tree and execution plan stored in , On the contrary, it is hard parsing . If it’s hard parsing , And the execution plan generated by this hard parsing is the same as the original Child Cursor The execution plan stored in is the same , that Oracle In this case, a new one will be generated Child Cursor outside , It also stores the original data of the same execution plan Child Cursor Mark as unshared （ The original Child Cursor stay V$SQL The column corresponding to the record in IS_SHAREABLE The value of is also changed from Y Turn into N), I’m putting the original Child cursor Mark as not shared at the same time ,Oracle There will also be a new generation of Child Cursor Execute one Cursor The process of merger （ here Cursor Merger means Oracle Will merge and store the same execution plan of the original Child Cursor And the new Child Cursor）; If it’s soft parsing / Soft analysis , that Oracle Will reuse related Child Cursor The parse tree and execution plan stored in .
Adaptive cursor sharing alleviates the side effects of bound variable snooping to some extent , But it has the following defects ：
l It will increase the number of hard resolution in addition .
l Additionally, the number of child cursors of the same parent cursor will be increased , This will increase soft parsing / The workload of finding matching sub cursors in soft parsing .
l In order to store these, add additional cursor , Shared pool （Shared Pool） There will also be extra pressure on space . So when you go from Oracle 10g Upgrade to 11g when , It is recommended to increase the size of the shared pool appropriately .
l If the number of bound variables exceeds 14 individual , be ACS invalid .
If it’s open ACS Too many cursors result in , And then the space of the shared pool is tight or too much Mutex wait for , You can disable it in any of the following ways ACS：
l The hidden parameter “_OPTIMIZER_EXTENDED_CURSOR_SHARING” and “_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL” The values of are set to NONE, This is equivalent to turning off extensible cursor sharing . Once extensible cursor sharing is disabled , be-all Child Cursor Will no longer be marked as Bind Sensitive, Then nature cannot be marked as Bind Aware, In other words, adaptive cursor sharing is disabled .
l The hidden parameter “_OPTIMIZER_ADAPTIVE_CURSOR_SHARING” The value of the set FALSE. Once the value of this implied parameter is set to FALSE, Then all of them Child Cursor Will no longer be marked as Bind Aware（ Even if they have been marked as Bind Sensitive）, In other words, adaptive cursor sharing is directly disabled .
What needs to be noted here is , Adaptive cursors are shared in Oracle 11g There is a hard limit in —— Only when the goal SQL Binding variables in （ Regardless of whether the binding variable should be SQL It comes with the system after the normal cursor sharing is turned on ） No more than 14 Time , Adaptive cursor sharing takes effect ; Once you surpass 14, Then SQL Corresponding Child Cursor Will never be marked as Bind Sensitive, Then adaptive cursor sharing fails .
Here is an example of an adaptive cursor ：
The database version is 11.2.0.3, Prepare basic data ：
1CREATE TABLE T_ACS_20170611_LHR AS SELECT * FROM DBA_OBJECTS; 2CREATE INDEX IDX_ACS_OBJID_LHR ON T_ACS_20170611_LHR(OBJECT_ID); 3SELECT COUNT(1) FROM T_ACS_20170611_LHR; 4UPDATE T_ACS_20170611_LHR T SET T.OBJECT_TYPE='TABLE' WHERE ROWNUM<=60001; -- Update data , Give Way OBJECT_TYPE Become unbalanced 5UPDATE T_ACS_20170611_LHR T SET T.OBJECT_TYPE='CLUSTER' WHERE ROWNUM<=2; 6COMMIT; 7LHR@orclasm > SELECT T.OBJECT_TYPE,COUNT(*) COUNTS FROM T_ACS_20170611_LHR T GROUP BY T.OBJECT_TYPE ORDER BY 2 DESC; 8 9OBJECT_TYPE COUNTS 10------------------- ---------- 11TABLE 61818 12SYNONYM 3718 13INDEX 3082 14JAVA CLASS 2381 15VIEW 1231 16TYPE 973 17INDEX PARTITION 738 18TRIGGER 592 19INDEX SUBPARTITION 585 20PACKAGE 560 21PACKAGE BODY 545 22LOB 541 23TABLE PARTITION 315 24TABLE SUBPARTITION 223 25FUNCTION 159 26LOB SUBPARTITION 150 27LOB PARTITION 121 28SEQUENCE 109 29TYPE BODY 96 30PROCEDURE 55 31JAVA RESOURCE 31 32OPERATOR 25 33LIBRARY 20 34QUEUE 19 35RULE SET 16 36DIRECTORY 14 37DATABASE LINK 12 38XML SCHEMA 7 39DIMENSION 5 40PROGRAM 5 41EVALUATION CONTEXT 5 42JAVA DATA 4 43MATERIALIZED VIEW 4 44RULE 4 45JOB 2 46CLUSTER 2 47JAVA SOURCE 2 48CONTEXT 2 49INDEXTYPE 2 50UNDEFINED 1 51 52-- perform WHERE Conditional median OBJECT_TYPE Column SQL sentence , So that the base table COL_USAGE$ You can record the column , Facilitate the subsequent automatic collection of statistical information on this column ： 53LHR@orclasm > SELECT OO.NAME OWNER, 54 2 O.NAME TABLE_NAME, 55 3 C.NAME COLUMN_NAME, 56 4 U.EQUALITY_PREDS, 57 5 U.EQUIJOIN_PREDS, 58 6 U.NONEQUIJOIN_PREDS, 59 7 U.RANGE_PREDS, 60 8 U.LIKE_PREDS, 61 9 U.NULL_PREDS, 62 10 U.TIMESTAMP 63 11 FROM SYS.COL_USAGE$ U, SYS.OBJ$ O, SYS.USER$ OO, SYS.COL$ C 64 12 WHERE O.OBJ# = U.OBJ# 65 13 AND OO.USER# = O.OWNER# 66 14 AND C.OBJ# = U.OBJ# 67 15 AND C.COL# = U.INTCOL# 68 16 AND O.NAME='T_ACS_20170611_LHR' 69 17 ; 70 71no rows selected 72 73LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE='TABLE'; 74 75 COUNT(*) 76---------- 77 61818 78 79LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE='CLUSTER'; 80 81 COUNT(*) 82---------- 83 2 84 85LHR@orclasm > SELECT OO.NAME OWNER, 86 2 O.NAME TABLE_NAME, 87 3 C.NAME COLUMN_NAME, 88 4 U.EQUALITY_PREDS, 89 5 U.EQUIJOIN_PREDS, 90 6 U.NONEQUIJOIN_PREDS, 91 7 U.RANGE_PREDS, 92 8 U.LIKE_PREDS, 93 9 U.NULL_PREDS, 94 10 U.TIMESTAMP 95 11 FROM SYS.COL_USAGE$ U, SYS.OBJ$ O, SYS.USER$ OO, SYS.COL$ C 96 12 WHERE O.OBJ# = U.OBJ# 97 13 AND OO.USER# = O.OWNER# 98 14 AND C.OBJ# = U.OBJ# 99 15 AND C.COL# = U.INTCOL# 100 16 AND O.NAME='T_ACS_20170611_LHR' 101 17 ; 102 103no rows selected 104 105LHR@orclasm > EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; 106 107PL/SQL procedure successfully completed. 108 109LHR@orclasm > SELECT OO.NAME OWNER, 110 2 O.NAME TABLE_NAME, 111 3 C.NAME COLUMN_NAME, 112 4 U.EQUALITY_PREDS, 113 5 U.EQUIJOIN_PREDS, 114 6 U.NONEQUIJOIN_PREDS, 115 7 U.RANGE_PREDS, 116 8 U.LIKE_PREDS, 117 9 U.NULL_PREDS, 118 10 U.TIMESTAMP 119 11 FROM SYS.COL_USAGE$ U, SYS.OBJ$ O, SYS.USER$ OO, SYS.COL$ C 120 12 WHERE O.OBJ# = U.OBJ# 121 13 AND OO.USER# = O.OWNER# 122 14 AND C.OBJ# = U.OBJ# 123 15 AND C.COL# = U.INTCOL# 124 16 AND O.NAME='T_ACS_20170611_LHR' 125 17 ; 126 127OWNER TABLE_NAME COLUMN_NAME EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP 128------------------------------ ------------------------------ ------------------------------ -------------- -------------- ----------------- ----------- ---------- ---------- ------------------- 129LHR T_ACS_20170611_LHR OBJECT_TYPE 1 0 0 0 0 0 2017-06-11 08:34:34 130 131LHR@orclasm > 132LHR@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_ACS_20170611_LHR',ESTIMATE_PERCENT => 100,CASCADE => TRUE,METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',NO_INVALIDATE => FALSE); 133 134PL/SQL procedure successfully completed. 135 136LHR@orclasm > 137LHR@orclasm > SELECT D.COLUMN_NAME,D.NUM_DISTINCT,D.NUM_BUCKETS,D.HISTOGRAM FROM DBA_TAB_COL_STATISTICS D WHERE D.TABLE_NAME='T_ACS_20170611_LHR' AND D.COLUMN_NAME='OBJECT_TYPE'; 138 139COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM 140------------------------------ ------------ ----------- --------------- 141OBJECT_TYPE 40 40 FREQUENCY 142 143-- In keeping the implicit parameters “_OPTIM_PEEK_USER_BINDS” And parameters CURSOR_SHARING The values of are all their default values , Define binding variables and experiment with them ： 144LHR@orclasm > ALTER SYSTEM FLUSH SHARED_POOL; -- The production warehouse should be used with caution 145 146System altered. 147 148LHR@orclasm > conn lhr/lhr 149Connected. 150LHR@orclasm > VAR X VARCHAR2(30); 151LHR@orclasm > EXEC :X :='CLUSTER'; 152 153PL/SQL procedure successfully completed. 154 155LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X; 156 157 COUNT(*) 158---------- 159 2 160 161LHR@orclasm > col SQL_TEXT format a88 162LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT FROM V$SQLAREA A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=%'; 163 164SQL_TEXT SQL_ID VERSION_COUNT 165---------------------------------------------------------------------------------------- ------------- ------------- 166SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X bt8tk3f1tnwcf 1 167 168 169LHR@orclasm > SELECT A.SQL_ID,A.CHILD_NUMBER,A.EXECUTIONS,A.BUFFER_GETS,A.IS_BIND_SENSITIVE,A.IS_BIND_AWARE,A.IS_SHAREABLE FROM V$SQL A WHERE A.SQL_ID='bt8tk3f1tnwcf'; 170 171SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I 172------------- ------------ ---------- ----------- - - - 173bt8tk3f1tnwcf 0 1 54 Y N Y
The goal is SQL The predicate condition of is “OBJECT_TYPE=:X”, This predicate condition is an equivalent query condition with bound variables , And the target line OBJECT_TYPE There are FREQUENCY Type of histogram statistics , So the predicate condition is an unsafe predicate condition . At the same time SQL When executed, bound variable snooping is enabled , It means Oracle Will put the SQL Corresponding Child Cursor Marked as Bind Sensitive.
From the above query results, we can see that , The goal is SQL Corresponding IS_BIND_SENSITIVE The value of is Y,IS_BIND_AWARE The value of is N,IS_SHAREABLE The value of is Y, This means that SQL Corresponding Child Cursor It is true that it has been Oracle Marked as Bind Sensitive; meanwhile , The Child Cursor It’s also shareable , But it’s not yet Bind Aware Of . in addition , Above Child Cursor The corresponding runtime Statistics BUFFER_GETS（ That is logic reading ） The value of is 54, This is normal , Because when the value of the bound variable is “CLUSTER” when , The goal is SQL Of the corresponding result set Cardinality The value of is only 2.
1LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bt8tk3f1tnwcf',0,'advanced'));
2
3PLAN_TABLE_OUTPUT
4--------------------------------------------------------------
5SQL_ID bt8tk3f1tnwcf, child number 0
6-------------------------------------
7SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X
8
9Plan hash value: 3002671579
10
11---------------------------------------------------------------------------------------
12| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
13---------------------------------------------------------------------------------------
14| 0 | SELECT STATEMENT | | | | 3 (100)| |
15| 1 | SORT AGGREGATE | | 1 | 7 | | |
16|* 2 | INDEX RANGE SCAN| IDX_ACS_OBJID_LHR | 2 | 14 | 3 (0)| 00:00:01 |
17---------------------------------------------------------------------------------------
18
19Query Block Name / Object Alias (identified by operation id):
20-------------------------------------------------------------
21
22 1 - SEL$1
23 2 - SEL$1 / T@SEL$1
24
25Outline Data
26-------------
27
28 /*+
29 BEGIN_OUTLINE_DATA
30 IGNORE_OPTIM_EMBEDDED_HINTS
31 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
32 DB_VERSION('11.2.0.3')
33 ALL_ROWS
34 OUTLINE_LEAF(@"SEL$1")
35 INDEX(@"SEL$1" "T"@"SEL$1" ("T_ACS_20170611_LHR"."OBJECT_TYPE"))
36 END_OUTLINE_DATA
37 */
38
39Peeked Binds (identified by position):
40--------------------------------------
41
42 1 - :X (VARCHAR2(30), CSID=852): 'CLUSTER'
43
44Predicate Information (identified by operation id):
45---------------------------------------------------
46
47 2 - access("T"."OBJECT_TYPE"=:X)
48
49Column Projection Information (identified by operation id):
50-----------------------------------------------------------
51
52 1 - (#keys=0) COUNT(*)[22]
53
54
5549 rows selected.
56
57LHR@orclasm >
It can be seen from the above display that ,Oracle At this time, the selected execution plan is right index IDX_ACS_OBJID_LHR Index range scan for . be aware “Peeked Binds” Part of the content is “1 – :X (VARCHAR2(30), CSID=852): ‘CLUSTER’”, This explanation Oracle In hard resolution target SQL You do use bound variable snooping , And do it “ Prying into ” The input value of the binding variable “seen in this action is “CLUSTER”.
You will now X Is changed to “TABLE”：
1LHR@orclasm > EXEC :X :='TABLE';
2
3PL/SQL procedure successfully completed.
4
5LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X;
6
7 COUNT(*)
8----------
9 61818
10LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT,A.EXECUTIONS FROM V$SQLAREA A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=%';
11
12SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
13---------------------------------------------------------------------------------------- ------------- ------------- ----------
14SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X bt8tk3f1tnwcf 1 2
15
16LHR@orclasm >
17
18LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bt8tk3f1tnwcf',0,'advanced'));
19
20PLAN_TABLE_OUTPUT
21----------------------------------------------------
22SQL_ID bt8tk3f1tnwcf, child number 0
23-------------------------------------
24SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X
25
26Plan hash value: 3002671579
27
28---------------------------------------------------------------------------------------
29| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
30---------------------------------------------------------------------------------------
31| 0 | SELECT STATEMENT | | | | 3 (100)| |
32| 1 | SORT AGGREGATE | | 1 | 7 | | |
33|* 2 | INDEX RANGE SCAN| IDX_ACS_OBJID_LHR | 2 | 14 | 3 (0)| 00:00:01 |
34---------------------------------------------------------------------------------------
35
36Query Block Name / Object Alias (identified by operation id):
37-------------------------------------------------------------
38
39 1 - SEL$1
40 2 - SEL$1 / T@SEL$1
41
42Outline Data
43-------------
44
45 /*+
46 BEGIN_OUTLINE_DATA
47 IGNORE_OPTIM_EMBEDDED_HINTS
48 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
49 DB_VERSION('11.2.0.3')
50 ALL_ROWS
51 OUTLINE_LEAF(@"SEL$1")
52 INDEX(@"SEL$1" "T"@"SEL$1" ("T_ACS_20170611_LHR"."OBJECT_TYPE"))
53 END_OUTLINE_DATA
54 */
55
56Peeked Binds (identified by position):
57--------------------------------------
58
59 1 - :X (VARCHAR2(30), CSID=852): 'CLUSTER'
60
61Predicate Information (identified by operation id):
62---------------------------------------------------
63
64 2 - access("T"."OBJECT_TYPE"=:X)
65
66Column Projection Information (identified by operation id):
67-----------------------------------------------------------
68
69 1 - (#keys=0) COUNT(*)[22]
70
71
7249 rows selected.
73
74LHR@orclasm >
75LHR@orclasm > SELECT A.SQL_ID,A.CHILD_NUMBER,A.EXECUTIONS,A.BUFFER_GETS,A.IS_BIND_SENSITIVE,A.IS_BIND_AWARE,A.IS_SHAREABLE FROM V$SQL A WHERE A.SQL_ID='bt8tk3f1tnwcf';
76
77SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I
78------------- ------------ ---------- ----------- - - -
79bt8tk3f1tnwcf 0 2 309 Y N Y
You can see at this time VERSION_COUNT The value of is 1, Column EXECUTIONS The value of is 2, explain Oracle In the second execution of the target SQL It’s soft parsing ; From the target SQL The implementation plan of is still going to the index IDX_ACS_OBJID_LHR Index range scan for , also “Peeked Binds” Part of it is still “1 – :X (VARCHAR2(30), CSID=852): ‘CLUSTER’”. obviously , here Oracle The corresponding parsing tree and execution plan of the previous hard parsing are used , That is, bound variable snooping works .
You can also see from the query results , The goal is SQL Corresponding IS_BIND_SENSITIVE The value of is Y,IS_BIND_AWARE The value of is N,IS_SHAREABLE The value of is Y, These values have not changed compared with before . But we noticed that , Above Child Cursor The corresponding runtime Statistics BUFFER GETS From the previous value of 54 To the present 309, Great changes have taken place , But it’s normal . Because when the value of the binding variable “is “TABLE” when , The goal is SQL Of the corresponding result set cardinality The value of is 61818, It’s not what it was before 2 了 .
I was introducing Bind Aware I have already mentioned ： The goal is SQL The corresponding Child Cursor Marked as Bind Aware Necessary conditions , It’s the time to SQL In the next two consecutive execution corresponding to runtime Statistics and the SQL Hard parsing corresponds to runtime Statistical information is quite different . Although the logic here reads BUFFER GETS The value of has really changed a lot , But the above SQL The value of in is “TABLE” In this case, only once , So it’s not enough to be marked as Bind Aware The premise of ,IS_BIND_AWARE Of course, the value of is N 了 .
V$SQL_CS_SELECTIVITY Used to display the specified 、 Has been marked as Bind Aware Of Child Cursor The range of the selectivity rate corresponding to the predicate conditions with bound variables stored in . Above Child Cursor Not yet marked as Bind Aware, So now with the goal SQL Corresponding SQL_ID To query the view V$SQL_CS_SELECTIVITY You can’t see the corresponding record ：
1LHR@orclasm > SELECT * FROM V$SQL_CS_SELECTIVITY D WHERE D.SQL_ID='bt8tk3f1tnwcf'; 2 3no rows selected 4 5-- In binding variables X The value of is TABLE And then execute the target again SQL： 6LHR@orclasm > SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X; 7 8 COUNT(*) 9---------- 10 61818 11 12LHR@orclasm >
Now it’s time to SQL Corresponding Child Cursor Has been marked as Bind Sensitive 了 , And the SQL The next two consecutive execution correspond to runtime Statistics , And it’s time to SQL Before hard parsing corresponding to runtime Statistical information is quite different , So at this time Oracle In the execution of this SQL Hard parsing will be used when , namely Oracle At this point, a new Child Cursor（ This new Child Cursor It will hang on the original Parent Cursor Next ）, also Oracle I’ll take this new Child Cursor Marked as Bind Aware.
1LHR@orclasm > SELECT A.SQL_TEXT, A.SQL_ID,A.VERSION_COUNT,A.EXECUTIONS FROM V$SQLAREA A WHERE A.SQL_TEXT LIKE 'SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=%'; 2 3SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS 4---------------------------------------------------------------------------------------- ------------- ------------- ---------- 5SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X bt8tk3f1tnwcf 2 3 6 7LHR@orclasm > SELECT A.SQL_ID,A.CHILD_NUMBER,A.EXECUTIONS,A.BUFFER_GETS,A.IS_BIND_SENSITIVE,A.IS_BIND_AWARE,A.IS_SHAREABLE FROM V$SQL A WHERE A.SQL_ID='bt8tk3f1tnwcf'; 8 9SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS I I I 10------------- ------------ ---------- ----------- - - - 11bt8tk3f1tnwcf 0 2 309 Y N N 12bt8tk3f1tnwcf 1 1 522 Y Y Y
You can see , Above SQL The corresponding column VERSION_COUNT From the previous value of 1 Into the present 2, Column EXECUTIONS The value of is 3, explain Oracle In the third execution of the SQL What is really practical is hard analysis .V$SQL One more. CHILD NUMBER by 1 The new Child Cursor, And the Child Cursor Corresponding IS_BIND_SENSITIVE、IS_BIND_AWARE and IS_SHAREABLE Values are Y, This means that SQL In this hard parsing, the newly generated Child cursor It is true that it has been Oracle Marked as Bind Aware, meanwhile , The Child Cursor It’s also shareable .
The goal is SQL The current implementation plan is as follows ：
1LHR@orclasm > SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('bt8tk3f1tnwcf',1,'advanced'));
2
3PLAN_TABLE_OUTPUT
4------------------------------------------------------------------------
5SQL_ID bt8tk3f1tnwcf, child number 1
6-------------------------------------
7SELECT COUNT(*) FROM T_ACS_20170611_LHR T WHERE T.OBJECT_TYPE=:X
8
9Plan hash value: 4256744017
10
11-------------------------------------------------------------------------------------------
12| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
13-------------------------------------------------------------------------------------------
14| 0 | SELECT STATEMENT | | | | 89 (100)| |
15| 1 | SORT AGGREGATE | | 1 | 7 | | |
16|* 2 | INDEX FAST FULL SCAN| IDX_ACS_OBJID_LHR | 61818 | 422K| 89 (2)| 00:00:02 |
17-------------------------------------------------------------------------------------------
18
19Query Block Name / Object Alias (identified by operation id):
20-------------------------------------------------------------
21
22 1 - SEL$1
23 2 - SEL$1 / T@SEL$1
24
25Outline Data
26-------------
27
28 /*+
29 BEGIN_OUTLINE_DATA
30 IGNORE_OPTIM_EMBEDDED_HINTS
31 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
32 DB_VERSION('11.2.0.3')
33 ALL_ROWS
34 OUTLINE_LEAF(@"SEL$1")
35 INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T_ACS_20170611_LHR"."OBJECT_TYPE"))
36 END_OUTLINE_DATA
37 */
38
39Peeked Binds (identified by position):
40--------------------------------------
41
42 1 - :X (VARCHAR2(30), CSID=852): 'TABLE'
43
44Predicate Information (identified by operation id):
45---------------------------------------------------
46
47 2 - filter("T"."OBJECT_TYPE"=:X)
48
49Column Projection Information (identified by operation id):
50-----------------------------------------------------------
51
52 1 - (#keys=0) COUNT(*)[22]
53
54
5549 rows selected.
It can be seen from the above display that ,Oracle At this time, the selected execution plan is right index IDX_ACS_OBJID_LHR Index fast full scan . be aware “Peeked Binds” Part of the content is “1 – :X (VARCHAR2(30), CSID=852): ‘TABLE’”, explain Oracle In the hard analysis of the above SQL It’s true that bound variable snooping is used again in the process of , And do it “ Prying into ” The input value of the binding variable you see in this action is “TABLE”.
CHILD_NUMBER by 1 Of Child Cursor Has been marked as Bind Aware, So now with the goal SQL Corresponding SQL_ID To query the view V$SQL_CS_SELECTIVITY You can see the corresponding record ：
1LHR@orclasm > SELECT * FROM V$SQL_CS_SELECTIVITY D WHERE D.SQL_ID='bt8tk3f1tnwcf'; 2 3ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER PREDICATE RANGE_ID LOW HIGH 4---------------- ---------- ------------- ------------ ---------------------------------------- ---------- ---------- ---------- 500000000AA2108A8 2207936910 bt8tk3f1tnwcf 1 =X 0 0.711697 0.869852 6 7LHR@orclasm > SELECT * FROM V$SQL_CS_STATISTICS D WHERE D.SQL_ID='bt8tk3f1tnwcf' ORDER BY D.CHILD_NUMBER; 8 9ADDRESS HASH_VALUE SQL_ID CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS CPU_TIME 10---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ---------- 1100000000AA2108A8 2207936910 bt8tk3f1tnwcf 0 821942781 Y 1 3 54 0 1200000000AA2108A8 2207936910 bt8tk3f1tnwcf 1 3197905255 Y 1 61819 522 0
It can be seen from the above display that , primary SQL Predicate conditions in “=:x” The corresponding range of selectivity is [0.711697,0.869852], The lower limit of the selection rate range is 0.711697, Cap of 0.869852.
How is the range of the rate of choice calculated ？Oracle First of all, when we do hard analysis （ After doing the binding variable snooping ） The selectivity of the above predicate conditions （ Here, the calculated selectivity is recorded as S), And then S Fluctuate 10％ The range of the above selectivity is obtained , The calculation formula of the selection rate range is [0.9*S,1.1*S].