Bound variable snooping and histograms

Binding variable peep the CBO a subsidiary function that closes he won't affect is with CBO or RBO, even if the binding variable peep off, the CBO will use other statistics (num_distinct,density, etc.) to evaluate the cost and cardinality, only is the inability to use histogram information: sql> SELECT x.ksppinm NAME, Y.KSPPSTVL VALUE, X.ksppdesc describ from Sys.x$ksppi x, SYS.X$KSPPCV y W Here x.inst_id = USERENV (' Instance ') and y.inst_id = USERENV (' Instance ') and x.indx = Y.indx and x.ksppinm like '%&am  P;par% '; 2 3 4 5 6 Enter value for Par:peek_user old 6:and x.ksppinm like '%&par% ' new 6:and x.ksppinm L IKE '%peek_user% ' NAME VALUE describ------------------------------------------------------------------------------

--_optim_peek_user_binds TRUE enable peeking of user binds currently opens the binding variable to start the test: sql> alter system flush Shared_pool;

System altered. Sql> Select Sql_text,sql_id,a.executions from V$sql a where a.parsing_schema_name= ' SCOTT ' ORDER by Last_active_time de  Sc 
2 3 No rows selected sql> variable n number;

sql> exec:n: = 7499;

Pl/sql procedure successfully completed. Sql> SELect * from emp where empno =: n; EMPNO ename JOB MGR hiredate SAL COMM--------------------------------------------------------- 	      -----------DEPTNO dname------------------------------------------------------------7499 ALLEN salesman 7698 20-feb-81 1600 UFO sql> Select Sql_text,sql_id,a.executions from V$sql a where a.parsi   Ng_schema_name= ' SCOTT ' ORDER by last_active_time Desc; 2 3 sql_text sql_id executions-----------------------------------------------------select * from EM P where empno 3VV0T64YN0WRM 1 =: N begin:n: = 7499;	       End;

2u1u06mytpsha 1 sql> exec:n: = 7521;

Pl/sql procedure successfully completed.

     Sql> SELECT * from emp where empno =: n; EMPNO ename JOB MGR hiredate SAL COMM--------------------------------------------------------- -----------DEPTNO dname------------------------------------------------------------7521 WARD salesman 7698 22-feb-81 1250 UFO View number of executions: sql> Select Sql_  Text,sql_id,a.executions from V$sql a where a.parsing_schema_name= ' SCOTT ' ORDER by last_active_time Desc; 2 3 sql_text sql_id executions-----------------------------------------------------select * from EM P where empno 3VV0T64YN0WRM 2 =: N begin:n: = 7521;	       End; 9b4dm4tp4k58q 1 Begin:n: = 7499;	       End;

2u1u06mytpsha 1 At this point the code is fully shared 2. Continue testing, turn off binding variables snooping: sql> sql> alter system set "_optim_peek_user_binds" =FALSE;

System altered.
Session altered.  Sql> SELECT x.ksppinm NAME, Y.KSPPSTVL VALUE, X.ksppdesc describ from Sys.x$ksppi x, SYS.X$KSPPCV y WHERE x.inst_id =  USERENV (' Instance ') and y.inst_id = USERENV (' Instance ') and x.indx = Y.indx and x.ksppinm like '%&par% '; 2 3 4 5 6 Enter value for Par:peek_user old 6:and x.ksppinm like '%&par% ' new 6:and x.ksppinm L IKE '%peek_user% ' NAME----------VALUE---------------------------------------------------------------------------------- --------------------------------------------------------------------------

------------------------------------- -------Describ------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------_optim_

Pee K_user_bin DS FALSE Enable peeking of user binds at this point the binding variable has been closed: sql> alter system flush Shared_pool;


System altered.

sql> exec:n: = 7698;

Pl/sql procedure successfully completed.

     Sql> SELECT * from emp where empno =: n; EMPNO ename JOB MGR hiredate SAL COMM---------------------------------------------------------	      -----------DEPTNO dname------------------------------------------------------------7698 BLAKE MANAGER 7839 01-may-81 2850 UFO sql> exec:n: = 7654;

Pl/sql procedure successfully completed.

     Sql> SELECT * from emp where empno =: n; EMPNO ename JOB MGR hiredate SAL COMM--------------------------------------------------------- 	      -----------DEPTNO dname------------------------------------------------------------7654 MARTIN salesman 7698 28-sep-81 1250 1400 UFO View execution: sql> Select Sql_text,sql_id,a.executions from V$sql a wher

E a.parsing_schema_name= ' SCOTT ' 2; Sql_text sql_id Executions-----------------------------------------------------SELECT * from emp where em Pno 3VV0T64YN0WRM 2 =: N begin:n: = 7698;	       End; gp8mhcr67r352 1 Begin:n: = 7654;	       End; APKBVS4ZQ7CNH 1 sql> Select A.sql_text,a.sql_id,a.executions,a.version_count from V$sqlarea a where sql_id= ' 3vv0

T64YN0WRM '; Sql_text sql_id executions Version_count----------------------------------------------------------------- -SElect * from EMP where empno 3VV0T64YN0WRM 3 1 =: n sql> Select A.sql_text,a.sql_id,a.executions,a.child _number from V$sql a where sql_id= ' 3VV0T64YN0WRM '; sql> sql_text sql_id executions child_number-------------------------------------------------------- ---------SELECT * from emp where empno 3VV0T64YN0WRM 3 0 =: N Description Turn off the binding variable snooping, do not affect the sharing of SQL statements, close the binding variable prying, Oracle cannot use histograms

