18:37:00 SQL> spool xjdx_20090508_topsql_explain.log 18:37:02 SQL> set lines 1000 pages 1000 18:37:02 SQL> alter session set optimizer_mode = first_rows_10; Session altered. Elapsed: 00:00:00.00 18:37:02 SQL> alter session set hash_join_enabled = false; Session altered. Elapsed: 00:00:00.00 18:37:02 SQL> alter session set "_optimizer_sortmerge_join_enabled" = false; Session altered. Elapsed: 00:00:00.00 18:37:02 SQL> alter session set "_optimizer_join_sel_sanity_check" = true; Session altered. Elapsed: 00:00:00.00 18:37:02 SQL> prompt(1) hash_value= 938714578 (1) hash_value= 938714578 18:37:02 SQL> explain plan for 18:37:02 2 SELECT T12.CONFLICT_ID, 18:37:03 3 T12.LAST_UPD, 18:37:03 4 T12.CREATED, 18:37:03 5 T12.LAST_UPD_BY, 18:37:03 6 T12.CREATED_BY, 18:37:03 7 T12.MODIFICATION_NUM, 18:37:03 8 T12.ROW_ID, 18:37:03 9 T12.PROD_CD, 18:37:03 10 T6.ACCRUE_UNIT_FLG, 18:37:03 11 T6.ALW_WAIT_LIST_FLG, 18:37:03 12 T12.X_PROMOTION_NUM, 18:37:03 13 T12.X_SUB_TYPE_CD, 18:37:03 14 T12.CASE_PACK, 18:37:03 15 T12.CATEGORY_CD, 18:37:03 16 T2.ROW_ID, 18:37:03 17 T2.CURR_VER_FLG, 18:37:03 18 T2.RELEASED_FLG, 18:37:03 19 T2.START_DT, 18:37:03 20 T2.VER_NUM, 18:37:03 21 T12.PROD_CD, 18:37:03 22 T12.PROD_ATTRIB02_CD, 18:37:03 23 T12.CONFIGURATION_FLG, 18:37:03 24 T12.CFG_MODEL_ID, 18:37:03 25 T6.CBT_ACCESS_DT_NUM, 18:37:03 26 T6.CRSE_NUM, 18:37:03 27 T6.REG_CMPLTN_CRT_CD, 18:37:03 28 T6.CRSE_RATING, 18:37:03 29 T6.CRSE_STATUS_CD, 18:37:03 30 T6.CRSE_TYPE_CD, 18:37:03 31 T6.CRSE_HOUR_NUM, 18:37:03 32 T12.EFF_END_DT, 18:37:03 33 T6.EXPIRATION_DT, 18:37:03 34 T12.FEATURED_FLG, 18:37:03 35 T10.FCST_QTY_FLG, 18:37:03 36 T6.FORMAT_CD, 18:37:03 37 T8.FILE_AUTO_UPD_FLG, 18:37:03 38 T8.FILE_DATE, 18:37:03 39 T8.FILE_DEFER_FLG, 18:37:03 40 T8.FILE_DOCK_REQ_FLG, 18:37:03 41 T8.FILE_DOCK_STAT_FLG, 18:37:03 42 T8.FILE_EXT, 18:37:03 43 T8.FILE_NAME, 18:37:03 44 T8.FILE_REV_NUM, 18:37:03 45 T8.FILE_SIZE, 18:37:03 46 T8.FILE_SRC_PATH, 18:37:03 47 T8.FILE_SRC_TYPE, 18:37:03 48 T12.ITEM_SIZE, 18:37:03 49 T6.PUBL_MAX_ATTENDEES, 18:37:03 50 T6.MAX_WAIT_LIST_NUM, 18:37:03 51 T12.NAME, 18:37:03 52 T12.NUM_OCCURRENCE, 18:37:03 53 T12.ORDERABLE_FLG, 18:37:03 54 T12.CASE_PACK, 18:37:03 55 T9.DISPLAY_FLG, 18:37:03 56 T12.PAR_PROD_INT_ID, 18:37:03 57 T1.OBJECT_NUM, 18:37:03 58 T12.CG_PR_CTLG_CAT_ID, 18:37:03 59 T1.VOD_NAME, 18:37:03 60 T12.POSTN_BL_PROD_FLG, 18:37:03 61 T12.PRICE_TYPE_CD, 18:37:03 62 T6.PR_CRSE_OFFR_ID, 18:37:03 63 T12.PR_EQUIV_PROD_ID, 18:37:03 64 T12.BU_ID, 18:37:03 65 T12.PR_PROD_LN_ID, 18:37:03 66 T12.PROD_DIST_CD, 18:37:03 67 T12.PROD_CD, 18:37:03 68 T12.PROD_TYPE_CD, 18:37:03 69 T12.PROD_IMAGE_ID, 18:37:03 70 T12.PRODUCT_LEVEL, 18:37:03 71 T12.THMBNL_IMAGE_ID, 18:37:03 72 T12.PROD_CD, 18:37:03 73 T2.RELEASED_FLG, 18:37:03 74 T12.ROOT_PROD_ID, 18:37:03 75 T11.SPN_DEFN_ID, 18:37:03 76 T12.SALES_PROD_FLG, 18:37:03 77 T12.SALES_SRVC_FLG, 18:37:03 78 T12.SERVICE_FLG, 18:37:03 79 T12.SERV_INST_FLG, 18:37:03 80 T12.PROD_CATG_CD, 18:37:03 81 T12.SHIP_FLG, 18:37:03 82 T12.TRANSFERABLE_FLG, 18:37:03 83 T12.EFF_START_DT, 18:37:03 84 T12.PROD_ATTRIB01_CD, 18:37:03 85 T12.DFLT_PROCSYS_ID, 18:37:03 86 T12.TAX_SUBCOMP_FLG, 18:37:03 87 T5.FILE_AUTO_UPD_FLG, 18:37:03 88 T5.FILE_DATE, 18:37:03 89 T5.FILE_DEFER_FLG, 18:37:03 90 T5.FILE_DOCK_REQ_FLG, 18:37:03 91 T5.FILE_DOCK_STAT_FLG, 18:37:03 92 T5.FILE_EXT, 18:37:03 93 T5.FILE_NAME, 18:37:03 94 T5.FILE_REV_NUM, 18:37:03 95 T5.FILE_SIZE, 18:37:03 96 T5.FILE_SRC_PATH, 18:37:03 97 T5.FILE_SRC_TYPE, 18:37:03 98 T12.TOOL_FLG, 18:37:03 99 T12.TYPE, 18:37:03 100 T6.VALUE_UNIT, 18:37:03 101 T12.UOM_CD, 18:37:03 102 T1.OBJECT_NUM, 18:37:03 103 T1.ROW_ID, 18:37:03 104 T12.VENDR_OU_ID, 18:37:03 105 T12.VERSION, 18:37:03 106 T12.STATUS_CD, 18:37:03 107 T1.VOD_TYPE_CD, 18:37:03 108 T3.NAME, 18:37:03 109 T8.ROW_ID, 18:37:03 110 T5.ROW_ID, 18:37:03 111 T6.ROW_ID, 18:37:03 112 T6.MODIFICATION_NUM, 18:37:03 113 T6.CREATED_BY, 18:37:03 114 T6.LAST_UPD_BY, 18:37:03 115 T6.CREATED, 18:37:03 116 T6.LAST_UPD, 18:37:03 117 T6.CONFLICT_ID, 18:37:03 118 T6.PAR_ROW_ID, 18:37:03 119 T10.ROW_ID, 18:37:03 120 T10.MODIFICATION_NUM, 18:37:03 121 T10.CREATED_BY, 18:37:03 122 T10.LAST_UPD_BY, 18:37:03 123 T10.CREATED, 18:37:03 124 T10.LAST_UPD, 18:37:03 125 T10.CONFLICT_ID, 18:37:03 126 T10.PAR_ROW_ID, 18:37:03 127 T3.ROW_ID 18:37:03 128 FROM SIEBEL.S_VOD T1, 18:37:03 129 SIEBEL.S_VOD_VER T2, 18:37:04 130 SIEBEL.S_PROD_LN T3, 18:37:04 131 SIEBEL.S_CTLG_CAT T4, 18:37:04 132 SIEBEL.S_LIT T5, 18:37:04 133 SIEBEL.S_PROD_INT_CRSE T6, 18:37:04 134 SIEBEL.S_CTLG_CAT T7, 18:37:04 135 SIEBEL.S_LIT T8, 18:37:04 136 SIEBEL.S_CTLG_CAT T9, 18:37:04 137 SIEBEL.S_PROD_INT_TNTX T10, 18:37:04 138 SIEBEL.S_ISS_OBJ_DEF T11, 18:37:04 139 SIEBEL.S_PROD_INT T12 18:37:04 140 WHERE T7.PAR_CAT_ID = T4.ROW_ID(+) 18:37:04 141 AND T4.PAR_CAT_ID = T9.ROW_ID(+) 18:37:04 142 AND T12.CG_PR_CTLG_CAT_ID = T7.ROW_ID(+) 18:37:04 143 AND T1.ROW_ID = T11.VOD_ID 18:37:04 144 AND T12.PROD_IMAGE_ID = T8.ROW_ID(+) 18:37:04 145 AND T12.CFG_MODEL_ID = T1.OBJECT_NUM 18:37:04 146 AND T1.ROW_ID = T2.VOD_ID 18:37:04 147 AND T12.THMBNL_IMAGE_ID = T5.ROW_ID(+) 18:37:04 148 AND T12.ROW_ID = T6.PAR_ROW_ID(+) 18:37:04 149 AND T12.ROW_ID = T10.PAR_ROW_ID(+) 18:37:04 150 AND T12.PR_PROD_LN_ID = T3.ROW_ID(+) 18:37:04 151 AND ((T2.RELEASED_FLG = 'Y' AND T2.ACTIVE_FLG = 'Y' AND 18:37:04 152 T2.START_DT <= TO_DATE(:1, 'MM/DD/YYYY HH24:MI:SS') AND 18:37:04 153 (T2.END_DT IS NULL OR 18:37:04 154 T2.END_DT > TO_DATE(:2, 'MM/DD/YYYY HH24:MI:SS')) AND 18:37:04 155 T11.FIRST_VERS <= T2.VER_NUM AND T11.LAST_VERS >= T2.VER_NUM) AND 18:37:04 156 (T12.STATUS_CD = 'zhengzaishiyong') AND 18:37:04 157 (T12.ROW_ID IN 18:37:04 158 (SELECT SQ1_T5.PROD_ID 18:37:04 159 FROM SIEBEL.S_PARTY_CTGRY SQ1_T1, 18:37:04 160 SIEBEL.S_PARTY_PER SQ1_T2, 18:37:04 161 SIEBEL.S_PARTY_RPT_REL SQ1_T3, 18:37:04 162 SIEBEL.S_CTLG SQ1_T4, 18:37:04 163 SIEBEL.S_CTLG_CAT_PROD SQ1_T5, 18:37:04 164 SIEBEL.S_PARTY_PER SQ1_T6, 18:37:04 165 SIEBEL.S_CTLG_CAT SQ1_T7 18:37:04 166 WHERE (SQ1_T7.CTLG_ID = SQ1_T4.ROW_ID AND 18:37:04 167 SQ1_T1.PARTY_ID = SQ1_T3.PARTY_ID AND 18:37:04 168 SQ1_T2.PERSON_ID = SQ1_T6.PARTY_ID AND 18:37:04 169 SQ1_T3.SUB_PARTY_ID = SQ1_T2.PARTY_ID AND 18:37:04 170 SQ1_T7.ROW_ID = SQ1_T1.CTLG_CAT_ID AND 18:37:04 171 SQ1_T5.CTLG_CAT_ID = SQ1_T7.ROW_ID) 18:37:04 172 AND (SQ1_T7.ACTIVE_FLG = 'Y' AND SQ1_T4.ACTIVE_FLG = 'Y') 18:37:04 173 AND (SQ1_T6.PERSON_ID = :3)) OR 18:37:04 174 T12.ROW_ID IN 18:37:04 175 (SELECT SQ1_T6.PROD_ID 18:37:04 176 FROM SIEBEL.S_PARTY_CTGRY SQ1_T1, 18:37:04 177 SIEBEL.S_PARTY_PER SQ1_T2, 18:37:04 178 SIEBEL.S_PARTY_RPT_REL SQ1_T3, 18:37:04 179 SIEBEL.S_CTLG SQ1_T4, 18:37:04 180 SIEBEL.S_POSTN SQ1_T5, 18:37:04 181 SIEBEL.S_CTLG_CAT_PROD SQ1_T6, 18:37:04 182 SIEBEL.S_PARTY_PER SQ1_T7, 18:37:04 183 SIEBEL.S_CTLG_CAT SQ1_T8 18:37:04 184 WHERE (SQ1_T8.CTLG_ID = SQ1_T4.ROW_ID AND 18:37:04 185 SQ1_T1.PARTY_ID = SQ1_T3.PARTY_ID AND 18:37:04 186 SQ1_T5.ROW_ID = SQ1_T7.PARTY_ID AND 18:37:04 187 SQ1_T2.PERSON_ID = SQ1_T5.BU_ID AND 18:37:04 188 SQ1_T3.SUB_PARTY_ID = SQ1_T2.PARTY_ID AND 18:37:04 189 SQ1_T8.ROW_ID = SQ1_T1.CTLG_CAT_ID AND 18:37:04 190 SQ1_T6.CTLG_CAT_ID = SQ1_T8.ROW_ID) 18:37:04 191 AND (SQ1_T8.ACTIVE_FLG = 'Y' AND SQ1_T4.ACTIVE_FLG = 'Y') 18:37:04 192 AND (SQ1_T7.PERSON_ID = :4)) OR 18:37:04 193 T12.ROW_ID IN 18:37:04 194 (SELECT SQ1_T2.PROD_ID 18:37:04 195 FROM SIEBEL.S_CTLG SQ1_T1, 18:37:04 196 SIEBEL.S_CTLG_CAT_PROD SQ1_T2, 18:37:04 197 SIEBEL.S_CTLG_CAT SQ1_T3 18:37:04 198 WHERE (SQ1_T3.CTLG_ID = SQ1_T1.ROW_ID AND 18:37:04 199 SQ1_T2.CTLG_CAT_ID = SQ1_T3.ROW_ID) 18:37:04 200 AND (SQ1_T3.ACTIVE_FLG = 'Y' AND SQ1_T1.ACTIVE_FLG = 'Y') 18:37:04 201 AND (SQ1_T3.PRIV_FLG = 'N'))) AND 18:37:04 202 (T12.PROD_CD = 'zuheshangping' AND T12.PROMO_TYPE_CD = 'kubancuxiao' OR 18:37:04 203 T12.PROD_CD = 'danyishangping')) 18:37:04 204 AND (T12.NAME LIKE :5); select * from table(dbms_xplan.display); Explained. Elapsed: 00:00:00.36 18:37:04 SQL> PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1956 | 13 | |* 1 | FILTER | | | | | | 2 | NESTED LOOPS OUTER | | 1 | 1956 | 13 | | 3 | NESTED LOOPS OUTER | | 1 | 1942 | 12 | | 4 | NESTED LOOPS OUTER | | 1 | 1924 | 11 | | 5 | NESTED LOOPS | | 1 | 1906 | 10 | | 6 | NESTED LOOPS | | 1 | 1856 | 9 | | 7 | NESTED LOOPS | | 5 | 9055 | 8 | | 8 | NESTED LOOPS OUTER | | 5 | 8575 | 7 | | 9 | NESTED LOOPS OUTER | | 5 | 8280 | 6 | | 10 | NESTED LOOPS OUTER | | 5 | 7785 | 5 | | 11 | NESTED LOOPS OUTER | | 5 | 7290 | 4 | | 12 | NESTED LOOPS OUTER | | 5 | 4130 | 3 | |* 13 | TABLE ACCESS BY INDEX ROWID| S_PROD_INT | 5 | 2960 | 2 | |* 14 | INDEX RANGE SCAN | S_PROD_INT_M53 | 326 | | 5 | | 15 | TABLE ACCESS BY INDEX ROWID| S_PROD_LN | 1 | 234 | 1 | |* 16 | INDEX UNIQUE SCAN | S_PROD_LN_P1 | 1 | | | | 17 | TABLE ACCESS BY INDEX ROWID | S_PROD_INT_CRSE | 1 | 632 | 1 | |* 18 | INDEX RANGE SCAN | S_PROD_INT_CRSE_U1 | 1 | | 1 | | 19 | TABLE ACCESS BY INDEX ROWID | S_LIT | 1 | 99 | 1 | |* 20 | INDEX UNIQUE SCAN | S_LIT_P1 | 1 | | | | 21 | TABLE ACCESS BY INDEX ROWID | S_LIT | 1 | 99 | 1 | |* 22 | INDEX UNIQUE SCAN | S_LIT_P1 | 1 | | | | 23 | TABLE ACCESS BY INDEX ROWID | S_PROD_INT_TNTX | 1 | 59 | 1 | |* 24 | INDEX RANGE SCAN | S_PROD_INT_TNTX_U1 | 1 | | 1 | | 25 | TABLE ACCESS BY INDEX ROWID | S_VOD | 1 | 96 | 1 | |* 26 | INDEX RANGE SCAN | S_VOD_U2 | 1 | | 2 | |* 27 | TABLE ACCESS BY INDEX ROWID | S_VOD_VER | 1 | 45 | 1 | |* 28 | INDEX RANGE SCAN | S_VOD_VER_U1 | 2 | | 4 | | 29 | TABLE ACCESS BY INDEX ROWID | S_ISS_OBJ_DEF | 1 | 50 | 1 | |* 30 | INDEX RANGE SCAN | S_ISS_OBJ_DEF_M3 | 1 | | 2 | | 31 | TABLE ACCESS BY INDEX ROWID | S_CTLG_CAT | 1 | 18 | 1 | |* 32 | INDEX UNIQUE SCAN | S_CTLG_CAT_P1 | 1 | | | | 33 | TABLE ACCESS BY INDEX ROWID | S_CTLG_CAT | 1 | 18 | 1 | |* 34 | INDEX UNIQUE SCAN | S_CTLG_CAT_P1 | 1 | | | | 35 | TABLE ACCESS BY INDEX ROWID | S_CTLG_CAT | 1 | 14 | 1 | |* 36 | INDEX UNIQUE SCAN | S_CTLG_CAT_P1 | 1 | | | | 37 | NESTED LOOPS | | 1 | 137 | 7 | | 38 | NESTED LOOPS | | 4 | 456 | 6 | | 39 | NESTED LOOPS | | 4 | 364 | 5 | | 40 | NESTED LOOPS | | 2 | 142 | 4 | | 41 | NESTED LOOPS | | 1 | 53 | 3 | | 42 | NESTED LOOPS | | 3 | 120 | 2 | |* 43 | INDEX RANGE SCAN | S_CTLG_CAT_PROD_U1 | 3 | 57 | 2 | |* 44 | TABLE ACCESS BY INDEX ROWID | S_CTLG_CAT | 1 | 21 | 1 | |* 45 | INDEX UNIQUE SCAN | S_CTLG_CAT_P1 | 1 | | | |* 46 | TABLE ACCESS BY INDEX ROWID | S_CTLG | 1 | 13 | 1 | |* 47 | INDEX UNIQUE SCAN | S_CTLG_P1 | 1 | | | | 48 | TABLE ACCESS BY INDEX ROWID | S_PARTY_CTGRY | 2 | 36 | 1 | |* 49 | INDEX RANGE SCAN | S_PARTY_CTGRY_F1 | 2 | | 1 | | 50 | TABLE ACCESS BY INDEX ROWID | S_PARTY_RPT_REL | 2 | 40 | 1 | |* 51 | INDEX RANGE SCAN | S_PARTY_RPTREL_F50 | 2 | | 1 | | 52 | TABLE ACCESS BY INDEX ROWID | S_PARTY_PER | 1 | 23 | 1 | |* 53 | INDEX RANGE SCAN | S_PARTY_PER_F1 | 1 | | 2 | |* 54 | INDEX RANGE SCAN | S_PARTY_PER_U1 | 1 | 23 | 3 | | 55 | NESTED LOOPS | | 1 | 157 | 8 | | 56 | NESTED LOOPS | | 1 | 144 | 7 | | 57 | NESTED LOOPS | | 1 | 125 | 6 | | 58 | NESTED LOOPS | | 1 | 104 | 5 | | 59 | NESTED LOOPS | | 1 | 86 | 4 | | 60 | NESTED LOOPS | | 4 | 264 | 3 | | 61 | NESTED LOOPS | | 2 | 86 | 2 | |* 62 | INDEX RANGE SCAN | S_PARTY_PER_U1 | 2 | 46 | 4 | | 63 | TABLE ACCESS BY INDEX ROWID | S_POSTN | 1 | 20 | 1 | |* 64 | INDEX UNIQUE SCAN | S_POSTN_P1 | 1 | | | |* 65 | INDEX RANGE SCAN | S_PARTY_PER_U1 | 2 | 46 | 3 | |* 66 | INDEX RANGE SCAN | S_PARTY_RPT_REL_U2 | 1 | 20 | 1 | |* 67 | INDEX RANGE SCAN | S_PARTY_CTGRY_U1 | 1 | 18 | 1 | |* 68 | TABLE ACCESS BY INDEX ROWID | S_CTLG_CAT | 1 | 21 | 1 | |* 69 | INDEX UNIQUE SCAN | S_CTLG_CAT_P1 | 1 | | | |* 70 | INDEX RANGE SCAN | S_CTLG_CAT_PROD_U1 | 1 | 19 | 1 | |* 71 | TABLE ACCESS BY INDEX ROWID | S_CTLG | 1 | 13 | 1 | |* 72 | INDEX UNIQUE SCAN | S_CTLG_P1 | 1 | | | | 73 | NESTED LOOPS | | 1 | 58 | 3 | | 74 | NESTED LOOPS | | 3 | 135 | 2 | |* 75 | INDEX RANGE SCAN | S_CTLG_CAT_PROD_U1 | 2 | 38 | 2 | |* 76 | TABLE ACCESS BY INDEX ROWID | S_CTLG_CAT | 1 | 26 | 1 | |* 77 | INDEX UNIQUE SCAN | S_CTLG_CAT_P1 | 1 | | | |* 78 | TABLE ACCESS BY INDEX ROWID | S_CTLG | 1 | 13 | 1 | |* 79 | INDEX UNIQUE SCAN | S_CTLG_P1 | 1 | | | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS (SELECT /*+ */ 0 FROM "SIEBEL"."S_CTLG_CAT" "SQ1_T7","SIEBEL"."S_PARTY_PER" "SQ1_T6","SIEBEL"."S_CTLG_CAT_PROD" "SQ1_T5","SIEBEL"."S_CTLG" "SQ1_T4","SIEBEL"."S_PARTY_RPT_REL" "SQ1_T3","SIEBEL"."S_PARTY_PER" "SQ1_T2","SIEBEL"."S_PARTY_CTGRY" "SQ1_T1" WHERE "SQ1_T7"."ROW_ID"="SQ1_T1"."CTLG_CAT_ID" AND "SQ1_T3"."SUB_PARTY_ID"="SQ1_T2"."PARTY_ID" AND "SQ1_T1"."PARTY_ID"="SQ1_T3"."PARTY_ID" AND "SQ1_T7"."CTLG_ID"="SQ1_T4"."ROW_ID" AND "SQ1_T4"."ACTIVE_FLG"='Y' AND "SQ1_T5"."PROD_ID"=:B1 AND "SQ1_T2"."PERSON_ID"="SQ1_T6"."PARTY_ID" AND "SQ1_T6"."PERSON_ID"=:Z AND "SQ1_T5"."CTLG_CAT_ID"="SQ1_T7"."ROW_ID" AND "SQ1_T7"."ACTIVE_FLG"='Y') OR EXISTS (SELECT /*+ */ 0 FROM "SIEBEL"."S_CTLG_CAT" "SQ1_T8","SIEBEL"."S_PARTY_PER" "SQ1_T7","SIEBEL"."S_CTLG_CAT_PROD" "SQ1_T6","SIEBEL"."S_POSTN" "SQ1_T5","SIEBEL"."S_CTLG" "SQ1_T4","SIEBEL"."S_PARTY_RPT_REL" "SQ1_T3","SIEBEL"."S_PARTY_PER" "SQ1_T2","SIEBEL"."S_PARTY_CTGRY" "SQ1_T1" WHERE "SQ1_T1"."PARTY_ID"="SQ1_T3"."PARTY_ID" AND "SQ1_T2"."PERSON_ID"="SQ1_T5"."BU_ID" AND "SQ1_T3"."SUB_PARTY_ID"="SQ1_T2"."PARTY_ID" AND "SQ1_T8"."CTLG_ID"="SQ1_T4"."ROW_ID" AND "SQ1_T4"."ACTIVE_FLG"='Y' AND "SQ1_T5"."ROW_ID"="SQ1_T7"."PARTY_ID" AND "SQ1_T6"."CTLG_CAT_ID"="SQ1_T8"."ROW_ID" AND "SQ1_T6"."PROD_ID"=:B2 AND "SQ1_T7"."PERSON_ID"=:Z AND "SQ1_T8"."ROW_ID"="SQ1_T1"."CTLG_CAT_ID" AND "SQ1_T8"."ACTIVE_FLG"='Y') OR EXISTS (SELECT /*+ */ 0 FROM "SIEBEL"."S_CTLG_CAT" "SQ1_T3","SIEBEL"."S_CTLG_CAT_PROD" "SQ1_T2","SIEBEL"."S_CTLG" "SQ1_T1" WHERE "SQ1_T3"."CTLG_ID"="SQ1_T1"."ROW_ID" AND "SQ1_T1"."ACTIVE_FLG"='Y' AND "SQ1_T2"."PROD_ID"=:B3 AND "SQ1_T2"."CTLG_CAT_ID"="SQ1_T3"."ROW_ID" AND "SQ1_T3"."ACTIVE_FLG"='Y' AND "SQ1_T3"."PRIV_FLG"='N')) 13 - filter("SYS_ALIAS_3"."STATUS_CD"='zhengzaishiyong' AND ("SYS_ALIAS_3"."PROD_CD"='zuheshangping' AND "SYS_ALIAS_3"."PROMO_TYPE_CD"='kubancuxiao' OR "SYS_ALIAS_3"."PROD_CD"='danyishangping')) 14 - access("SYS_ALIAS_3"."NAME" LIKE :Z) filter("SYS_ALIAS_3"."NAME" LIKE :Z) 16 - access("SYS_ALIAS_3"."PR_PROD_LN_ID"="T3"."ROW_ID"(+)) 18 - access("SYS_ALIAS_3"."ROW_ID"="T6"."PAR_ROW_ID"(+)) 20 - access("SYS_ALIAS_3"."THMBNL_IMAGE_ID"="T5"."ROW_ID"(+)) 22 - access("SYS_ALIAS_3"."PROD_IMAGE_ID"="T8"."ROW_ID"(+)) 24 - access("SYS_ALIAS_3"."ROW_ID"="T10"."PAR_ROW_ID"(+)) 26 - access("SYS_ALIAS_3"."CFG_MODEL_ID"="T1"."OBJECT_NUM") 27 - filter("T2"."RELEASED_FLG"='Y' AND "T2"."ACTIVE_FLG"='Y' AND "T2"."START_DT"<=TO_DATE(:Z,'MM/DD/YYYY HH24:MI:SS') AND ("T2"."END_DT" IS NULL OR "T2"."END_DT">TO_DATE(:Z,'MM/DD/YYYY HH24:MI:SS'))) 28 - access("T1"."ROW_ID"="T2"."VOD_ID") 30 - access("T1"."ROW_ID"="T11"."VOD_ID" AND "T11"."LAST_VERS">="T2"."VER_NUM" AND "T11"."FIRST_VERS"<="T2"."VER_NUM") filter("T11"."FIRST_VERS"<="T2"."VER_NUM") 32 - access("SYS_ALIAS_3"."CG_PR_CTLG_CAT_ID"="T7"."ROW_ID"(+)) 34 - access("T7"."PAR_CAT_ID"="T4"."ROW_ID"(+)) 36 - access("T4"."PAR_CAT_ID"="T9"."ROW_ID"(+)) 43 - access("SQ1_T5"."PROD_ID"=:B1) 44 - filter("SQ1_T7"."ACTIVE_FLG"='Y') 45 - access("SQ1_T5"."CTLG_CAT_ID"="SQ1_T7"."ROW_ID") 46 - filter("SQ1_T4"."ACTIVE_FLG"='Y') 47 - access("SQ1_T7"."CTLG_ID"="SQ1_T4"."ROW_ID") 49 - access("SQ1_T7"."ROW_ID"="SQ1_T1"."CTLG_CAT_ID") 51 - access("SQ1_T1"."PARTY_ID"="SQ1_T3"."PARTY_ID") 53 - access("SQ1_T3"."SUB_PARTY_ID"="SQ1_T2"."PARTY_ID") 54 - access("SQ1_T6"."PERSON_ID"=:Z AND "SQ1_T2"."PERSON_ID"="SQ1_T6"."PARTY_ID") 62 - access("SQ1_T7"."PERSON_ID"=:Z) 64 - access("SQ1_T5"."ROW_ID"="SQ1_T7"."PARTY_ID") 65 - access("SQ1_T2"."PERSON_ID"="SQ1_T5"."BU_ID") 66 - access("SQ1_T3"."SUB_PARTY_ID"="SQ1_T2"."PARTY_ID") 67 - access("SQ1_T1"."PARTY_ID"="SQ1_T3"."PARTY_ID") 68 - filter("SQ1_T8"."ACTIVE_FLG"='Y') 69 - access("SQ1_T8"."ROW_ID"="SQ1_T1"."CTLG_CAT_ID") 70 - access("SQ1_T6"."PROD_ID"=:B1 AND "SQ1_T6"."CTLG_CAT_ID"="SQ1_T8"."ROW_ID") 71 - filter("SQ1_T4"."ACTIVE_FLG"='Y') 72 - access("SQ1_T8"."CTLG_ID"="SQ1_T4"."ROW_ID") 75 - access("SQ1_T2"."PROD_ID"=:B1) 76 - filter("SQ1_T3"."ACTIVE_FLG"='Y' AND "SQ1_T3"."PRIV_FLG"='N') 77 - access("SQ1_T2"."CTLG_CAT_ID"="SQ1_T3"."ROW_ID") 78 - filter("SQ1_T1"."ACTIVE_FLG"='Y') 79 - access("SQ1_T3"."CTLG_ID"="SQ1_T1"."ROW_ID") Note: cpu costing is off 157 rows selected. Elapsed: 00:00:03.64 18:37:10 SQL> SELECT count(sid), event 18:44:51 2 FROM v$session_wait 18:44:51 3 WHERE event NOT LIKE 'SQL%' 18:44:51 4 AND event NOT LIKE 'rdbms%' 18:44:51 5 AND event NOT LIKE '%mon%' 18:44:51 6 AND event NOT LIKE '%remote%' 18:44:51 7 group by event 18:44:51 8 order by 1; COUNT(SID) EVENT ---------- ---------------------------------------------------------------- 1 PL/SQL lock timer 8 db file sequential read 12 wait list latch free 558 latch free Elapsed: 00:00:00.07 18:44:52 SQL> / COUNT(SID) EVENT ---------- ---------------------------------------------------------------- 1 PL/SQL lock timer 2 wait list latch free 7 db file sequential read 567 latch free Elapsed: 00:00:00.05 18:44:53 SQL> / COUNT(SID) EVENT ---------- ---------------------------------------------------------------- 1 PL/SQL lock timer 1 global cache cr request 1 log file sync 1 global cache null to x 2 buffer busy global cache 8 db file sequential read 16 wait list latch free 555 latch free 8 rows selected. Elapsed: 00:00:00.03 18:44:54 SQL> / COUNT(SID) EVENT ---------- ---------------------------------------------------------------- 1 CGS wait for IPC msg 1 PL/SQL lock timer 1 global cache cr request 5 db file sequential read 19 wait list latch free 555 latch free 6 rows selected. Elapsed: 00:00:00.04 18:44:54 SQL> / COUNT(SID) EVENT ---------- ---------------------------------------------------------------- 1 PL/SQL lock timer 1 db file sequential read 1 global cache cr request 1 log file sync 10 wait list latch free 563 latch free 6 rows selected. Elapsed: 00:00:00.04 18:44:55 SQL> / COUNT(SID) EVENT ---------- ---------------------------------------------------------------- 1 PL/SQL lock timer 1 global cache s to x 1 log file sync 2 wait list latch free 7 db file sequential read 571 latch free 6 rows selected. Elapsed: 00:00:00.03 18:44:55 SQL> / COUNT(SID) EVENT ---------- ---------------------------------------------------------------- 1 PL/SQL lock timer 3 wait list latch free 6 db file sequential read 572 latch free Elapsed: 00:00:00.03 18:44:56 SQL> / COUNT(SID) EVENT ---------- ---------------------------------------------------------------- 1 PL/SQL lock timer 1 global cache cr request 3 wait list latch free 5 db file sequential read 573 latch free Elapsed: 00:00:00.04 18:44:56 SQL> host