Oracle bound variable snooping

With the specific input values, the selectable rate (selectivity) of the Where condition of SQL and the number of rows (cardinality) of the result set may vary, The value of selectivity and cardinality directly affects the CBO's estimate of the cost value of the relevant execution steps, which in turn affects the CBO's choice of the SQL execution plan. This means that the target SQL execution plan may change as the specific input values are different.

For SQL that does not use bound variables, the SQL text of the target SQL changes as the number of input values changes, so that Oracle can easily calculate the values of the corresponding selectivity and cardinality to select the execution plan accordingly. But for SQL using bound variables, the situation is completely different, because now regardless of the specific input value of the corresponding binding variable, the SQL text of the target SQL is exactly the same, in which case how should Oracle decide the execution plan of the target SQL?

For SQL using bound variables, Oracle can choose from two ways to determine its execution plan:

• Using bound variables to snoop

• If you do not use a binding variable for snooping, the default selectable rate (for example, 5%) is used for predicate conditions where the selectable rate may vary depending on the specific input value.

Binding variable snooping (bind peeking) is introduced in Oracle 9i, whether binding variables are enabled to spy on the control of _optim_peek_user_binds by the implied parameters, the default value of _optim_peek_user_binds is true, Indicates that the binding variable snooping is enabled by default in Oracle 9i and its subsequent releases.

When the binding variable snooping is enabled, Oracle will actually spy on (peeking) the specific input values of the corresponding binding variable whenever Oracle parses the target SQL using the bound variable in a hard-parsing manner, with these specific input values as the standard. To determine the values of the selectivity and cardinality of the Where condition of the target SQL that used the bound variable, and select the execution plan for that SQL accordingly. This "Spy (peeking)" action is only performed when the hard parsing, when the target SQL using the binding variable is executed again (in this case, the corresponding soft-parsing/soft-soft parsing), even if the corresponding binding variable at this time the specific input value and the previous hard parsing corresponding value is different, Oracle also inherits the parse tree and execution plan from the previous hard parse, and does not repeat the "snooping" action described above.

The benefit of binding variable snooping is obvious, because with the binding variable snooping, Oracle can avoid using the default selectable rate when calculating the values of selectivity and cardinality for the where condition of the target SQL. So there is a greater likelihood of getting the SQL accurate execution plan. Similarly, the downside of binding variable snooping is obvious, for Target SQL whose execution plan may vary with the specific input value of the corresponding binding variable, once the binding variable snooping is enabled, its execution plan will be fixed to what the fixed execution plan is, is completely dependent on the specific value of the corresponding binding variable passed in by the SQL at hard parse time. This means that a single amount of binding variable snooping is enabled, and the target SQL will follow through with the parse tree and execution plan generated by the previous hard parse, even if this continuation is not appropriate for the current situation.

Binding variable snooping This behavior, regardless of the specific input value of the subsequent incoming binding variable, has been followed by the parsing rights and execution plan characteristics of the previous hard parse that have been criticized (this situation has been mitigated until the introduction of adaptive cursor sharing in Oracle 11g), as it may allow the CBO to The execution plan selected (for some specific input values of the binding variable) is not the optimal execution plan for the target SQL in the current scenario, and it may lead to a sudden change in the target SQL execution plan, which directly affects the performance of the application system.

For example, a SQL execution plan will correspond to two execution plans depending on the specific input value of the binding variable, one is the index range scan for the index, and the other is a fast full scan of the index. Under normal circumstances, for most of the bound variable input values, the execution plan should go through the index range scan, in rare cases will go index fast full scan. But if there is a shared cursor for the SQL that is out of the shared pool by age, then Oracle will have to parse hard when the SQL executes again. Unfortunately, if the input value of the binding variable passed in the hard parse is exactly the very few cases of fast full scan, then the subsequent SQL walk will take the execution plan, in which case the SQL execution efficiency is likely to be one or more orders of magnitude slower than before. Performance in the application system is suddenly one day found that an application is not moving, and has been good.

Let's look at an example of a bound variable snooping:

Create a test table T1 and index and collect statistical information

