Bind variable Spy (bind Variable peeking)

Tag: Code does not have a cursor style INF CPU filter based on stat

Binding variable Spy is when Oracle will look at the true value of the bound variable when performing a hard-parse build execution plan to evaluate the selection rate of the predicate condition of the bound variable, affecting the execution plan is to choose whether the access path is to go through the index scan to access the table or go directly to the full table scan.

There is no binding variable spy this feature when Oracle how to evaluate the selectivity that, is based on the field statistics NDV,NDV close to the table of the number of rows, the higher the selectivity, the greater the probability of the index, the smaller the NDV, the lower the selectivity, the more the probability of walking the full table scan, but in the real environment, Many business scenarios in the value of the field is not uniform, this time using NDV to evaluate the selectivity is obviously unreasonable, so the binding variable spy will refer to the value of the field, the field histogram and other statistical information, will evaluate a reliable selection rate, this time the optimizer to choose more accurate.

The benefit of binding variable spy is the ability to accurately generate execution plans at the time of the first statement execution, but since the cursor is shared, then the same statement is executed again without prying eyes, which is a bad place to use bound variables.

The binding variable spy is introduced in Oracle 9i and is controlled by the hidden parameter _optim_peek_user_binds, and the default value of this parameter is true, which means that the binding variable spy is enabled by default.

Whether the optimizer uses the binding variable spy to generate the execution plan. You can print the execution plan in the cursor to see the effect of this feature.

1 Select* fromTable (Dbms_xplan.display_cursor ('D0G5MW5SKPTKQ',0,'allstats Advanced'));2 3sql_id D0G5MW5SKPTKQ, child number04-------------------------------------5 Select* fromT1whereOwner =: Owner6  7Plan Hash Value:36176920138  9---------------------------------------------------------------------------Ten| Id | Operation | Name | E-rows | e-bytes| Cost (%CPU) | E-time | One--------------------------------------------------------------------------- A|0|      SELECT STATEMENT |        |       | |427( -)| | -|*1| TABLE ACCESS full| T1 |90944| 9m|427(1)|xx:xx: on| ---------------------------------------------------------------------------- the   -Query Block Name/Object Alias (identified by Operation ID): -------------------------------------------------------------- --sel$1/[Email protected]$1 + peeked binds (identified by position): --------------------------------------- : 1 (VARCHAR2 (+), csid=178): 'SYS'  A   at predicate information (identified by Operation ID): ---------------------------------------------------- --Filter ("OWNER"=:owner)

The yellow part of the execution plan therefore, when Oracle generates this execution plan, it uses the binding variable spy, because the ' SYS ' selection rate in the column is too low, so the optimizer chooses to walk the table-access path of the full table scan.

Bind variable Spy (bind Variable peeking)