Home » RDBMS Server » Performance Tuning » Select statement taking Long time to execute (Oracle 10g)
|
|
Re: Select statement taking Long time to execute [message #637698 is a reply to message #637697] |
Thu, 21 May 2015 16:34 |
|
P1Oracle
Messages: 60 Registered: August 2014 Location: Hyderabad
|
Member |
|
|
Hi,
EXPLAIN PLAN FOR
SELECT L.SHIP_FROM_ORG_ID ORG_ID,
org.ORGANIZATION_CODE,
rcta.CUSTOMER_TRX_ID,
rctla.CUSTOMER_TRX_LINE_ID,
rctla.PREVIOUS_CUSTOMER_TRX_LINE_ID,
rcta.TRX_NUMBER,
rcta.TRX_DATE,
rctla.CREATION_DATE,
hca.ACCOUNT_NUMBER,
hca.ORIG_SYSTEM_REFERENCE,
hpty.PARTY_NAME CUST_NAME,
hcasa.CUSTOMER_CATEGORY_CODE PRICE_LEVEL,
H.HEADER_ID,
L.LINE_ID,
H.ORDER_NUMBER,
H.CUST_PO_NUMBER,
L.LINE_NUMBER
||'.'
|| L.SHIPMENT_NUMBER LINE_NUMBER,
L.INVENTORY_ITEM_ID,
L.SHIP_FROM_ORG_ID,
L.ORDERED_ITEM ITEM_NUMBER,
L.ITEM_TYPE_CODE,
L.SOURCE_TYPE_CODE,
MSIB.DESCRIPTION,
NVL(rctla.QUANTITY_INVOICED, rctla.QUANTITY_CREDITED) QUANTITY_INVOICED,
rctla.UOM_CODE,
L.FULFILLMENT_DATE,
NVL(L.ACTUAL_SHIPMENT_DATE,L.FULFILLMENT_DATE) ACTUAL_SHIPMENT_DATE,
to_number(REPLACE(L.ATTRIBUTE1,',','')) MSRP,
NVL(rctla.UNIT_SELLING_PRICE,0) UNIT_SELLING_PRICE,
rctla.EXTENDED_AMOUNT,
--L.UNIT_COST,
COALESCE(to_number(l.ATTRIBUTE5),
(SELECT DISTINCT ITEM_COST
FROM CST_ITEM_COSTS cst
WHERE cst.inventory_item_id=l.inventory_item_id
AND cst.organization_id =l.ship_from_org_id
AND L.ITEM_TYPE_CODE ='KIT'
AND cst.cost_type_id =3
),0) UNIT_COST,
rcta.INVOICE_CURRENCY_CODE,
NVL(rcta.EXCHANGE_RATE,1) EXCHANGE_RATE,
NVL(L.ATTRIBUTE7,1) SALES_RATE,
trunc(l.creation_date) LINE_CREATION_DATE,
l.PRICING_DATE,
rcta.ORG_ID OU_ID,
hou.NAME OU_NAME,
L.LINE_CATEGORY_CODE,
L.INVOICE_TO_ORG_ID,
NVL(L.SHIP_TO_ORG_ID,H.SHIP_TO_ORG_ID) SHIP_TO_ORG_ID,
L.PRICE_LIST_ID,
rcta.BILL_TO_SITE_USE_ID,
hzloc.ADDRESS2 BILL_TO_ADDR1,
hzloc.ADDRESS3 BILL_TO_ADDR2,
hzloc.CITY BILL_TO_CITY,
--hzloc.STATE BILL_TO_STATE,
NVL(hzloc.STATE, hzloc.PROVINCE) BILL_TO_STATE,
hzloc.POSTAL_CODE BILL_TO_POSTAL_CODE,
hzloc.COUNTRY BILL_TO_COUNTRY,
rcta.SHIP_TO_SITE_USE_ID,
hzloc2.ADDRESS1 SHIP_TO_NAME,
hzloc2.ADDRESS2 SHIP_TO_ADDR1,
hzloc2.ADDRESS3 SHIP_TO_ADDR2,
hzloc2.CITY SHIP_TO_CITY,
--hzloc2.STATE SHIP_TO_STATE ,
NVL(hzloc2.STATE, hzloc2.PROVINCE) SHIP_TO_STATE ,
hzloc2.POSTAL_CODE SHIP_TO_POSTAL_CODE,
hzloc2.COUNTRY SHIP_TO_COUNTRY,
H.END_CUSTOMER_SITE_USE_ID,
hzloc3.ADDRESS1 END_CUST_NAME,
hzloc3.ADDRESS2 END_CUST_ADDR1,
hzloc3.ADDRESS3 END_CUST_ADDR2,
hzloc3.CITY END_CUST_CITY,
--hzloc3.STATE END_CUST_STATE ,
NVL(hzloc3.STATE, hzloc3.PROVINCE) END_CUST_STATE ,
hzloc3.POSTAL_CODE END_CUST_POSTAL_CODE,
hzloc3.COUNTRY END_CUST_COUNTRY,
rcta.PRIMARY_SALESREP_ID,
srep.NAME SALESREP_NAME,
rctta.NAME TRAN_TYPE_NAME,
rctta.TYPE TRAN_TYPE,
hcsua.TERRITORY_ID,
terr.SEGMENT1 TERRITORY_NAME,
H.FREIGHT_TERMS_CODE,
FLV.MEANING FREIGHT_TERMS, -- V2.1 --
H.SHIPPING_METHOD_CODE, -- V2.1 --
WCS.SHIP_METHOD_MEANING -- V2.1 --
,
ptrm.NAME TERM_NAME -- V3.0 --
,
rcta.TERM_ID -- V3.0 --
FROM APPS.OE_ORDER_LINES_ALL L ,
APPS.OE_ORDER_HEADERS_ALL H ,
APPS.MTL_SYSTEM_ITEMS_B MSIB ,
APPS.RA_CUSTOMER_TRX_LINES_ALL rctla ,
APPS.RA_CUSTOMER_TRX_ALL rcta ,
APPS.RA_CUST_TRX_TYPES_ALL rctta ,
APPS.HZ_CUST_ACCOUNTS hca ,
APPS.HZ_PARTIES hpty ,
APPS.HZ_CUST_SITE_USES_ALL hcsua ,
APPS.HZ_CUST_ACCT_SITES_ALL hcasa ,
APPS.HZ_LOCATIONS hzloc ,
APPS.HZ_PARTY_SITES hzpsi ,
APPS.HZ_CUST_SITE_USES_ALL hcsua2 ,
APPS.HZ_CUST_ACCT_SITES_ALL hcasa2 ,
APPS.HZ_LOCATIONS hzloc2 ,
APPS.HZ_PARTY_SITES hzpsi2 ,
APPS.HZ_CUST_SITE_USES_ALL hcsua3 ,
APPS.HZ_CUST_ACCT_SITES_ALL hcasa3 ,
APPS.HZ_LOCATIONS hzloc3 ,
APPS.HZ_PARTY_SITES hzpsi3 ,
APPS.RA_TERRITORIES terr -- V1.4
,
AR.RA_TERMS_TL ptrm -- V3.0 --
,
APPS.jtf_rs_salesreps srep ,
APPS.org_organization_definitions org ,
HR.HR_ALL_ORGANIZATION_UNITS hou -- V2.0 --
,
APPS.FND_LOOKUP_VALUES_VL FLV -- V2.1 --
,
WSH.WSH_CARRIER_SERVICES WCS -- V2.1 --
WHERE L.HEADER_ID = H.HEADER_ID
AND L.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND L.SHIP_FROM_ORG_ID = MSIB.ORGANIZATION_ID
AND L.SHIP_FROM_ORG_ID = ORG.ORGANIZATION_ID
--and to_char(l.LINE_ID) = rctla.INTERFACE_LINE_ATTRIBUTE6 -- V2.8 --
AND to_number(rctla.INTERFACE_LINE_ATTRIBUTE6) = l.LINE_ID -- V2.8 --
AND rctla.CUSTOMER_TRX_ID = rcta.CUSTOMER_TRX_ID
AND rcta.BILL_TO_CUSTOMER_ID = hca.CUST_ACCOUNT_ID
AND rcta.CUST_TRX_TYPE_ID = rctta.CUST_TRX_TYPE_ID
AND hca.PARTY_ID = hpty.PARTY_ID
AND rcta.BILL_TO_SITE_USE_ID = hcsua.SITE_USE_ID
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcsua.TERRITORY_ID = terr.TERRITORY_ID(+)
AND hcasa.PARTY_SITE_ID = hzpsi.PARTY_SITE_ID
AND hzpsi.location_id = hzloc.location_id
AND rcta.SHIP_TO_SITE_USE_ID = hcsua2.SITE_USE_ID
AND hcsua2.cust_acct_site_id = hcasa2.cust_acct_site_id
AND hcasa2.PARTY_SITE_ID = hzpsi2.PARTY_SITE_ID
AND hzpsi2.location_id = hzloc2.location_id
AND H.END_CUSTOMER_SITE_USE_ID = hcsua3.SITE_USE_ID(+)
AND hcsua3.cust_acct_site_id = hcasa3.cust_acct_site_id(+)
AND hcasa3.PARTY_SITE_ID = hzpsi3.PARTY_SITE_ID(+)
AND hzpsi3.location_id = hzloc3.location_id(+)
AND rcta.PRIMARY_SALESREP_ID = srep.SALESREP_ID(+)
AND rcta.ORG_ID = hou.ORGANIZATION_ID
AND rcta.TERM_ID = ptrm.TERM_ID(+)
AND 'US' = ptrm.LANGUAGE(+)
AND FLV.LOOKUP_TYPE(+) = 'FREIGHT_TERMS'
AND H.FREIGHT_TERMS_CODE = FLV.LOOKUP_CODE(+)
AND H.SHIPPING_METHOD_CODE = WCS.SHIP_METHOD_CODE(+)
AND rcta.ORG_ID = srep.ORG_ID(+)
AND rctla.WH_UPDATE_DATE IS NULL
/
When i ran SELECT * FROM table(dbms_xplan.display);
it is showing 130 rows selected.
|
|
|
Re: Select statement taking Long time to execute [message #637699 is a reply to message #637698] |
Thu, 21 May 2015 16:42 |
|
P1Oracle
Messages: 60 Registered: August 2014 Location: Hyderabad
|
Member |
|
|
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2255527393
----------------------------------------------------------------------------------------------------
-----------------------------------------------
| Id | Operation | Name |
Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
-----------------------------------------------
| 0 | SELECT STATEMENT | |
98716 | 116M| | 17345 (1)| 00:03:29 |
|* 1 | FILTER | |
| | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | CST_ITEM_COSTS |
1 | 17 | | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | CST_ITEM_COSTS_U1 |
1 | | | 1 (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT OUTER | |
98716 | 116M| | 17345 (1)| 00:03:29 |
| 5 | TABLE ACCESS FULL | HZ_LOCATIONS |
8598 | 403K| | 31 (4)| 00:00:01 |
|* 6 | HASH JOIN RIGHT OUTER | |
98716 | 112M| | 17313 (1)| 00:03:28 |
| 7 | TABLE ACCESS FULL | HZ_PARTY_SITES |
15927 | 155K| | 31 (0)| 00:00:01 |
|* 8 | HASH JOIN RIGHT OUTER | |
98716 | 111M| | 17281 (1)| 00:03:28 |
| 9 | TABLE ACCESS FULL | HZ_CUST_ACCT_SITES_ALL |
1893 | 18930 | | 8 (0)| 00:00:01 |
|* 10 | HASH JOIN | |
98716 | 110M| | 17272 (1)| 00:03:28 |
| 11 | TABLE ACCESS FULL | HZ_LOCATIONS |
8598 | 260K| | 31 (4)| 00:00:01 |
|* 12 | HASH JOIN | |
98716 | 107M| | 17240 (1)| 00:03:27 |
| 13 | TABLE ACCESS FULL | HZ_PARTY_SITES |
15927 | 155K| | 31 (0)| 00:00:01 |
|* 14 | HASH JOIN | |
98716 | 106M| | 17208 (1)| 00:03:27 |
| 15 | TABLE ACCESS FULL | HZ_CUST_ACCT_SITES_ALL |
1893 | 51111 | | 8 (0)| 00:00:01 |
|* 16 | HASH JOIN | |
98716 | 103M| | 17198 (1)| 00:03:27 |
| 17 | TABLE ACCESS FULL | HZ_LOCATIONS |
8598 | 403K| | 31 (4)| 00:00:01 |
|* 18 | HASH JOIN | |
98716 | 99M| | 17167 (1)| 00:03:27 |
| 19 | TABLE ACCESS FULL | HZ_PARTY_SITES |
15927 | 155K| | 31 (0)| 00:00:01 |
|* 20 | HASH JOIN | |
98716 | 98M| | 17134 (1)| 00:03:26 |
| 21 | TABLE ACCESS FULL | HZ_CUST_ACCT_SITES_ALL |
1893 | 18930 | | 8 (0)| 00:00:01 |
|* 22 | HASH JOIN | |
98716 | 97M| | 17125 (1)| 00:03:26 |
| 23 | TABLE ACCESS FULL | HZ_PARTIES |
40250 | 943K| | 162 (1)| 00:00:02 |
|* 24 | HASH JOIN RIGHT OUTER | |
98716 | 95M| | 16963 (1)| 00:03:24 |
| 25 | TABLE ACCESS FULL | RA_TERRITORIES |
50 | 450 | | 3 (0)| 00:00:01 |
|* 26 | HASH JOIN | |
98716 | 94M| | 16959 (1)| 00:03:24 |
| 27 | TABLE ACCESS FULL | HZ_CUST_SITE_USES_ALL |
3758 | 45096 | | 12 (0)| 00:00:01 |
|* 28 | HASH JOIN | |
98731 | 93M| | 16945 (1)| 00:03:24 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 29 | TABLE ACCESS FULL | HZ_CUST_SITE_USES_ALL |
3758 | 37580 | | 12 (0)| 00:00:01 |
|* 30 | HASH JOIN | |
98731 | 92M| | 16932 (1)| 00:03:24 |
| 31 | TABLE ACCESS FULL | HR_ALL_ORGANIZATION_UNITS |
2505 | 60120 | | 8 (0)| 00:00:01 |
|* 32 | HASH JOIN | |
98731 | 89M| | 16923 (1)| 00:03:24 |
| 33 | TABLE ACCESS FULL | HZ_CUST_ACCOUNTS |
1048 | 22008 | | 5 (0)| 00:00:01 |
|* 34 | HASH JOIN | |
98745 | 87M| | 16917 (1)| 00:03:24 |
|* 35 | TABLE ACCESS FULL | RA_CUST_TRX_TYPES_ALL |
882 | 28224 | | 6 (0)| 00:00:01 |
|* 36 | HASH JOIN RIGHT OUTER | |
62247 | 53M| | 16910 (1)| 00:03:23 |
| 37 | TABLE ACCESS FULL | JTF_RS_SALESREPS |
585 | 15795 | | 4 (0)| 00:00:01 |
|* 38 | HASH JOIN RIGHT OUTER | |
62247 | 51M| | 16905 (1)| 00:03:23 |
| 39 | TABLE ACCESS BY INDEX ROWID | RA_TERMS_TL |
26 | 468 | | 2 (0)| 00:00:01 |
|* 40 | INDEX SKIP SCAN | RA_TERMS_TL_N1 |
26 | | | 1 (0)| 00:00:01 |
|* 41 | HASH JOIN | |
62247 | 50M| 3472K| 16902 (1)| 00:03:23 |
|* 42 | TABLE ACCESS FULL | RA_CUSTOMER_TRX_ALL |
49320 | 2889K| | 404 (2)| 00:00:05 |
|* 43 | HASH JOIN | |
62247 | 47M| 22M| 13946 (1)| 00:02:48 |
|* 44 | TABLE ACCESS FULL | RA_CUSTOMER_TRX_LINES_ALL |
433K| 17M| | 1564 (3)| 00:00:19 |
| 45 | NESTED LOOPS OUTER | |
70812 | 50M| | 8704 (1)| 00:01:45 |
|* 46 | HASH JOIN RIGHT OUTER | |
70812 | 50M| | 8704 (1)| 00:01:45 |
| 47 | TABLE ACCESS FULL | WSH_CARRIER_SERVICES |
111 | 4995 | | 3 (0)| 00:00:01 |
|* 48 | HASH JOIN RIGHT OUTER | |
70812 | 47M| | 8700 (1)| 00:01:45 |
| 49 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES |
1 | 57 | | 4 (0)| 00:00:01 |
|* 50 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 |
1 | | | 3 (0)| 00:00:01 |
|* 51 | HASH JOIN | |
70812 | 43M| 2272K| 8695 (1)| 00:01:45 |
| 52 | TABLE ACCESS FULL | OE_ORDER_HEADERS_ALL |
44620 | 1742K| | 200 (4)| 00:00:03 |
|* 53 | HASH JOIN | |
70812 | 40M| | 6323 (2)| 00:01:16 |
| 54 | TABLE ACCESS FULL | MTL_SYSTEM_ITEMS_B |
28090 | 932K| | 753 (1)| 00:00:10 |
|* 55 | HASH JOIN | |
69859 | 37M| | 5569 (2)| 00:01:07 |
|* 56 | HASH JOIN | |
13 | 1313 | | 50 (6)| 00:00:01 |
|* 57 | HASH JOIN | |
25 | 2325 | | 46 (5)| 00:00:01 |
| 58 | NESTED LOOPS | |
| | | | |
| 59 | NESTED LOOPS | |
25 | 1625 | | 28 (4)| 00:00:01 |
| 60 | NESTED LOOPS | |
25 | 1425 | | 27 (8)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 61 | HASH JOIN | |
37 | 1517 | | 27 (8)| 00:00:01 |
|* 62 | TABLE ACCESS FULL | HR_ORGANIZATION_INFORMATION |
52 | 1716 | | 17 (0)| 00:00:01 |
|* 63 | TABLE ACCESS FULL | HR_ALL_ORGANIZATION_UNITS |
37 | 296 | | 9 (12)| 00:00:01 |
|* 64 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK |
1 | 16 | | 0 (0)| 00:00:01 |
|* 65 | INDEX UNIQUE SCAN | MTL_PARAMETERS_U1 |
1 | | | 0 (0)| 00:00:01 |
| 66 | TABLE ACCESS BY INDEX ROWID| MTL_PARAMETERS |
1 | 8 | | 1 (0)| 00:00:01 |
|* 67 | TABLE ACCESS FULL | HR_ORGANIZATION_INFORMATION |
196 | 5488 | | 17 (0)| 00:00:01 |
|* 68 | TABLE ACCESS FULL | GL_LEDGERS |
49 | 392 | | 3 (0)| 00:00:01 |
| 69 | TABLE ACCESS FULL | OE_ORDER_LINES_ALL |
203K| 90M| | 5518 (2)| 00:01:07 |
| 70 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_SITE_USES_ALL |
1 | 10 | | 0 (0)| 00:00:01 |
|* 71 | INDEX UNIQUE SCAN | HZ_CUST_SITE_USES_U1 |
1 | | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
-----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:B1='KIT')
3 - access("CST"."INVENTORY_ITEM_ID"=:B1 AND "CST"."ORGANIZATION_ID"=:B2 AND "CST"."COST_TYPE_ID"
=3)
4 - access("HZPSI3"."LOCATION_ID"="HZLOC3"."LOCATION_ID"(+))
6 - access("HCASA3"."PARTY_SITE_ID"="HZPSI3"."PARTY_SITE_ID"(+))
8 - access("HCSUA3"."CUST_ACCT_SITE_ID"="HCASA3"."CUST_ACCT_SITE_ID"(+))
10 - access("HZPSI"."LOCATION_ID"="HZLOC"."LOCATION_ID")
12 - access("HCASA"."PARTY_SITE_ID"="HZPSI"."PARTY_SITE_ID")
14 - access("HCSUA"."CUST_ACCT_SITE_ID"="HCASA"."CUST_ACCT_SITE_ID")
16 - access("HZPSI2"."LOCATION_ID"="HZLOC2"."LOCATION_ID")
18 - access("HCASA2"."PARTY_SITE_ID"="HZPSI2"."PARTY_SITE_ID")
20 - access("HCSUA2"."CUST_ACCT_SITE_ID"="HCASA2"."CUST_ACCT_SITE_ID")
22 - access("HCA"."PARTY_ID"="HPTY"."PARTY_ID")
24 - access("HCSUA"."TERRITORY_ID"="TERR"."TERRITORY_ID"(+))
26 - access("RCTA"."BILL_TO_SITE_USE_ID"="HCSUA"."SITE_USE_ID")
28 - access("RCTA"."SHIP_TO_SITE_USE_ID"="HCSUA2"."SITE_USE_ID")
30 - access("RCTA"."ORG_ID"="HOU"."ORGANIZATION_ID")
32 - access("RCTA"."BILL_TO_CUSTOMER_ID"="HCA"."CUST_ACCOUNT_ID")
34 - access("RCTA"."CUST_TRX_TYPE_ID"="CUST_TRX_TYPE_ID")
35 - filter("ZD_EDITION_NAME"='ORA$BASE')
36 - access("RCTA"."PRIMARY_SALESREP_ID"="SREP"."SALESREP_ID"(+) AND "RCTA"."ORG_ID"="SREP"."ORG_I
D"(+))
38 - access("RCTA"."TERM_ID"="PTRM"."TERM_ID"(+))
40 - access("PTRM"."LANGUAGE"(+)='US')
filter("PTRM"."LANGUAGE"(+)='US')
41 - access("RCTLA"."CUSTOMER_TRX_ID"="RCTA"."CUSTOMER_TRX_ID")
42 - filter("RCTA"."SHIP_TO_SITE_USE_ID" IS NOT NULL)
43 - access("L"."LINE_ID"=TO_NUMBER("RCTLA"."INTERFACE_LINE_ATTRIBUTE6"))
44 - filter("RCTLA"."WH_UPDATE_DATE" IS NULL)
46 - access("H"."SHIPPING_METHOD_CODE"="WCS"."SHIP_METHOD_CODE"(+))
48 - access("H"."FREIGHT_TERMS_CODE"="LOOKUP_CODE"(+))
50 - access("LOOKUP_TYPE"(+)='FREIGHT_TERMS' AND "LANGUAGE"(+)=USERENV('LANG') AND "ZD_EDITION_NAM
E"(+)='V_20141219_1621')
filter("ZD_EDITION_NAME"(+)='V_20141219_1621' AND "LANGUAGE"(+)=USERENV('LANG'))
51 - access("L"."HEADER_ID"="H"."HEADER_ID")
53 - access("L"."INVENTORY_ITEM_ID"="MSIB"."INVENTORY_ITEM_ID" AND "L"."SHIP_FROM_ORG_ID"="MSIB"."
ORGANIZATION_ID")
55 - access("L"."SHIP_FROM_ORG_ID"="HAO"."ORGANIZATION_ID")
56 - access("LGR"."LEDGER_ID"=TO_NUMBER(DECODE(RTRIM(TRANSLATE("HOI2"."ORG_INFORMATION1",'01234567
89','
')),NULL,"HOI2"."ORG_INFORMATION1",'-99999')))
57 - access("HAO"."ORGANIZATION_ID"="HOI2"."ORGANIZATION_ID")
61 - access("HAO"."ORGANIZATION_ID"="HOI1"."ORGANIZATION_ID")
62 - filter("HOI1"."ORG_INFORMATION1"='INV' AND "HOI1"."ORG_INFORMATION2"='Y' AND "HOI1"."ORG_INFO
RMATION_CONTEXT"||''='CLASS')
63 - filter("HAO"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"HAO"."BUSINESS_
GROUP_ID","HR_GENERAL"."GET_BUSINESS_GR
OUP_ID"()))
64 - access("HAO"."ORGANIZATION_ID"="ORGANIZATION_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITI
ON_NAME"='ORA$BASE')
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
filter(DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_RECORD"('HR_ALL_ORGAN
IZATION_UNITS',"ORGANIZATION_ID"))='TRU
E')
65 - access("HAO"."ORGANIZATION_ID"="MP"."ORGANIZATION_ID")
67 - filter("HOI2"."ORG_INFORMATION_CONTEXT"||''='Accounting Information')
68 - filter("LGR"."OBJECT_TYPE_CODE"='L' AND NVL("LGR"."COMPLETE_FLAG",'Y')='Y')
71 - access("H"."END_CUSTOMER_SITE_USE_ID"="HCSUA3"."SITE_USE_ID"(+))
130 rows selected.
|
|
|
|
|
Re: Select statement taking Long time to execute [message #637706 is a reply to message #637701] |
Thu, 21 May 2015 22:07 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Please refer to the page BlackSwan has noted. I have duplicated here part of that page but your should review the full page anyway.
Quote:I am attaching here the promotional first chapter of my book on SQL Tuning, and the scripts file for people to find. These are free to use and share. As always, there is no warranty express or implied and use at your own risk.
At the suggestion of BlackSwan, I have also attached the latest document which describes the information needed to tune a SQL statement and how you can get it using the scripts also available here. If you are going to ask for help in tuning a SQL statement, please review this document first. You will at some point be asked to produce this information so if you can have it in your original post, better results will follow. I suggest providing the BASIC information first, then follow up with the ADVANCED information when asked.
Enjoy. Kevin
As indicated, here are the attachments. There is a minimum amount of information anyone here needs to help you, which is why everyone is asking for it. All who have responded to your request for help can help you tune your query, if you provide the information. They are all experts in Oracle which I know because I have conversed with them all over many years.
Good luck, post back when you have the requested info, or at least the BASIC info noted in the attachment. At some point you may become interested in the book itself. Follow the link. The book is at its lowest price ever.
Kevin
[Updated on: Thu, 21 May 2015 22:08] Report message to a moderator
|
|
|
Re: Select statement taking Long time to execute [message #637707 is a reply to message #637706] |
Thu, 21 May 2015 23:18 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
How manw rows are in APPS.RA_CUSTOMER_TRX_LINES_ALL?
How many of them have WH_UPDATE_DATE IS NULL?
Your query is doing a lot of Full Table Scans. That might be fine if a significant portion of the rows in RA_CUSTOMER_TRX_LINES_ALL have a NULL WH_UPDATE_DATE. But if there are (say) <1% with a NULL date, then your query would be best to start with that table and then do Indexed Nested Loops joins to the rest.
So to start with, run these and tell us the results:
SELECT COUNT(*)
FROM APPS.RA_CUSTOMER_TRX_LINES_ALL;
SELECT COUNT(*)
FROM APPS.RA_CUSTOMER_TRX_LINES_ALL
WHERE WH_UPDATE_DATE IS NULL;
Oh, and please run them on the environment where it is slow, not some development environment where the tables are almost empty.
Ross Leishman
P.S. Just because it looks like I'm helping, don't ignore the other guys' request for more information.
|
|
|
Goto Forum:
Current Time: Sat Jul 27 09:18:16 CDT 2024
|