[Email protected]>create table T1 as select * from Dba_objects; Table created. [Email protected]>create index idx_t1 on T1 (object_id); index created.  [Email protected]>select count (*) from T1; COUNT (*)----------72005[email protected]>select count (Distinct (object_id)) from T1; COUNT (DISTINCT (object_id))--------------------------72005[email protected]>exec dbms_stats.gather_table_stats (ownname=>user,tabname=> ' T1 ',estimate_percent=>100,cascade=>true,method_opt=> ' for all columns size 1 ', no_invalidate=>false);P L/sql procedure successfully completed.

Perform the following two SQL and view Oracle's parsing of SQL

[Email protected]>select count (*)  from t1 where object_id between  999 and 1000;  count (*)---------- 2[email protected]>select count (*)  from t1 where object_id between 999 and 60000;  count (*)--- -------     58180[email protected]>col sql_text for a80[email  protected]>select sql_text,sql_id,version_count,executions from v$sqlarea where  sql_text like  ' Select count (*)  from t1 % '; sql_text sql_id  version_count  Executions-------------------------------------------------------------------------------- -------------- ------------------------- ------------- ----------select count (*)  from t1 where  object_id between 999 and 1000 5gu397922cuqd     11selEct count (*)  from t1 where object_id between 999 and 60000  B8xxw70vja3tn     11

As you can see from the query results, Oracle uses hard parsing when executing the above SQL. Oracle generates a parent cursor and a child cursor for each of the two SQL respectively.

Then view the execution plan:

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/8E/58/wKioL1i-V5HTyQs-AABEBprX2lI104.png "style=" float : none; "title=" 1.png "alt=" Wkiol1i-v5htyqs-aabebprx2li104.png "/>

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/8E/5A/wKiom1i-V5KCcftKAABHNo2Q7oY075.png "style=" float : none; "title=" 2.png "alt=" Wkiom1i-v5kccftkaabhno2q7oy075.png "/>

From the execution plan you can see that between 999 and 1000 of the SQL Walk is an index range scan, while between 999 and 60000 walk the execution plan is the index fast full scan.

Now we are transforming the full two SQL to use the equivalent form of the binding variable. Define two binding variables x and Y, and assign them values 999 and 1000, respectively.

[Email protected]>var x number; [Email protected]>var y number; [email protected]>exec:x: = 999;pl/sql procedure successfully completed. [email protected]>exec:y: = 1000;pl/sql procedure successfully completed.

Obviously, the rewrite form "Between:x and:y" with the binding variable x and Y is equivalent to the original "between 999 and 1000". And if Y is re-assigned to 60000, then it is equivalent to "between 999 and 60000".

Now the values for x and Y are 999 and 100, respectively, to perform the rewritten SQL

[Email protected]>select count (*)  from t1 where object_id between : X and :y;  count (*)---------- 2[email protected]>select sql_text,sql_id, version_count,executions from v$sqlarea where sql_text like  ' Select count (* )  from t1 % '; sql_text sql_id  version_count  Executions-------------------------------------------------------------------------------- -------------- ------------------------- ------------- ----------select count (*)  from t1 where  object_id between 999 and 1000 5gu397922cuqd      11select count (*)  from t1 where object_id between 999 and 60000  b8xxw70vja3tn     11select count (*)  from t1 where  object_id between :x and :y 9dhu3xk2zu531     11 

As you can see from the above query results, Oracle is also hard-resolved when executing the above equivalent SQL for the first time

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/8E/5A/wKiom1i-WbDhyFLFAACXGqnFHA4818.png "title=" 1.png "alt=" Wkiom1i-wbdhyflfaacxgqnfha4818.png "/>

From the execution plan, this is the index range scan for index IDX_T1, and Oracle evaluates the value of cardinality for the result set returned by this index range scan to 3. and notice that the "peeked binds" section of the content is "1-: X (number): 999 2-: Y (number): 1000", which means that Oracle in the process of hard parsing the above SQL did use the binding variable snooping, and do "snooping" The specific input values for the binding variables x and y seen in this action are 999 and 1000, respectively.

