Re: Bind Variable Peeking Not Working Properly

From: Daniel <danielroy10junk_at_hotmail.com>
Date: 8 Dec 2005 07:22:04 -0800
Message-ID: <1134055324.712160.309940@o13g2000cwo.googlegroups.com>

_optim_peek_user_binds is indeed TRUE (the default), and we are using CBO. Here is the exec plan:

---

| Id | Operation | Name
| Rows | Bytes | Cost |

---

| 0 | SELECT STATEMENT |
| 2 | 1822 | 14 |

|*  1 |  FILTER                                   |


|       |       |       |

|   2 |   NESTED LOOPS OUTER                      |

|     2 |  1822 |    14 |

|   3 |    NESTED LOOPS OUTER                     |

|     2 |  1788 |    13 |

|   4 |     NESTED LOOPS OUTER                    |

|     2 |  1754 |    12 |

|   5 |      NESTED LOOPS OUTER                   |

|     2 |  1720 |    11 |

|   6 |       NESTED LOOPS OUTER                  |

|     2 |  1602 |    10 |

|   7 |        NESTED LOOPS OUTER                 |

|     2 |  1452 |     9 |

|   8 |         NESTED LOOPS                      |

|     2 |  1328 |     8 |

|   9 |          NESTED LOOPS OUTER               |

|     2 |  1288 |     7 |

|  10 |           NESTED LOOPS OUTER              |

|     2 |  1222 |     6 |

|  11 |            NESTED LOOPS OUTER             |

|     2 |  1166 |     5 |

|  12 |             NESTED LOOPS OUTER            |

|     2 |  1110 |     4 |

|  13 |              NESTED LOOPS OUTER           |

|     2 |   858 |     3 |

|  14 |               NESTED LOOPS                |

|     2 |   836 |     2 |

|  15 |                TABLE ACCESS BY INDEX ROWID| S_LST_OF_VAL

|     3 |   141 |     1 |

|* 16 |                 INDEX RANGE SCAN          | S_LST_OF_VAL_U1_X


|   453 |       |     5 |

|* 17 |                TABLE ACCESS BY INDEX ROWID| S_ASSET


|     1 |   371 |     1 |

|* 18 |                 INDEX RANGE SCAN          | S_ASSET_V1


|    13 |       |     2 |

|* 19 |               INDEX UNIQUE SCAN           | S_PARTY_P1


|     1 |    11 |     1 |

|  20 |              TABLE ACCESS BY INDEX ROWID  | S_CONTACT

|     1 |   126 |     1 |

|* 21 |               INDEX UNIQUE SCAN           | S_CONTACT_U2


|     1 |       |     1 |

|  22 |             TABLE ACCESS BY INDEX ROWID   | S_CONTACT_BU

|     1 |    28 |     1 |

|* 23 |              INDEX UNIQUE SCAN            | S_CONTACT_BU_P1


|     1 |       |     1 |

|  24 |            TABLE ACCESS BY INDEX ROWID    | S_CONTACT_FNX

|     1 |    28 |     1 |

|* 25 |             INDEX RANGE SCAN              | S_CONTACT_FNX_U1


|     1 |       |     2 |

|  26 |           TABLE ACCESS BY INDEX ROWID     | S_ASSET_CON

|     1 |    33 |     1 |

|* 27 |            INDEX RANGE SCAN               | S_ASSET_CON_U1


|     1 |       |     2 |

|* 28 |          INDEX RANGE SCAN                 | S_ASSET_BU_U1


|     1 |    20 |     2 |

|  29 |         TABLE ACCESS BY INDEX ROWID       | S_PROD_INT

|     1 |    62 |     1 |

|* 30 |          INDEX UNIQUE SCAN                | S_PROD_INT_P1


|     1 |       |       |

|  31 |        TABLE ACCESS BY INDEX ROWID        | S_ASSET_ATX

|     1 |    75 |     1 |

|* 32 |         INDEX RANGE SCAN                  | S_ASSET_ATX_U1


|     1 |       |     2 |

|  33 |       TABLE ACCESS BY INDEX ROWID         | S_VHCL_FIN_DTL

|     1 |    59 |     1 |

|* 34 |        INDEX UNIQUE SCAN                  | S_VHCL_FIN_DTL_P1


|     1 |       |     1 |

|  35 |      TABLE ACCESS BY INDEX ROWID          | S_ORG_EXT

|     1 |    17 |     1 |

|* 36 |       INDEX UNIQUE SCAN                   | S_ORG_EXT_U3


|     1 |       |       |

|  37 |     TABLE ACCESS BY INDEX ROWID           | S_ORG_EXT

|     1 |    17 |     1 |

|* 38 |      INDEX UNIQUE SCAN                    | S_ORG_EXT_U3


|     1 |       |       |

|  39 |    TABLE ACCESS BY INDEX ROWID            | S_ORG_EXT

|     1 |    17 |     1 |

|* 40 |     INDEX UNIQUE SCAN                     | S_ORG_EXT_U3


|     1 |       |       |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   1 - filter(:Z=:Z)
  16 - access("T1"."VAL" LIKE :Z AND
"T1"."TYPE"='AUTO_VEHICLE_OPTY_TYPE_CD' AND

              "T1"."LANG_ID"=:Z)
       filter("T1"."TYPE"='AUTO_VEHICLE_OPTY_TYPE_CD' AND
"T1"."LANG_ID"=:Z AND "T1"."VAL" LIKE
              :Z)
  17 - filter("T14"."REF_NUMBER_2"="T1"."NAME")
  18 - access("T14"."PR_CON_ID"=:Z)
  19 - access("T9"."ROW_ID"(+)=:Z)
       filter("T14"."PR_CON_ID"="T9"."ROW_ID"(+))
  21 - access("T6"."PAR_ROW_ID"(+)=:Z)
       filter("T14"."PR_CON_ID"="T6"."PAR_ROW_ID"(+))
  23 - access("T5"."ROW_ID"(+)=:Z)
       filter("T14"."PR_CON_ID"="T5"."ROW_ID"(+))
  25 - access("T3"."PAR_ROW_ID"(+)=:Z)
       filter("T14"."PR_CON_ID"="T3"."PAR_ROW_ID"(+))

  27 - access("T14"."ROW_ID"="T11"."ASSET_ID"(+) AND "T11"."CONTACT_ID"(+)=:Z)

       filter("T14"."PR_CON_ID"="T11"."CONTACT_ID"(+))
  28 - access("T14"."ROW_ID"="T8"."ASSET_ID" AND "T8"."BU_ID"=:Z)
  30 - access("T14"."PROD_ID"="T2"."ROW_ID"(+))
  32 - access("T14"."ROW_ID"="T7"."PAR_ROW_ID"(+))
  34 - access("T14"."PR_FIN_DTL_ID"="T13"."ROW_ID"(+))
  36 - access("T14"."RTNG_DLR_ID"="T10"."PAR_ROW_ID"(+))
  38 - access("T14"."PREF_SRV_DLR_ID"="T4"."PAR_ROW_ID"(+))
  40 - access("T14"."DLR_ID"="T12"."PAR_ROW_ID"(+))
 

Note: cpu costing is off

Daniel Received on Thu Dec 08 2005 - 09:22:04 CST