Hi Experts,
I have this query running with 23min in development server, but report took 1hr to execute in production server. Hoping to get idea of to improve on the performance.
I have attached necessary information as below. When i try to ftp my trace file, noticed that hit to 10MB of file size, and having error when running tkprof.
I opened the trace file, i saw these lines and printed until filesize up to 10mb.
my oracle db is
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
END OF STMT
PARSE #3:c=10000,e=3256,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=1071649075322
EXEC #3:c=0,e=593,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=1071649078646
WAIT #3: nam='SQL*Net message to client' ela= 3 driver id=1952673792 #bytes=1 p3=0 obj#=270 tim=1071649078817
WAIT #3: nam='db file sequential read' ela= 1240 file#=766 block#=547660 blocks=1 obj#=37006875 tim=1071649080664
WAIT #3: nam='gc cr grant 2-way' ela= 364 p1=747 p2=753505 p3=1 obj#=37006875 tim=1071649081460
WAIT #3: nam='db file sequential read' ela= 1168 file#=747 block#=753505 blocks=1 obj#=37006875 tim=1071649082750
WAIT #3: nam='gc cr grant 2-way' ela= 592 p1=767 p2=127180 p3=1 obj#=37006875 tim=1071649083720
My query:
SELECT
msi.description "ST Model",
oel.ordered_item "Product Part Number",
hp.party_name "Bill To Cust Name",
shc.location"Ship To Cust Num",
oeh.order_number "Order Num",
oel.line_number || '.' || oel.shipment_number "Order Line Num",
ft.nls_territory "Ship To Country",
ccm.designapplication "Design Application",
ccm.formattedcapacity||'GB' "Capacity",
oel.attribute1 "Order Category",
oel.Subinventory "Subinventory",
to_char(wnd.confirm_date,'MM/DD/YYYY DY') "Shipment Date",
NVL(oel.shipped_quantity,0) "Net Units"
FROM
apps.oe_order_headers_all oeh,
apps.oe_order_lines_all oel,
apps.seaeng_ccfamilymodelinfo ccm,
HZ_CUST_SITE_USES_ALL SHC,
HZ_CUST_ACCT_SITES_ALL SHAC,
HZ_PARTY_SITES SHPS,
HZ_LOCATIONS SHLC,
apps.fnd_territories ft,
apps.hz_cust_accounts hca,
apps.hz_parties hp,
--
apps.mtl_system_items_b msi
--
,wsh_delivery_assignments wda
,wsh_new_deliveries wnd
,wsh_delivery_details wdd
WHERE
oeh.header_id = oel.header_id
--
AND oeh.SHIP_TO_ORG_ID = SHC.SITE_USE_ID
AND SHC.SITE_USE_CODE = 'SHIP_TO'
and hp.party_id = shps.party_id
AND SHC.CUST_ACCT_SITE_ID = SHAC.CUST_ACCT_SITE_ID
AND SHAC.PARTY_SITE_ID = SHPS.PARTY_SITE_ID
AND SHPS.LOCATION_ID = SHLC.LOCATION_ID
AND SHLC.COUNTRY = ft.territory_code
AND hca.cust_account_id = SHAC.cust_account_id
--
AND msi.description = ccm.stmodelnumber
--
AND oel.inventory_item_id = msi.inventory_item_id
and msi.organization_id = 65
--
and wda.delivery_id = wnd.delivery_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND oel.line_id = wdd.source_line_id
AND wnd.status_code = 'CL'
AND ccm.designapplication IN ('PSG','ESG','NSG')
AND hca.sales_channel_code NOT IN ('RTL','AD-RTL','EU')
AND oel.attribute1 IN ('NB','NBEOL','NBSEA')
AND oel.subinventory IN ('KFGI','AFGI','SFGIF','FGIF')
AND trunc(WND.CONFIRM_DATE) >= TO_DATE('01-Jan-2009','DD-MON-YYYY')
AND trunc(WND.CONFIRM_DATE) < TO_DATE('31-JAN-2009','DD-MON-YYYY') + 1
AND oel.org_id = 189
--AND oel.header_id = 7233926
GROUP BY msi.description,
oel.ordered_item,
hp.party_name,
shc.LOCATION,
oeh.order_number,
oel.line_number || '.' || oel.shipment_number,
ft.nls_territory,
ccm.designapplication,
ccm.formattedcapacity || 'GB',
oel.attribute1,
oel.subinventory,
to_char(wnd.confirm_date,'MM/DD/YYYY DY'),
NVL (oel.shipped_quantity, 0)
HAVING COUNT (*) >= 1
SQL> show parameter user_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u33/oracle/product/10gRAC/adm
in/dap12_okdev14/udump
SQL>
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.4
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean FALSE
SQL>
SQL> show parameter db_file_multi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 8
SQL>
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4058013364
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 286 | 13161 (1)| 00:02:38 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 286 | 13161 (1)| 00:02:38 |
| 3 | NESTED LOOPS | | 1 | 286 | 13160 (1)| 00:02:38 |
| 4 | NESTED LOOPS | | 1 | 276 | 13159 (1)| 00:02:38 |
| 5 | NESTED LOOPS | | 1 | 250 | 13158 (1)| 00:02:38 |
| 6 | NESTED LOOPS | | 1 | 243 | 13157 (1)| 00:02:38 |
| 7 | NESTED LOOPS | | 1 | 235 | 13156 (1)| 00:02:38 |
| 8 | NESTED LOOPS | | 1 | 220 | 13155 (1)| 00:02:38 |
| 9 | NESTED LOOPS | | 1 | 204 | 13153 (1)| 00:02:38 |
| 10 | NESTED LOOPS | | 1 | 173 | 13151 (1)| 00:02:38 |
| 11 | NESTED LOOPS | | 1 | 155 | 13149 (1)| 00:02:38 |
| 12 | NESTED LOOPS | | 53 | 7420 | 13042 (1)| 00:02:37 |
| 13 | NESTED LOOPS | | 53 | 6784 | 12883 (1)| 00:02:35 |
| 14 | NESTED LOOPS | | 27 | 3132 | 12814 (1)| 00:02:34 |
| 15 | NESTED LOOPS | | 375 | 36000 | 12805 (1)| 00:02:34 |
| 16 | INLIST ITERATOR | | | | | |
|* 17 | TABLE ACCESS BY INDEX ROWID| OE_ORDER_LINES_ALL | 359 | 20822 | 12086 (1)| 00:02:26 |
|* 18 | INDEX RANGE SCAN | OE_ORDER_LINES_N21 | 45242 | | 115 (1)| 00:00:02 |
| 19 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 1 | 38 | 2 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | | 1 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | SEAENG_CCFAMILYMODELINFO | 1 | 20 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | SEAENG_CCFAMILYMODELINFO_U1 | 1 | | 0 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_DETAILS | 2 | 24 | 3 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | WSH_DELIVERY_DETAILS_N3 | 2 | | 2 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_ASSIGNMENTS | 1 | 12 | 3 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | WSH_DELIVERY_ASSIGNMENTS_N3 | 1 | | 2 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | WSH_NEW_DELIVERIES | 1 | 15 | 2 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | WSH_NEW_DELIVERIES_U1 | 1 | | 1 (0)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_HEADERS_ALL | 1 | 18 | 2 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | OE_ORDER_HEADERS_U1 | 1 | | 1 (0)| 00:00:01 |
|* 31 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_SITE_USES_ALL | 1 | 31 | 2 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | HZ_CUST_SITE_USES_U1 | 1 | | 1 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCT_SITES_ALL | 1 | 16 | 2 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | HZ_CUST_ACCT_SITES_U1 | 1 | | 1 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | HZ_PARTY_SITES | 1 | 15 | 1 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | HZ_PARTY_SITES_U1 | 1 | | 0 (0)| 00:00:01 |
| 37 | TABLE ACCESS BY INDEX ROWID | HZ_LOCATIONS | 1 | 8 | 1 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | HZ_LOCATIONS_U1 | 1 | | 0 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | FND_TERRITORIES | 1 | 7 | 1 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | FND_TERRITORIES_U1 | 1 | | 0 (0)| 00:00:01 |
| 41 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 1 | 26 | 1 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | | 0 (0)| 00:00:01 |
|* 43 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNTS | 1 | 10 | 1 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_U1 | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COUNT(*)>=1)
17 - filter("OEL"."ORG_ID"=189 AND ("OEL"."ATTRIBUTE1"='NB' OR "OEL"."ATTRIBUTE1"='NBEOL' OR
"OEL"."ATTRIBUTE1"='NBSEA'))
18 - access("OEL"."SUBINVENTORY"='AFGI' OR "OEL"."SUBINVENTORY"='FGIF' OR "OEL"."SUBINVENTORY"='KFGI' OR
"OEL"."SUBINVENTORY"='SFGIF')
20 - access("OEL"."INVENTORY_ITEM_ID"="MSI"."INVENTORY_ITEM_ID" AND "MSI"."ORGANIZATION_ID"=65)
21 - filter("CCM"."DESIGNAPPLICATION"='ESG' OR "CCM"."DESIGNAPPLICATION"='NSG' OR
"CCM"."DESIGNAPPLICATION"='PSG')
22 - access("MSI"."DESCRIPTION"="CCM"."STMODELNUMBER")
24 - access("OEL"."LINE_ID"="WDD"."SOURCE_LINE_ID")
26 - access("WDD"."DELIVERY_DETAIL_ID"="WDA"."DELIVERY_DETAIL_ID")
27 - filter("WND"."STATUS_CODE"='CL' AND TRUNC(INTERNAL_FUNCTION("WND"."CONFIRM_DATE"))>=TO_DATE(' 2009-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("WND"."CONFIRM_DATE"))<TO_DATE(' 2009-02-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
28 - access("WDA"."DELIVERY_ID"="WND"."DELIVERY_ID")
30 - access("OEH"."HEADER_ID"="OEL"."HEADER_ID")
31 - filter("SHC"."SITE_USE_CODE"='SHIP_TO')
32 - access("OEH"."SHIP_TO_ORG_ID"="SHC"."SITE_USE_ID")
34 - access("SHC"."CUST_ACCT_SITE_ID"="SHAC"."CUST_ACCT_SITE_ID")
36 - access("SHAC"."PARTY_SITE_ID"="SHPS"."PARTY_SITE_ID")
38 - access("SHPS"."LOCATION_ID"="SHLC"."LOCATION_ID")
40 - access("SHLC"."COUNTRY"="FT"."TERRITORY_CODE")
42 - access("HP"."PARTY_ID"="SHPS"."PARTY_ID")
43 - filter("HCA"."SALES_CHANNEL_CODE"<>'RTL' AND "HCA"."SALES_CHANNEL_CODE"<>'AD-RTL' AND
"HCA"."SALES_CHANNEL_CODE"<>'EU')
44 - access("HCA"."CUST_ACCOUNT_ID"="SHAC"."CUST_ACCOUNT_ID")
81 rows selected.
Elapsed: 00:00:04.71
SQL>
SQL> alter session set events '10046 trace name context forever, level 8';
Session altered.
Elapsed: 00:00:00.54
SQL>
SQL> -- put your statement here
SQL> SELECT
2 msi.description "ST Model",
3 oel.ordered_item "Product Part Number",
4 hp.party_name "Bill To Cust Name",
5 shc.location"Ship To Cust Num",
6 oeh.order_number "Order Num",
7 oel.line_number || '.' || oel.shipment_number "Order Line Num",
8 ft.nls_territory "Ship To Country",
9 ccm.designapplication "Design Application",
10 ccm.formattedcapacity||'GB' "Capacity",
11 oel.attribute1 "Order Category",
12 oel.Subinventory "Subinventory",
13 to_char(wnd.confirm_date,'MM/DD/YYYY DY') "Shipment Date",
14 NVL(oel.shipped_quantity,0) "Net Units"
15 FROM
16 apps.oe_order_headers_all oeh,
17 apps.oe_order_lines_all oel,
18 apps.seaeng_ccfamilymodelinfo ccm,
19 HZ_CUST_SITE_USES_ALL SHC,
20 HZ_CUST_ACCT_SITES_ALL SHAC,
21 HZ_PARTY_SITES SHPS,
22 HZ_LOCATIONS SHLC,
23 apps.fnd_territories ft,
24 apps.hz_cust_accounts hca,
25 apps.hz_parties hp,
26 --
27 apps.mtl_system_items_b msi
28 --
29 ,wsh_delivery_assignments wda
30 ,wsh_new_deliveries wnd
31 ,wsh_delivery_details wdd
32 WHERE
33 oeh.header_id = oel.header_id
34 --
35 AND oeh.SHIP_TO_ORG_ID = SHC.SITE_USE_ID
36 AND SHC.SITE_USE_CODE = 'SHIP_TO'
37 and hp.party_id = shps.party_id
38 AND SHC.CUST_ACCT_SITE_ID = SHAC.CUST_ACCT_SITE_ID
39 AND SHAC.PARTY_SITE_ID = SHPS.PARTY_SITE_ID
40 AND SHPS.LOCATION_ID = SHLC.LOCATION_ID
41 AND SHLC.COUNTRY = ft.territory_code
42 AND hca.cust_account_id = SHAC.cust_account_id
43 --
44 AND msi.description = ccm.stmodelnumber
45 --
46 AND oel.inventory_item_id = msi.inventory_item_id
47 and msi.organization_id = 65
48 --
49 and wda.delivery_id = wnd.delivery_id
50 AND wdd.delivery_detail_id = wda.delivery_detail_id
51 AND oel.line_id = wdd.source_line_id
52 AND wnd.status_code = 'CL'
53 AND ccm.designapplication IN ('PSG','ESG','NSG')
54 AND hca.sales_channel_code NOT IN ('RTL','AD-RTL','EU')
55 AND oel.attribute1 IN ('NB','NBEOL','NBSEA')
56 AND oel.subinventory IN ('KFGI','AFGI','SFGIF','FGIF')
57 AND trunc(WND.CONFIRM_DATE) >= TO_DATE('01-Jan-2009','DD-MON-YYYY')
58 AND trunc(WND.CONFIRM_DATE) < TO_DATE('31-JAN-2009','DD-MON-YYYY') + 1
59 AND oel.org_id = 189
60 --AND oel.header_id = 7233926
61 GROUP BY msi.description,
62 oel.ordered_item,
63 hp.party_name,
64 shc.LOCATION,
65 oeh.order_number,
66 oel.line_number || '.' || oel.shipment_number,
67 ft.nls_territory,
68 ccm.designapplication,
69 ccm.formattedcapacity || 'GB',
70 oel.attribute1,
71 oel.subinventory,
72 to_char(wnd.confirm_date,'MM/DD/YYYY DY'),
73 NVL (oel.shipped_quantity, 0)
74 HAVING COUNT (*) >= 1
75 /
592 rows selected.
Elapsed: 00:39:37.34
Execution Plan
----------------------------------------------------------
Plan hash value: 4058013364
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 286 | 13161 (1)| 00:02:38 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 286 | 13161 (1)| 00:02:38 |
| 3 | NESTED LOOPS | | 1 | 286 | 13160 (1)| 00:02:38 |
| 4 | NESTED LOOPS | | 1 | 276 | 13159 (1)| 00:02:38 |
| 5 | NESTED LOOPS | | 1 | 250 | 13158 (1)| 00:02:38 |
| 6 | NESTED LOOPS | | 1 | 243 | 13157 (1)| 00:02:38 |
| 7 | NESTED LOOPS | | 1 | 235 | 13156 (1)| 00:02:38 |
| 8 | NESTED LOOPS | | 1 | 220 | 13155 (1)| 00:02:38 |
| 9 | NESTED LOOPS | | 1 | 204 | 13153 (1)| 00:02:38 |
| 10 | NESTED LOOPS | | 1 | 173 | 13151 (1)| 00:02:38 |
| 11 | NESTED LOOPS | | 1 | 155 | 13149 (1)| 00:02:38 |
| 12 | NESTED LOOPS | | 53 | 7420 | 13042 (1)| 00:02:37 |
| 13 | NESTED LOOPS | | 53 | 6784 | 12883 (1)| 00:02:35 |
| 14 | NESTED LOOPS | | 27 | 3132 | 12814 (1)| 00:02:34 |
| 15 | NESTED LOOPS | | 375 | 36000 | 12805 (1)| 00:02:34 |
| 16 | INLIST ITERATOR | | | | | |
|* 17 | TABLE ACCESS BY INDEX ROWID| OE_ORDER_LINES_ALL | 359 | 20822 | 12086 (1)| 00:02:26 |
|* 18 | INDEX RANGE SCAN | OE_ORDER_LINES_N21 | 45242 | | 115 (1)| 00:00:02 |
| 19 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 1 | 38 | 2 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | | 1 (0)| 00:00:01 |
|* 21 | TABLE ACCESS BY INDEX ROWID | SEAENG_CCFAMILYMODELINFO | 1 | 20 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | SEAENG_CCFAMILYMODELINFO_U1 | 1 | | 0 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_DETAILS | 2 | 24 | 3 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | WSH_DELIVERY_DETAILS_N3 | 2 | | 2 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | WSH_DELIVERY_ASSIGNMENTS | 1 | 12 | 3 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | WSH_DELIVERY_ASSIGNMENTS_N3 | 1 | | 2 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID | WSH_NEW_DELIVERIES | 1 | 15 | 2 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | WSH_NEW_DELIVERIES_U1 | 1 | | 1 (0)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_HEADERS_ALL | 1 | 18 | 2 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | OE_ORDER_HEADERS_U1 | 1 | | 1 (0)| 00:00:01 |
|* 31 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_SITE_USES_ALL | 1 | 31 | 2 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | HZ_CUST_SITE_USES_U1 | 1 | | 1 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCT_SITES_ALL | 1 | 16 | 2 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | HZ_CUST_ACCT_SITES_U1 | 1 | | 1 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | HZ_PARTY_SITES | 1 | 15 | 1 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | HZ_PARTY_SITES_U1 | 1 | | 0 (0)| 00:00:01 |
| 37 | TABLE ACCESS BY INDEX ROWID | HZ_LOCATIONS | 1 | 8 | 1 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | HZ_LOCATIONS_U1 | 1 | | 0 (0)| 00:00:01 |
| 39 | TABLE ACCESS BY INDEX ROWID | FND_TERRITORIES | 1 | 7 | 1 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | FND_TERRITORIES_U1 | 1 | | 0 (0)| 00:00:01 |
| 41 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 1 | 26 | 1 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | | 0 (0)| 00:00:01 |
|* 43 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNTS | 1 | 10 | 1 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_U1 | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(COUNT(*)>=1)
17 - filter("OEL"."ORG_ID"=189 AND ("OEL"."ATTRIBUTE1"='NB' OR "OEL"."ATTRIBUTE1"='NBEOL' OR
"OEL"."ATTRIBUTE1"='NBSEA'))
18 - access("OEL"."SUBINVENTORY"='AFGI' OR "OEL"."SUBINVENTORY"='FGIF' OR "OEL"."SUBINVENTORY"='KFGI' OR
"OEL"."SUBINVENTORY"='SFGIF')
20 - access("OEL"."INVENTORY_ITEM_ID"="MSI"."INVENTORY_ITEM_ID" AND "MSI"."ORGANIZATION_ID"=65)
21 - filter("CCM"."DESIGNAPPLICATION"='ESG' OR "CCM"."DESIGNAPPLICATION"='NSG' OR
"CCM"."DESIGNAPPLICATION"='PSG')
22 - access("MSI"."DESCRIPTION"="CCM"."STMODELNUMBER")
24 - access("OEL"."LINE_ID"="WDD"."SOURCE_LINE_ID")
26 - access("WDD"."DELIVERY_DETAIL_ID"="WDA"."DELIVERY_DETAIL_ID")
27 - filter("WND"."STATUS_CODE"='CL' AND TRUNC(INTERNAL_FUNCTION("WND"."CONFIRM_DATE"))>=TO_DATE(' 2009-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("WND"."CONFIRM_DATE"))<TO_DATE(' 2009-02-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
28 - access("WDA"."DELIVERY_ID"="WND"."DELIVERY_ID")
30 - access("OEH"."HEADER_ID"="OEL"."HEADER_ID")
31 - filter("SHC"."SITE_USE_CODE"='SHIP_TO')
32 - access("OEH"."SHIP_TO_ORG_ID"="SHC"."SITE_USE_ID")
34 - access("SHC"."CUST_ACCT_SITE_ID"="SHAC"."CUST_ACCT_SITE_ID")
36 - access("SHAC"."PARTY_SITE_ID"="SHPS"."PARTY_SITE_ID")
38 - access("SHPS"."LOCATION_ID"="SHLC"."LOCATION_ID")
40 - access("SHLC"."COUNTRY"="FT"."TERRITORY_CODE")
42 - access("HP"."PARTY_ID"="SHPS"."PARTY_ID")
43 - filter("HCA"."SALES_CHANNEL_CODE"<>'RTL' AND "HCA"."SALES_CHANNEL_CODE"<>'AD-RTL' AND
"HCA"."SALES_CHANNEL_CODE"<>'EU')
44 - access("HCA"."CUST_ACCOUNT_ID"="SHAC"."CUST_ACCOUNT_ID")
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
1915554 consistent gets
467079 physical reads
232 redo size
51846 bytes sent via SQL*Net to client
971 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
592 rows processed
Edited by: Lygine on Jun 11, 2010 12:38 AM