Now keep x unchanged, change Y to 60000:

[email protected]>exec:y: = 60000;pl/sql procedure successfully completed.  [Email protected]>select count (*) from T1 where object_id between:x and:y; COUNT (*)----------58180

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/8E/5B/wKiom1i-W1GjIt6yAAA2k1Z_Uos382.png "style=" float : none; "title=" 1.png "alt=" Wkiom1i-w1gjit6yaaa2k1z_uos382.png "/>

From the above query results can be seen above SQL corresponding to the value of Version_count is 1, the value of column executions is 2, which indicates that Oracle is the second time to execute the SQL with soft parsing.

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/8E/59/wKioL1i-W1LwF0D7AACXQcddp3o111.png "style=" float : none; "title=" 2.png "alt=" Wkiol1i-w1lwf0d7aacxqcddp3o111.png "/>

As you can see from the execution plan, the execution plan for SQL is still going to be the index range scan for index IDX_T1, and the content of the "peeked binds" section remains "1-: X (number): 999 2-: Y (number): 1000".

Previously, when we did not use binding variables, we knew that Oracle was taking an index fast full scan when executing the "between 999 and 60000" condition. But the second execution uses the binding variable equivalent to overwrite the SQL, even if the binding variables x and y of the specific input values are 999 and 60000, but Oracle still follows the SQL before the hard parsing (corresponding to the binding quantitative X and y of the specific input values are 999 and 1000) resulting from the parse tree and execution plan , instead of repeatedly performing "snooping" actions.

If you want the above equivalent SQL to go through the index fast full scan again, you only need to use hard parsing when Oracle executes SQL again. Because once hard parsing is used, Oracle will perform a "snooping" action again. There are many ways to use hard parsing when Oracle executes the target SQL again, and one common way is to perform DDL operations on the tables involved in the target SQL. Because once a DDL operation is performed on a table, all shared cursor in the library cache that contains the table in SQL text is marked as invalidated by Oracle (invalid), which means that the parse tree and execution plan stored in these shared cursor will no longer be reused , so when Oracle executes the SQL associated with this table again, it uses hard parsing. Here you choose to annotate the table (COMMENT), which is also a DDL operation.

Execute Comment statement against table T1 and execute equivalent SQL

[email protected]>comment on table t1 is  ' Test table for bind  peeking '; Comment created. [Email protected]>select count (*)  from t1 where object_id between : X and :y;  count (*)----------     58180[email protected]> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text  like  ' Select count (*)  from t1 % '; sql_text sql_id  version_count  Executions-------------------------------------------------------------------------------- -------------- ------------------------- ------------- ----------select count (*)  from t1 where  object_id between 999 and 1000 5gu397922cuqd      11select count (*)  from t1 where object_id between 999 And 60000 b8xxw70vja3tn     11select count (*)  from t1  Where object_id between :x and :y 9dhu3xk2zu531     11

