4 Replies Latest reply: Jan 8, 2013 4:19 AM by Dom Brooks RSS

    how can we verify if bind peekink is activated by Oracle or not

    981252
      Hi

      Oracle Version 10.2.0.5
      CURSOR_SHARING="SIMILAR"
      hidden parameter optimpeek_user_binds = "TRUE"
      stats gathering with "for all columnes size 1 "

      SQL>
      SQL> set linesize 300
      SQL> set pagesize 1000
      SQL> /

      PLAN_TABLE_OUTPUT
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      SQL_ID bh7hrghvacvqn
      --------------------
      select * from ( select f_p_person0_.BPP_IDENT as col_0_0_ from F_P_PERSONNE f_p_person0_
      left outer join T_ETABLISS t_etabliss1_ on f_p_person0_.BPP_PTRETABIDENT=t_etabliss1_.TA_IDEN
      T left outer join F_INTERMEDIAIRE f_intermed2_ on
      f_p_person0_.BPP_PTR_INIDENT=f_intermed2_.IN_IDENT where (f_p_person0_.BPP_PP_ACTIVE is
      null) and (f_p_person0_.BPP_SOCIETE=:1 or f_p_person0_.BPP_SOCIETE=:2 ) and
      (t_etabliss1_.TA_CODE=:3 or t_etabliss1_.TA_IDENT is null) and
      (t_etabliss1_.TA_PTRTDILVID=:4 or t_etabliss1_.TA_IDENT is null) and (exists (select
      f_polices3_.POL_IDENT, f_p_c_clie4_.BPCL_IDENT from F_POLICES f_polices3_, F_P_C_CLIENT
      f_p_c_clie4_ where f_p_c_clie4_.BPCL_PTRBPPIDENT=f_p_person0_.BPP_IDENT and
      f_p_c_clie4_.BPCL_IDENT=f_polices3_.POL_PTRCLID and (f_polices3_.POL_NUMPOL like :5 ))) )
      where rownum <= :6

      Plan hash value: 1858865683

      -----------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      -----------------------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | | | 909 (100)| |
      | 1 | COUNT STOPKEY | | | | | |
      | 2 | NESTED LOOPS OUTER | | 11 | 1210 | 909 (1)| 00:00:11 |
      | 3 | FILTER | | | | | |
      | 4 | HASH JOIN RIGHT OUTER | | 11 | 1166 | 909 (1)| 00:00:11 |
      | 5 | TABLE ACCESS FULL | T_ETABLISS | 4 | 72 | 3 (0)| 00:00:01 |
      | 6 | NESTED LOOPS | | 88 | 3872 | 905 (1)| 00:00:11 |
      | 7 | VIEW | VW_SQ_1 | 4689 | 60957 | 815 (1)| 00:00:10 |
      | 8 | HASH UNIQUE | | 4689 | 151K| | |
      | 9 | HASH JOIN | | 4689 | 151K| 815 (1)| 00:00:10 |
      | 10 | VIEW | index$_join$_007 | 4666 | 100K| 609 (1)| 00:00:08 |
      | 11 | HASH JOIN | | | | | |
      | 12 | HASH JOIN | | | | | |
      | 13 | INDEX RANGE SCAN | IND_POL_NUMPOL | 4666 | 100K| 6 (17)| 00:00:01 |
      | 14 | INDEX FAST FULL SCAN | IND_POL_PTRCLID | 4666 | 100K| 325 (1)| 00:00:04 |
      | 15 | INDEX FAST FULL SCAN | PK_POL_IDENT | 4666 | 100K| 421 (1)| 00:00:06 |
      | 16 | TABLE ACCESS FULL | F_P_C_CLIENT | 53835 | 578K| 205 (1)| 00:00:03 |
      | 17 | TABLE ACCESS BY INDEX ROWID| F_P_PERSONNE | 1 | 31 | 1 (0)| 00:00:01 |
      | 18 | INDEX UNIQUE SCAN | PK_BPP_IDENT | 1 | | 0 (0)| |
      | 19 | INDEX UNIQUE SCAN | PK_IN_IDENT | 1 | 4 | 0 (0)| |
      -----------------------------------------------------------------------------------------------------

      Query Block Name / Object Alias (identified by operation id):
      -------------------------------------------------------------

      1 - SEL$E1B20FB2
      5 - SEL$E1B20FB2 / T_ETABLISS1_@SEL$2
      7 - SEL$3C459230 / VW_SQ_1@SEL$E00E1480
      8 - SEL$3C459230
      10 - SEL$6A96EA55 / F_POLICES3_@SEL$7
      11 - SEL$6A96EA55
      13 - SEL$6A96EA55 / indexjoin$_alias$_001@SEL$6A96EA55
      14 - SEL$6A96EA55 / indexjoin$_alias$_002@SEL$6A96EA55
      15 - SEL$6A96EA55 / indexjoin$_alias$_003@SEL$6A96EA55
      16 - SEL$3C459230 / F_P_C_CLIE4_@SEL$7
      17 - SEL$E1B20FB2 / F_P_PERSON0_@SEL$3
      18 - SEL$E1B20FB2 / F_P_PERSON0_@SEL$3
      19 - SEL$E1B20FB2 / F_INTERMED2_@SEL$4

      Outline Data
      -------------

      /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OUTLINE_LEAF(@"SEL$6A96EA55")
      OUTLINE_LEAF(@"SEL$3C459230")
      OUTLINE_LEAF(@"SEL$E1B20FB2")
      UNNEST(@"SEL$7")
      OUTLINE(@"SEL$3C459230")
      OUTLINE(@"SEL$7")
      OUTLINE(@"SEL$E00E1480")
      OUTLINE(@"SEL$7C4821CC")
      MERGE(@"SEL$C76F8E91")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$C76F8E91")
      MERGE(@"SEL$829C93BE")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$829C93BE")
      MERGE(@"SEL$4")
      MERGE(@"SEL$64EAE176")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$64EAE176")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      NO_ACCESS(@"SEL$E1B20FB2" "VW_SQ_1"@"SEL$E00E1480")
      INDEX_RS_ASC(@"SEL$E1B20FB2" "F_P_PERSON0_"@"SEL$3" ("F_P_PERSONNE"."BPP_IDENT"))
      FULL(@"SEL$E1B20FB2" "T_ETABLISS1_"@"SEL$2")
      INDEX(@"SEL$E1B20FB2" "F_INTERMED2_"@"SEL$4" ("F_INTERMEDIAIRE"."IN_IDENT"))
      LEADING(@"SEL$E1B20FB2" "VW_SQ_1"@"SEL$E00E1480" "F_P_PERSON0_"@"SEL$3"
      "T_ETABLISS1_"@"SEL$2" "F_INTERMED2_"@"SEL$4")
      USE_NL(@"SEL$E1B20FB2" "F_P_PERSON0_"@"SEL$3")
      USE_HASH(@"SEL$E1B20FB2" "T_ETABLISS1_"@"SEL$2")
      USE_NL(@"SEL$E1B20FB2" "F_INTERMED2_"@"SEL$4")
      SWAP_JOIN_INPUTS(@"SEL$E1B20FB2" "T_ETABLISS1_"@"SEL$2")
      INDEX_JOIN(@"SEL$3C459230" "F_POLICES3_"@"SEL$7" ("F_POLICES"."POL_NUMPOL")
      ("F_POLICES"."POL_PTRCLID") ("F_POLICES"."POL_IDENT"))
      FULL(@"SEL$3C459230" "F_P_C_CLIE4_"@"SEL$7")
      LEADING(@"SEL$3C459230" "F_POLICES3_"@"SEL$7" "F_P_C_CLIE4_"@"SEL$7")
      USE_HASH(@"SEL$3C459230" "F_P_C_CLIE4_"@"SEL$7")
      USE_HASH_AGGREGATION(@"SEL$3C459230")
      END_OUTLINE_DATA
      */

      SQL_ID bh7hrghvacvqn
      --------------------
      select * from ( select f_p_person0_.BPP_IDENT as col_0_0_ from F_P_PERSONNE f_p_person0_
      left outer join T_ETABLISS t_etabliss1_ on f_p_person0_.BPP_PTRETABIDENT=t_etabliss1_.TA_IDEN
      T left outer join F_INTERMEDIAIRE f_intermed2_ on
      f_p_person0_.BPP_PTR_INIDENT=f_intermed2_.IN_IDENT where (f_p_person0_.BPP_PP_ACTIVE is
      null) and (f_p_person0_.BPP_SOCIETE=:1 or f_p_person0_.BPP_SOCIETE=:2 ) and
      (t_etabliss1_.TA_CODE=:3 or t_etabliss1_.TA_IDENT is null) and
      (t_etabliss1_.TA_PTRTDILVID=:4 or t_etabliss1_.TA_IDENT is null) and (exists (select
      f_polices3_.POL_IDENT, f_p_c_clie4_.BPCL_IDENT from F_POLICES f_polices3_, F_P_C_CLIENT
      f_p_c_clie4_ where f_p_c_clie4_.BPCL_PTRBPPIDENT=f_p_person0_.BPP_IDENT and
      f_p_c_clie4_.BPCL_IDENT=f_polices3_.POL_PTRCLID and (f_polices3_.POL_NUMPOL like :5 ))) )
      where rownum <= :6

      Plan hash value: 1942090705

      -----------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      -----------------------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | | | 3954 (100)| |
      | 1 | COUNT STOPKEY | | | | | |
      | 2 | CONCATENATION | | | | | |
      | 3 | FILTER | | | | | |
      | 4 | NESTED LOOPS SEMI | | 1 | 98 | 1977 (1)| 00:00:24 |
      | 5 | FILTER | | | | | |
      | 6 | HASH JOIN RIGHT OUTER | | 1 | 92 | 14 (8)| 00:00:01 |
      | 7 | TABLE ACCESS FULL | T_ETABLISS | 4 | 72 | 3 (0)| 00:00:01 |
      | 8 | NESTED LOOPS OUTER | | 845 | 31265 | 10 (0)| 00:00:01 |
      | 9 | TABLE ACCESS FULL | F_P_PERSONNE | 845 | 27885 | 10 (0)| 00:00:01 |
      | 10 | INDEX UNIQUE SCAN | PK_IN_IDENT | 1 | 4 | 0 (0)| |
      | 11 | VIEW | VW_SQ_1 | 1481 | 8886 | 1963 (1)| 00:00:24 |
      | 12 | HASH JOIN | | 6223 | 218K| 1963 (1)| 00:00:24 |
      | 13 | VIEW | index$_join$_007 | 6223 | 145K| 863 (1)| 00:00:11 |
      | 14 | HASH JOIN | | | | | |
      | 15 | HASH JOIN | | | | | |
      | 16 | INDEX RANGE SCAN | IND_POL_NUMPOL | 6223 | 145K| 30 (4)| 00:00:01 |
      | 17 | INDEX FAST FULL SCAN | IND_POL_PTRCLID | 6223 | 145K| 439 (1)| 00:00:06 |
      | 18 | INDEX FAST FULL SCAN | PK_POL_IDENT | 6223 | 145K| 564 (1)| 00:00:07 |
      | 19 | TABLE ACCESS FULL | F_P_C_CLIENT | 294K| 3446K| 1098 (1)| 00:00:14 |
      | 20 | FILTER | | | | | |
      | 21 | NESTED LOOPS OUTER | | 201 | 21507 | 1977 (1)| 00:00:24 |
      | 22 | FILTER | | | | | |
      | 23 | HASH JOIN RIGHT OUTER | | 201 | 20703 | 1977 (1)| 00:00:24 |
      | 24 | TABLE ACCESS FULL | T_ETABLISS | 4 | 72 | 3 (0)| 00:00:01 |
      | 25 | HASH JOIN SEMI | | 201 | 7839 | 1974 (1)| 00:00:24 |
      | 26 | TABLE ACCESS FULL | F_P_PERSONNE | 845 | 27885 | 10 (0)| 00:00:01 |
      | 27 | VIEW | VW_SQ_1 | 6223 | 37338 | 1963 (1)| 00:00:24 |
      | 28 | HASH JOIN | | 6223 | 218K| 1963 (1)| 00:00:24 |
      | 29 | VIEW | index$_join$_007 | 6223 | 145K| 863 (1)| 00:00:11 |
      | 30 | HASH JOIN | | | | | |
      | 31 | HASH JOIN | | | | | |
      | 32 | INDEX RANGE SCAN | IND_POL_NUMPOL | 6223 | 145K| 30 (4)| 00:00:01 |
      | 33 | INDEX FAST FULL SCAN| IND_POL_PTRCLID | 6223 | 145K| 439 (1)| 00:00:06 |
      | 34 | INDEX FAST FULL SCAN | PK_POL_IDENT | 6223 | 145K| 564 (1)| 00:00:07 |
      | 35 | TABLE ACCESS FULL | F_P_C_CLIENT | 294K| 3446K| 1098 (1)| 00:00:14 |
      | 36 | INDEX UNIQUE SCAN | PK_IN_IDENT | 1 | 4 | 0 (0)| |
      -----------------------------------------------------------------------------------------------------

      Query Block Name / Object Alias (identified by operation id):
      -------------------------------------------------------------

      1 - SEL$E1B20FB2
      7 - SEL$E1B20FB2_1 / T_ETABLISS1_@SEL$2
      9 - SEL$E1B20FB2_1 / F_P_PERSON0_@SEL$3
      10 - SEL$E1B20FB2_1 / F_INTERMED2_@SEL$4
      11 - SEL$3C459230 / VW_SQ_1@SEL$E00E1480
      12 - SEL$3C459230
      13 - SEL$6A96EA55 / F_POLICES3_@SEL$7
      14 - SEL$6A96EA55
      16 - SEL$6A96EA55 / indexjoin$_alias$_001@SEL$6A96EA55
      17 - SEL$6A96EA55 / indexjoin$_alias$_002@SEL$6A96EA55
      18 - SEL$6A96EA55 / indexjoin$_alias$_003@SEL$6A96EA55
      19 - SEL$3C459230 / F_P_C_CLIE4_@SEL$7
      24 - SEL$E1B20FB2_2 / T_ETABLISS1_@SEL$E1B20FB2_2
      26 - SEL$E1B20FB2_2 / F_P_PERSON0_@SEL$E1B20FB2_2
      27 - SEL$3C459230 / VW_SQ_1@SEL$E1B20FB2_2
      29 - SEL$6A96EA55 / F_POLICES3_@SEL$7
      32 - SEL$6A96EA55 / indexjoin$_alias$_001@SEL$6A96EA55
      33 - SEL$6A96EA55 / indexjoin$_alias$_002@SEL$6A96EA55
      34 - SEL$6A96EA55 / indexjoin$_alias$_003@SEL$6A96EA55
      35 - SEL$3C459230 / F_P_C_CLIE4_@SEL$7
      36 - SEL$E1B20FB2_2 / F_INTERMED2_@SEL$E1B20FB2_2

      Outline Data
      -------------

      /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
      OUTLINE_LEAF(@"SEL$6A96EA55")
      OUTLINE_LEAF(@"SEL$3C459230")
      OUTLINE_LEAF(@"SEL$E1B20FB2")
      UNNEST(@"SEL$7")
      OUTLINE_LEAF(@"SEL$E1B20FB2_1")
      USE_CONCAT(@"SEL$E1B20FB2" 8)
      OUTLINE_LEAF(@"SEL$E1B20FB2_2")
      OUTLINE(@"SEL$3C459230")
      OUTLINE(@"SEL$7")
      OUTLINE(@"SEL$E00E1480")
      OUTLINE(@"SEL$E1B20FB2")
      UNNEST(@"SEL$7")
      OUTLINE(@"SEL$7C4821CC")
      MERGE(@"SEL$C76F8E91")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$C76F8E91")
      MERGE(@"SEL$829C93BE")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$829C93BE")
      MERGE(@"SEL$4")
      MERGE(@"SEL$64EAE176")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$64EAE176")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$E1B20FB2_1" "F_P_PERSON0_"@"SEL$3")
      INDEX(@"SEL$E1B20FB2_1" "F_INTERMED2_"@"SEL$4" ("F_INTERMEDIAIRE"."IN_IDENT"))
      FULL(@"SEL$E1B20FB2_1" "T_ETABLISS1_"@"SEL$2")
      NO_ACCESS(@"SEL$E1B20FB2_1" "VW_SQ_1"@"SEL$E00E1480")
      FULL(@"SEL$E1B20FB2_2" "F_P_PERSON0_"@"SEL$E1B20FB2_2")
      NO_ACCESS(@"SEL$E1B20FB2_2" "VW_SQ_1"@"SEL$E1B20FB2_2")
      FULL(@"SEL$E1B20FB2_2" "T_ETABLISS1_"@"SEL$E1B20FB2_2")
      INDEX(@"SEL$E1B20FB2_2" "F_INTERMED2_"@"SEL$E1B20FB2_2" ("F_INTERMEDIAIRE"."IN_IDENT"))
      LEADING(@"SEL$E1B20FB2_1" "F_P_PERSON0_"@"SEL$3" "F_INTERMED2_"@"SEL$4"
      "T_ETABLISS1_"@"SEL$2" "VW_SQ_1"@"SEL$E00E1480")
      LEADING(@"SEL$E1B20FB2_2" "F_P_PERSON0_"@"SEL$E1B20FB2_2" "VW_SQ_1"@"SEL$E1B20FB2_2"
      "T_ETABLISS1_"@"SEL$E1B20FB2_2" "F_INTERMED2_"@"SEL$E1B20FB2_2")
      USE_NL(@"SEL$E1B20FB2_1" "F_INTERMED2_"@"SEL$4")
      USE_HASH(@"SEL$E1B20FB2_1" "T_ETABLISS1_"@"SEL$2")
      USE_NL(@"SEL$E1B20FB2_1" "VW_SQ_1"@"SEL$E00E1480")
      USE_HASH(@"SEL$E1B20FB2_2" "VW_SQ_1"@"SEL$E1B20FB2_2")
      USE_HASH(@"SEL$E1B20FB2_2" "T_ETABLISS1_"@"SEL$E1B20FB2_2")
      USE_NL(@"SEL$E1B20FB2_2" "F_INTERMED2_"@"SEL$E1B20FB2_2")
      SWAP_JOIN_INPUTS(@"SEL$E1B20FB2_1" "T_ETABLISS1_"@"SEL$2")
      SWAP_JOIN_INPUTS(@"SEL$E1B20FB2_2" "T_ETABLISS1_"@"SEL$E1B20FB2_2")
      INDEX_JOIN(@"SEL$3C459230" "F_POLICES3_"@"SEL$7" ("F_POLICES"."POL_NUMPOL")
      ("F_POLICES"."POL_PTRCLID") ("F_POLICES"."POL_IDENT"))
      FULL(@"SEL$3C459230" "F_P_C_CLIE4_"@"SEL$7")
      LEADING(@"SEL$3C459230" "F_POLICES3_"@"SEL$7" "F_P_C_CLIE4_"@"SEL$7")
      USE_HASH(@"SEL$3C459230" "F_P_C_CLIE4_"@"SEL$7")
      END_OUTLINE_DATA
      */

      Peeked Binds (identified by position):
      --------------------------------------

      1 - :1 (VARCHAR2(30), CSID=873): 'S0001'
      2 - :2 (VARCHAR2(30), CSID=873): '*****'
      3 - :3 (VARCHAR2(30), CSID=873): 'GACNC'
      4 - :4 (NUMBER): 1200014
      5 - :5 (VARCHAR2(30), CSID=873): '%18726%'
      6 - :6 (NUMBER): 201


      262 rows selected.