Information.

Continue testing turn off binding variable snooping, impact on Oracle execution plan? First open the binding variable snooping: sql> SELECT x.ksppinm NAME, Y.KSPPSTVL VALUE, X.ksppdesc describ from Sys.x$ksppi x, SYS.X$KSPPCV y WHERE x.inst_id = USERENV (' Instance ') and y.inst_id = USERENV (' Instance ') and x.indx = Y.indx and x.ksppinm like '%&par  %'; 2 3 4 5 6 Enter value for Par:peek_user old 6:and x.ksppinm like '%&par% ' new 6:and x.ksppinm L IKE '%peek_user% ' NAME--------------------------------------------------------------------------------VALUE----- ---------------------------------------------------------------------------Describ--------------------------------------------------------------------------------_optim_ Peek_user_binds TRUE enable peeking of user binds sql> begin 2 for I in 1. 10000 3 loop 4 insert INTO test values (1, ' A1 ' | |
  i);
  5 commit; 
  6 end Loop;
  7 End;

8/sql> INSERT INTO test values (2, ' a ');

1 row created.

Sql> commit;

Commit complete.

	Sql> Select Id,count (*) from the Test 2 group by ID; ID COUNT (*)--------------------1 10000 2 1 sql> BEGIN dbms_stats.
                                Gather_table_stats (ownname => ' test ', tabname => ' test ',  Estimate_percent =>, method_opt => ' for all columns           Size Skewonly ', no_invalidate => FALSE, Degree
=> 8, Cascade => TRUE);
  End;    2 3 45 6 7 8 9 11/pl/sql procedure successfully completed.
sql> variable n number;

sql> exec:n: = 2;

Pl/sql procedure successfully completed.

	Sql> SELECT * FROM test where id =: n;

ID NAME--------------------2 A sql> select * from table (dbms_xplan.display_cursor); Plan_table_output--------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------------Sql_ ID 14nw6f8vtgsz7, child number 0-------------------------------------select * FROM test where id =: N plan hash Value: 2624864549-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |   -----------------------------------------------------------------------------------------
| 0 |		SELECT STATEMENT |     |	|	|		2 (100) |   |
|  1 | TABLE ACCESS by INDEXrowid|     TEST |    1 |     14 | 2 (0) |   00:00:01 | |* 2 | INDEX RANGE SCAN |     test_idx1 |     1 | | 1 (0) |
00:00:01 | -----------------------------------------------------------------------------------------Predicate information ( identified by Operation ID):---------------------------------------------------2-access ("id" =:n) rows Selecte

D. Returns an indexed scan of a recorded walk sql> exec:n: = 1;

Pl/sql procedure successfully completed.

Sql> SELECT * FROM test where id =: n;

Sql> select * FROM table (dbms_xplan.display_cursor); Plan_table_output--------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------------Sql_ ID 14nw6f8vtgsz7, child number 0-------------------------------------select * FROM test where id =: N plan hash Value: 2624864549-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |   -----------------------------------------------------------------------------------------
| 0 |		SELECT STATEMENT |     |	|	|		2 (100) |   |
|  1 | TABLE ACCESS by INDEX rowid|     TEST |    1 |     14 | 2 (0) |   00:00:01 | |* 2 | INDEX RANGE SCAN |     test_idx1 |     1 | | 1 (0) |
00:00:01 | -----------------------------------------------------------------------------------------Predicate information ( identified by Operation ID):---------------------------------------------------2-access ("id" =:n) rows Selecte


D. Go or index Scan, fully explained that Oracle in the processing of SQL with binding variables, only in the hard parsing of the value of the binding variable in SQL, and then based on the value of the detected to determine the entire SQL execution plan.

Turn off binding variables snooping: sql> alter system set "_optim_peek_user_binds" =FALSE;

System altered.

Sql> alter system flush Shared_pool;

System altered.
sql> variable n number;

sql> exec:n: = 2;

Pl/sql procedure successfully completed.

	Sql> SELECT * FROM test where id =: n; ID NAME--------------------2 A sql> select * from table (dbms_xplan.display_cursor); Plan_table_output--------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------------Sql_  ID 9tcmwpk23vu2y, child number 0-------------------------------------select * FROM test where id =: N plan hash Value: 1357081020--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |   --------------------------------------------------------------------------
| 0 |	 SELECT STATEMENT |	 |	 |     | 	 9 (100) |  | |* 1 | TABLE ACCESS full|  TEST | 5001 |     70014 | 9 (0) |
00:00:01 | --------------------------------------------------------------------------predicate information (identified by Operation ID):---------------------------------------------------1-filter ("id" =:n) roWS selected.


At this point went the full table scan, due to the close of the binding variable snooping, can not use histogram information.

  conclusion; (1) Without binding variables in case of column skew severity, histograms can provide the best data distribution Reference (2) binding variables can be used to peep the histogram, but 11g adaptive cursor sharing cannot distinguish between binding sensitive and insensitive cursors

(3) Do not peek at the binding variable in the case of loading histogram information, but the actual calculation cardinality does not refer to histogram start binding variables: The use of histogram information, and then based on the value of the detected to determine the entire SQL execution plan.

 Do not start binding variables: Do not use histogram information, Oracle does not know the distribution of data regardless of whether to start binding variable snooping does not affect SQL statement sharing