From the results of the query above you can see that the equivalent SQL corresponding column Version_count value is 1, the value of the column executions from the previous 2 to the current 1, that Oracle in the third execution of the SQL with a hard parse (the value of executions is 1, Because Oracle regenerates a pair of parent cursor and child cursor here, the shared cursor corresponding to the original executions value of 2 has been marked as invalid by Oracle, which is equivalent to being discarded.

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/8E/5B/wKiom1i-X_CSPyGlAACbQqs2_bE113.png "title=" 1.png "alt=" Wkiom1i-x_cspyglaacbqqs2_be113.png "/>

As can be seen from the execution plan, the execution plan now goes through an index fast full scan, and Oracle evaluates the cardinality value of the result set returned by the fast full scan to perform this index at 57646. and the contents of the "peeked binds" section remain "1-: X (number): 999 2-: Y (number): 60000". Demonstrates that Oracle did use the binding variable snooping again during the execution of the SQL above, and the specific input values for the binding variables x and Y that were seen during the "snooping" action were 999 and 60000, respectively.

Now set the value of the implied parameter _optim_peek_user_binds to False to turn off the binding variable snooping:

[Email protected]>alter session Set "_optim_peek_user_binds" =FALSE; Session altered.

Then keep the value of x unchanged, change the Y value to 1000

[Email protected]>select count (*)  from t1 where object_id between : X and :y;  count (*)---------- 2[email protected]>select sql_text,sql_id, version_count,executions from v$sqlarea where sql_text like  ' Select count (* )  from t1 % '; sql_text sql_id  version_count  Executions-------------------------------------------------------------------------------- -------------- ------------------------- ------------- ----------select count (*)  from t1 where  object_id between 999 and 1000 5gu397922cuqd      11select count (*)  from t1 where object_id between 999 and 60000  b8xxw70vja3tn     11select count (*)  from t1 where  object_id between :x and :y 9dhu3xk2zu531     22 

From the results of the above query, you can see that the value of the equivalent SQL corresponding column version_count and column executions has changed from 1 to 2, which means that Oracle uses hard parsing when it executes the SQL for the fourth time. A value of Version_count of 2 means that two child cursor is hung under the parent cursor where the SQL resides. As you can see from the following query results, the SQL does have two child Cursor:

[Email protected]>select plan_hash_value,child_number from v$sql where sql_id= ' 9dhu3xk2zu531 '; Plan_hash_value child_number---------------------------1410530761 0 2351893609 1

Obviously, the parsing right and execution plan that we put the binding variable snooping on when we execute SQL again is stored in the child cursor with Child_number 1. View execution plans

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/8E/5A/wKioL1i-Zo7wJFiJAACPkGWks_M680.png "title=" 1.png "alt=" Wkiol1i-zo7wjfijaacpkgwks_m680.png "/>

From the execution plan you can see that Oracle's execution plan at this time has changed from the previous index rapid full scan to the current index range scan. And Oracle evaluates the cardinality value of the result set returned by performing this index range scan to 180. Note that the Outline data section has "Opt_param (' _optim_peek_user_binds ' false ')" and there is no "peeking binds" part of the execution plan, stating that Oracle has disabled the binding variable snooping at this time.

As explained earlier, the use of DDL operations allows Oracle to use hard parsing when it executes SQL again, but the downside of this approach is that it is far too broad because once a DDL operation is performed on a table, all SQL associated with the table is used for hard parsing. This is not good, especially for OLTP-type applications, because this can result in a sharp increase in the number of hard resolutions in a short period of time, which in turn affects the performance of the system.

Here's a way to get Oracle to use hard parsing when it executes the target SQL again, but its scope is limited to the shared Cursor of the target SQL, which means that it can enable Oracle to use hard parsing when executing the target SQL. All other SQL will remain the same as it did when it was executed.

This approach is to use Dbms_shared_pool. PURGE. It is a method introduced from Oracle 10.2.0.4 that can be used to delete a shared Cursor that is cached in the library cache. Dbms_shared_pool. Purge the principle that Oracle can use hard parsing when executing target SQL is obvious-if a shared cursor for a SQL is deleted, Oracle will naturally use hard parsing when it executes the SQL again.

To view the address and Hash_value values for the target sql:

[Email protected]>select sql_text,sql_id,version_count,executions,address,hash_value from  v$sqlarea where sql_text like  ' Select count (*)  from t1 % '; Sql_text sql_id  version_count executions address   hash_ VALUE-------------------------------------------------------------------------------- ------------------- -------------------- ------------- ---------- ---------------- ----------Select count (*)  FROM T1 WHERE OBJECT_ID BETWEEN 999 AND 1000 5GU397922CUQD      11 00000000b4d1b130 1143368397select count (*)  from t1  where object_id between 999 and 60000 b8xxw70vja3tn      11 00000000b4d1aa90  924127028select count (*)  from t1 where  object_id between :x and :y 9dhu3xk2zu531     22 00000000b4cc4840 2247955553 

Use Dbms_shared_pool.purge to delete the shared Cursor of the target sql:

