SQL> SELECT * FROM table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2686893296 -------------------------------------------------------------------------------- --------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- --------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 356 | 107 (4)| 00:00:02 | | 1 | SORT UNIQUE | | 1 | 356 | 106 (3)| 00:00:02 | | 2 | NESTED LOOPS | | | | | | | 3 | NESTED LOOPS | | 1 | 356 | 105 (2)| 00:00:02 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 4 | NESTED LOOPS | | 1 | 348 | 104 (2)| 00:00:02 | | 5 | NESTED LOOPS | | 1 | 324 | 102 (2)| 00:00:02 | | 6 | NESTED LOOPS | | 1 | 308 | 100 (2)| 00:00:02 | | 7 | NESTED LOOPS | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 1 | 297 | 99 (3)| 00:00:02 | | 8 | NESTED LOOPS | | 1 | 285 | 98 (3)| 00:00:02 | | 9 | NESTED LOOPS | | 1 | 273 | 93 (3)| 00:00:02 | | 10 | NESTED LOOPS | | 1 | 253 | 91 (3)| 00:00:02 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 11 | NESTED LOOPS | | 1 | 235 | 90 (3)| 00:00:02 | | 12 | NESTED LOOPS | | 1 | 206 | 86 (3)| 00:00:02 | | 13 | NESTED LOOPS | | 1 | 185 | 85 (3)| 00:00:02 | | 14 | NESTED LOOPS OUTER | | 1 | 176 | 84 (3)| 00:00:02 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 15 | NESTED LOOPS | | 1 | 166 | 83 (3)| 00:00:01 | | 16 | NESTED LOOPS | | 1 | 157 | 82 (3)| 00:00:01 | |* 17 | HASH JOIN | | 1 | 149 | 81 (3)| 00:00:01 | | 18 | TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_AS PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SIGNMENTS | 42 | 420 | 4 (0)| 00:00:01 | |* 19 | INDEX RANGE SCAN | WSH_DELIVERY_AS SIGNMENTS_N1 | 42 | | 3 (0)| 00:00:01 | | 20 | NESTED LOOPS | | | | | | | 21 | NESTED LOOPS | | 132 | 18348 | 77 (3)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 22 | NESTED LOOPS | | 4 | 388 | 55 (2)| 00:00:01 | |* 23 | HASH JOIN | | 1 | 82 | 49 (3)| 00:00:01 | | 24 | NESTED LOOPS | | | | | | | 25 | NESTED LOOPS | | 1 | 61 | 27 (4)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- |* 26 | HASH JOIN | | 2 | 106 | 26 (4)| 00:00:01 | | 27 | NESTED LOOPS | | | | | | | 28 | NESTED LOOPS | | 8 | 200 | 16 (0)| 00:00:01 | | 29 | NESTED LOOPS | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 8 | 136 | 8 (0)| 00:00:01 | | 30 | TABLE ACCESS BY INDEX ROWID| WSH_NEW_DELIVER IES | 1 | 10 | 2 (0)| 00:00:01 | |* 31 | INDEX UNIQUE SCAN | WSH_NEW_DELIVER IES_U1 | 1 | | 1 (0)| 00:00:01 | |* 32 | INDEX SKIP SCAN | HR_ALL_ORGANIZA TION_UNTS_TL_PK | 8 | 56 | 6 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- |* 33 | INDEX UNIQUE SCAN | HR_ORGANIZATION _UNITS_PK | 1 | | 0 (0)| 00:00:01 | |* 34 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZA TION_UNITS | 1 | 8 | 1 (0)| 00:00:01 | |* 35 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION _INFORMATION | 18 | 504 | 9 (0)| 00:00:01 | |* 36 | INDEX RANGE SCAN | HR_ORGANIZATION _INFORMATIO_IX1 | 47 | | 1 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- |* 37 | INDEX UNIQUE SCAN | MTL_PARAMETERS_ U1 | 1 | | 0 (0)| 00:00:01 | | 38 | TABLE ACCESS BY INDEX ROWID | MTL_PARAMETERS | 1 | 8 | 1 (0)| 00:00:01 | |* 39 | TABLE ACCESS FULL | OE_ORDER_HEADER S_ALL | 31 | 651 | 22 (0)| 00:00:01 | | 40 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_LINES_ PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ALL | 12 | 180 | 6 (0)| 00:00:01 | |* 41 | INDEX RANGE SCAN | OE_ORDER_LINES_ N1 | 12 | | 1 (0)| 00:00:01 | |* 42 | INDEX RANGE SCAN | WSH_DELIVERY_DE TAILS_N3 | 41 | | 2 (0)| 00:00:01 | | 43 | TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_DE TAILS | 34 | 1428 | 6 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 44 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_SITE_US ES_ALL | 1 | 8 | 1 (0)| 00:00:01 | |* 45 | INDEX UNIQUE SCAN | HZ_CUST_SITE_US ES_U1 | 1 | | 0 (0)| 00:00:01 | | 46 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCT_SI TES_ALL | 1 | 9 | 1 (0)| 00:00:01 | |* 47 | INDEX UNIQUE SCAN | HZ_CUST_ACCT_SI TES_U1 | 1 | | 0 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 48 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNT S | 1 | 10 | 1 (0)| 00:00:01 | |* 49 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNT S_U1 | 1 | | 0 (0)| 00:00:01 | |* 50 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEM S_B_U1 | 1 | 9 | 1 (0)| 00:00:01 | |* 51 | TABLE ACCESS BY INDEX ROWID | MTL_DESCR_ELEME PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- NT_VALUES | 1 | 21 | 1 (0)| 00:00:01 | |* 52 | INDEX UNIQUE SCAN | MTL_DESCR_ELEME NT_VALUES_U1 | 1 | | 0 (0)| 00:00:01 | |* 53 | TABLE ACCESS BY INDEX ROWID | MTL_LOT_NUMBERS | 1 | 29 | 4 (0)| 00:00:01 | |* 54 | INDEX RANGE SCAN | MTL_LOT_NUMBERS _N1 | 2 | | 2 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 55 | TABLE ACCESS BY INDEX ROWID | XXGMD_PM_DROPS | 1 | 18 | 1 (0)| 00:00:01 | |* 56 | INDEX UNIQUE SCAN | XXGMD_PM_DRP_UK | 1 | | 0 (0)| 00:00:01 | |* 57 | TABLE ACCESS BY INDEX ROWID | XXGMD_PM_CSPECS | 1 | 20 | 2 (0)| 00:00:01 | |* 58 | INDEX RANGE SCAN | XXGMD_PM_CSP_UK | 1 | | 1 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 59 | TABLE ACCESS BY INDEX ROWID | XXGMD_PM_CSPEC_ ELEMENTS | 20 | 240 | 5 (0)| 00:00:01 | |* 60 | INDEX RANGE SCAN | XXGMD_PM_CSE_UK | 20 | | 1 (0)| 00:00:01 | |* 61 | TABLE ACCESS BY INDEX ROWID | XXGMD_PM_ELEMEN TS | 1 | 12 | 1 (0)| 00:00:01 | |* 62 | INDEX UNIQUE SCAN | XXGMD_PM_ELM_PK PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- | 1 | | 0 (0)| 00:00:01 | |* 63 | TABLE ACCESS BY INDEX ROWID | XXGMD_PM_CSPEC_ ELEMENT_RULES | 1 | 11 | 1 (0)| 00:00:01 | |* 64 | INDEX UNIQUE SCAN | XXGMD_PM_CSR_UK | 1 | | 0 (0)| 00:00:01 | | 65 | TABLE ACCESS BY INDEX ROWID | XXGMD_PM_DROP_C HEM_RESULTS | 1 | 16 | 2 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- |* 66 | INDEX RANGE SCAN | XXGMD_PM_DCR_UK | 1 | | 1 (0)| 00:00:01 | | 67 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION _INFORMATION | 1 | 24 | 2 (0)| 00:00:01 | |* 68 | INDEX RANGE SCAN | HR_ORGANIZATION _INFORMATIO_FK2 | 1 | | 1 (0)| 00:00:01 | |* 69 | INDEX UNIQUE SCAN | GL_LEDGERS_U2 | 1 | | 0 (0)| 00:00:01 | PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- |* 70 | TABLE ACCESS BY INDEX ROWID | GL_LEDGERS | 1 | 8 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- --------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 17 - access("WDD"."DELIVERY_DETAIL_ID"="WDA"."DELIVERY_DETAIL_ID") 19 - access("WDA"."DELIVERY_ID"=TO_NUMBER(:P_DELIVERY_ID)) 23 - access("OOHA"."SHIP_FROM_ORG_ID"="HAO"."ORGANIZATION_ID") 26 - access("HAO"."ORGANIZATION_ID"="HOI1"."ORGANIZATION_ID") 31 - access("WND"."DELIVERY_ID"=TO_NUMBER(:P_DELIVERY_ID)) 32 - access("HAOTL"."LANGUAGE"=USERENV('LANG')) filter("HAOTL"."LANGUAGE"=USERENV('LANG') AND DECODE("HR_SECURITY"."VIEW_ ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_RECORD"('HR _ALL_ORGANIZATION_UNITS',"HAOTL"."ORGANIZATION_ID"))='TRUE') 33 - access("HAO"."ORGANIZATION_ID"="HAOTL"."ORGANIZATION_ID") PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 34 - filter("HAO"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(), 'Y',"HAO"."BUSINESS_GROUP_ID","HR_GENERAL"."GET_B USINESS_GROUP_ID"())) 35 - filter("HOI1"."ORG_INFORMATION2"='Y' AND "HOI1"."ORG_INFORMATION_CONTEXT" ||''='CLASS') 36 - access("HOI1"."ORG_INFORMATION1"='INV') 37 - access("HAO"."ORGANIZATION_ID"="MP"."ORGANIZATION_ID") 39 - filter("OOHA"."DELIVER_TO_ORG_ID" IS NOT NULL) 41 - access("OOLA"."HEADER_ID"="OOHA"."HEADER_ID") PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 42 - access("WDD"."SOURCE_LINE_ID"="OOLA"."LINE_ID") 45 - access("OOHA"."DELIVER_TO_ORG_ID"="SHIP_SU"."SITE_USE_ID") 47 - access("SHIP_CAS"."CUST_ACCT_SITE_ID"="SHIP_SU"."CUST_ACCT_SITE_ID") 49 - access("OOHA"."SOLD_TO_ORG_ID"="CUST_ACCT"."CUST_ACCOUNT_ID"(+)) 50 - access("MSIB"."INVENTORY_ITEM_ID"="OOLA"."INVENTORY_ITEM_ID" AND "MSIB"." ORGANIZATION_ID"="WDD"."ORGANIZATION_ID") 51 - filter("MDEV"."ELEMENT_VALUE" IS NOT NULL) 52 - access("MSIB"."INVENTORY_ITEM_ID"="MDEV"."INVENTORY_ITEM_ID" AND "MDEV"." ELEMENT_NAME"='C-ALLOY') PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 53 - filter("MLN"."INVENTORY_ITEM_ID"="WDD"."INVENTORY_ITEM_ID") 54 - access("MLN"."LOT_NUMBER"="WDD"."LOT_NUMBER") 56 - access("XPD"."PLANT_CODE"="MP"."ORGANIZATION_CODE" AND "XPD"."DROP_NUMBER "="MLN"."PARENT_LOT_NUMBER") 57 - filter("CSPEC"."EFFECTIVE_START_DATE"<=SYSDATE@! AND NVL("CSPEC"."EFFECTI VE_END_DATE",SYSDATE@!+500)>=SYSDATE@!) 58 - access("CSPEC"."CSPEC_NAME"="MDEV"."ELEMENT_VALUE") 60 - access("CSPEC"."CSPEC_ID"="CSPEC_EL"."CSPEC_ID") 61 - filter(UPPER("ELEMENT_NAME")<>'AL') PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 62 - access("CSPEC_EL"."ELEMENT_ID"="ELEMENT"."ELEMENT_ID") 63 - filter("CSPEC_RULE"."PRINT_ELEMENT"='Y') 64 - access("CSPEC_RULE"."CSPEC_ELEMENT_ID"="CSPEC_EL"."CSPEC_ELEMENT_ID" AND "CSPEC_RULE"."PARTY_SITE_ID"="SHIP_CAS"."PARTY_SITE_ID") 66 - access("XPD"."DROP_ID"="XPDCR"."DROP_ID") filter(UPPER("ELEMENT_NAME")<>'AL' AND UPPER("ELEMENT_NAME")=UPPER("ELEME NT_NAME")) 68 - access("HAO"."ORGANIZATION_ID"="HOI2"."ORGANIZATION_ID") filter("HOI2"."ORG_INFORMATION_CONTEXT"||''='Accounting Information') 69 - access("LGR"."LEDGER_ID"=TO_NUMBER(DECODE(RTRIM(TRANSLATE("HOI2"."ORG_INF PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ORMATION1",'0123456789',' ')),NULL,"HOI2"."ORG_INFORMATION1",'-99999'))) 70 - filter("LGR"."OBJECT_TYPE_CODE"='L' AND NVL("LGR"."COMPLETE_FLAG",'Y')='Y ') 122 rows selected. SQL> spool off;