[Email protected]>exec sys.dbms_shared_pool.purge (' 00000000b4cc4840,2247955553 ', ' C ');P l/sql  Procedure successfully completed. [Email protected]>select sql_text,sql_id,version_count,executions,address,hash_value from  v$sqlarea where sql_text like  ' Select count (*)  from t1 % '; Sql_text sql_id  version_count executions address   hash_ VALUE-------------------------------------------------------------------------------- ------------------- -------------------- ------------- ---------- ---------------- ----------Select count (*)  FROM T1 WHERE OBJECT_ID BETWEEN 999 AND 1000 5GU397922CUQD      11 00000000b4d1b130 1143368397select count (*)  from t1  where object_id between 999 and 60000 b8xxw70vja3tn      11 00000000b4d1aa90  924127028 

As you can see from the above query results, Dbms_shared_pool.purge did delete the shared Cursor for the target SQL.

It is important to note that if you use Dbms_shared_pool.purge in 10.2.0.4, you must have an agent set event 5614566 before use (Alter session set events ' 5614566 trace name Context forever '), otherwise dbms_shared_pool.purge will not work, this limit is no longer present in the 10.2.0.4 version.

Now keep the X value unchanged, modify Y to 60000, and execute the target sql:

[email protected]>exec :y := 60000;pl/sql procedure successfully  Completed. [Email protected]>select count (*)  from t1 where object_id between : X and :y;  count (*)----------     58180      [email protected]>select sql_text,sql_id,version_count,executions from v$sqlarea  where sql_text like  ' Select count (*)  from t1 % '; sql_text sql_id  version_count  Executions-------------------------------------------------------------------------------- -------------- ------------------------- ------------- ----------select count (*)  from t1 where  object_id between 999 and 1000 5gu397922cuqd      11select count (*)  from t1 where object_id between 999 anD 60000 b8xxw70vja3tn     11select count (*)  from t1  where object_id between :x and :y 9dhu3xk2zu531     21[ Email protected]>select plan_hash_value,child_number from v$sql where sql_id= ' 9dhu3xk2zu531 '; Plan_hash_value child_number--------------- ------------     2351893609     0

From the results of the above query, you can see that the SQL corresponds to a value of Version_count of 2,executions of 1. A value of executions of 1 indicates that Oracle is actually using hard parsing when executing some SQL, but the value of version_count should be 1, and from the query it is also true that only a child cursor is hung under the parent cursor of the target SQL. So the value of version_count should be 1 instead of 2 (maybe Oracle bug, no more).

As you can see from the execution plan, Oracle still chooses an index range scan, and the value of cardinality for the result set returned by Oracle to perform this index range scan remains at 180.

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/8E/5C/wKiom1i-bSni1GokAACUucZJtPw938.png "title=" 1.png "alt=" Wkiom1i-bsni1gokaacuuczjtpw938.png "/>

This means that when we turn off the bound variable, no matter what the value passed in to the bound variable x and y, it will not affect the Oracle's choice of the target SQL execution plan. This also fits with what was mentioned earlier: If you do not use a binding variable for snooping, Oracle uses the default selectable rate (for example, 5%) for predicate conditions where the selectable rate may vary with the specific input value.

How did the 180 figure out?

The formula for the selectivity and cardinality of the above SQL where condition is as follows:

cardinality = Num_rows * selectivity

selectivity = 0.05*0.05

The above formula applies to calculations that disable the binding variable snooping and where conditions are "target column between x and Y" for selectivity and cardinality

Num_rows indicates the number of records in the column that contains the target column

The Where condition is the target column between x and Y, which is equivalent to the target column >=x and target column <=y, and Oracle uses a 5% selectable rate for the target column >=x and destination column <=y. So the selectable rate is 0.05*0.05.

The substituting formula is calculated and the result is 180.

[Email protected]>select table_name,num_rows from dba_tables where Owner=user and table_name= ' T1 '; TABLE_NAME Num_rows-------------------------------------------------------------------------------------------- --------T172005[email protected]>select Round (72005*0.05*0.05) from dual; ROUND (72005*0.05*0.05)----------------------180

Reference: Oracle-based SQL optimization

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1904004

Oracle bound variable snooping