Home » Applications » Oracle Fusion Apps & E-Business Suite » Query Execution time problem (Oracle Apps 11i)
| Query Execution time problem [message #293863] |
Tue, 15 January 2008 06:08  |
mehulmb
Messages: 25
Registered: May 2006
Location: Pune
|
Junior Member |
|
|
Hello guys,
I have used two instance in oracle apps that is Indian and US..now my query is taking so much time to execute it. i have used dblink with 12 tables...is there any other solution to execute the query fastest.my query is like this :
SELECT
CTA.TRX_NUMBER INVOICE_NUMBER,
CTA.TRX_DATE INVOICE_DATE,
APS.DUE_DATE DUE_DATE,
-- TO_DATE(CTLA.GL_DATE,'DD-MON-RRRR') - TO_DATE(APS.DUE_DATE,'DD-MON-RRRR') DAYS_LATE,
TO_DATE('&GL_DATE','DD-MON-RRRR') - TO_DATE(APS.DUE_DATE,'DD-MON-RRRR') DAYS_LATE,
DECODE(CEIL(TO_NUMBER(TO_DATE('&GL_DATE','DD-MON-RRRR') - TO_DATE(APS.DUE_DATE,'DD-MON-RRRR'))/30),1,'1-30 Days',2,'31-60 Days',3,'61-90 Days',4,'91-120 Days',5,'121-150 Days',6,'151-180 Days','181 Plus Days') as AGING,
IND_RCTA.CT_REFERENCE ORDER_NUMBER,
IND_OH.CUST_PO_NUMBER PO_NUMBER,
HCA.ACCOUNT_NUMBER BILL_TO_CUSTOMER_NO,
HP.PARTY_NAME BILL_TO_CUSTOMER_NAME,
IND_HCSU.LOCATION BILL_TO_LOCATION,
HL.ADDRESS1 ||' ' || HL.ADDRESS2 ||' '|| HL.ADDRESS3 || ' ' ||HL.PROVINCE||' '|| HL.CITY ||' '|| HL.STATE ||' '|| HL.POSTAL_CODE ||' '||HL.COUNTRY BILL_TO_ADDRESS,
IND_HCA.ACCOUNT_NUMBER SHIP_TO_CUSTOMER_NO,
IND_HP.PARTY_NAME SHIP_TO_CUSTOMER_NAME,
IND_HCSU1.LOCATION SHIP_TO_LOCATION,
IND_HP.ADDRESS1 ||' ' || IND_HP.ADDRESS2 || ' '|| IND_HP.ADDRESS3 ||' '|| IND_HP.ADDRESS4 ||' '||IND_HP.CITY ||' '||IND_HP.STATE ||' '||IND_HP.POSTAL_CODE ||' '|| IND_HP.COUNTRY SHIP_TO_ADDRESS,
IND_CTLA.INTERFACE_LINE_ATTRIBUTE3 DELIVERY_ID,
IND_RCTA.INTERFACE_HEADER_ATTRIBUTE15 GC_NOTE,
IND_OH.FREIGHT_CARRIER_CODE TRANSPORTER,
--APS.AMOUNT_DUE_ORIGINAL ORIGINAL_AMOUNT,
--APS.AMOUNT_APPLIED APPLIED_AMOUNT,
--APS.AMOUNT_DUE_REMAINING REMAIN_AMOUNT,
IND_RC.DESCRIPTION REGION_DESCRIPTION,
IND_RC.NAME REGION,
IND_APS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL,
IND_APS.AMOUNT_APPLIED AMOUNT_APPLIED,
IND_APS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING,
(IND_APS.AMOUNT_DUE_ORIGINAL * 0.98) + IND_APS.TAX_ORIGINAL LINE_AMT_98_TAX,
(IND_APS.AMOUNT_DUE_ORIGINAL * 0.02) "2% BILL AMOUNT"
/*
BSA.NAME SOURCE_NAME,
CTA.CUSTOMER_TRX_ID CUSTOMER_TRX_ID,
CTTA.NAME CLASS_NAME,
DECODE(CTTA.TYPE,'CM','Credit Memo','DM','Debit Memo','Invoice') CLASS_TYPE,
CTLA.GL_DATE GL_DATE,
CTA.INVOICE_CURRENCY_CODE,
HCSUA.LOCATION LOCATION*/
FROM
AR.RA_BATCH_SOURCES_ALL BSA,
AR.RA_CUSTOMER_TRX_ALL CTA,
AR.RA_CUST_TRX_TYPES_ALL CTTA,
AR.RA_CUST_TRX_LINE_GL_DIST_ALL CTLA,
AR.HZ_PARTIES HP,
AR.HZ_CUST_ACCOUNTS HCA,
AR.HZ_CUST_SITE_USES_ALL HCSUA,
AR.HZ_LOCATIONS HL,
AR.HZ_PARTY_SITES HPS,
AR.AR_PAYMENT_SCHEDULES_ALL APS,
AR.RA_CUSTOMER_TRX_ALL@QU_CIL.CIDC.CUMMINS.COM IND_RCTA ,
AR.HZ_PARTIES@QU_CIL.CIDC.CUMMINS.COM IND_HP,
AR.HZ_PARTY_SITES@QU_CIL.CIDC.CUMMINS.COM IND_HPS ,
AR.HZ_CUST_ACCOUNTS@QU_CIL.CIDC.CUMMINS.COM IND_HCA,
AR.HZ_CUST_ACCT_SITES_ALL@QU_CIL.CIDC.CUMMINS.COM IND_HCAS,
AR.HZ_CUST_SITE_USES_ALL@QU_CIL.CIDC.CUMMINS.COM IND_HCSU ,
AR.HZ_CUST_SITE_USES_ALL@QU_CIL.CIDC.CUMMINS.COM IND_HCSU1 ,
ONT.OE_ORDER_HEADERS_ALL@QU_CIL.CIDC.CUMMINS.COM IND_OH,
AR.RA_CUSTOMER_TRX_LINES_ALL@QU_CIL.CIDC.CUMMINS.COM IND_CTLA,
AR.AR_PAYMENT_SCHEDULES_ALL@QU_CIL.CIDC.CUMMINS.COM IND_APS,
AR.AR_COLLECTORS@QU_CIL.CIDC.CUMMINS.COM IND_RC,
AR.HZ_CUSTOMER_PROFILES@QU_CIL.CIDC.CUMMINS.COM IND_HCP
WHERE
CTA.BATCH_SOURCE_ID = BSA.BATCH_SOURCE_ID
AND CTA.CUST_TRX_TYPE_ID = CTTA.CUST_TRX_TYPE_ID
AND CTLA.CUSTOMER_TRX_ID = CTA.CUSTOMER_TRX_ID
AND CTLA.ACCOUNT_CLASS = 'REC'
AND HCA.CUST_ACCOUNT_ID = CTA.BILL_TO_CUSTOMER_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND HCSUA.SITE_USE_ID = CTA.BILL_TO_SITE_USE_ID
AND HL.LOCATION_ID = HPS.LOCATION_ID
AND HPS.PARTY_ID = HCA.PARTY_ID
AND HPS.ORIG_SYSTEM_REFERENCE = HCSUA.ORIG_SYSTEM_REFERENCE
AND APS.CUSTOMER_TRX_ID = CTLA.CUSTOMER_TRX_ID
AND IND_RCTA.TRX_NUMBER = CTA.TRX_NUMBER
AND CTA.TRX_NUMBER = '1107060005'
AND IND_HP.PARTY_ID=IND_HPS.PARTY_ID
AND IND_HP.PARTY_ID=IND_HCA.PARTY_ID
AND IND_HCA.CUST_ACCOUNT_ID=IND_HCAS.CUST_ACCOUNT_ID
AND IND_HPS.PARTY_SITE_ID=IND_HCAS.PARTY_SITE_ID
AND IND_HCAS.CUST_ACCT_SITE_ID=IND_HCSU.CUST_ACCT_SITE_ID
AND IND_HCSU.SITE_USE_ID=IND_RCTA.BILL_TO_SITE_USE_ID
AND IND_HCSU1.SITE_USE_ID = IND_RCTA.SHIP_TO_SITE_USE_ID
AND IND_OH.ORDER_NUMBER = IND_RCTA.CT_REFERENCE
AND IND_CTLA.CUSTOMER_TRX_ID = IND_RCTA.CUSTOMER_TRX_ID
AND IND_APS.CUSTOMER_TRX_ID = IND_RCTA.CUSTOMER_TRX_ID
AND IND_HCP.COLLECTOR_ID = IND_RC.COLLECTOR_ID
AND IND_HCP.CUST_ACCOUNT_ID = IND_HCA.CUST_ACCOUNT_ID
AND IND_HCP.SITE_USE_ID = IND_HCSU.SITE_USE_ID
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query Execution time problem [message #294137 is a reply to message #294045] |
Wed, 16 January 2008 10:29  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
I agree with LF that usually it's not up to the customer to decide whether they want/need MV's or not (thanks heaven it's not, my job is hard enough as it is without customers giving opinions on technical details  ).
One suggestion though: add a hint 'remote' in your statement (or something like that, syntax is in the performance and tuning guide). I recall that statements are processed on the "calling" database, therefor getting loads of data through the databaselink and then start processing it without indexes etc.
Not sure about this, but it is worth a try (and it's safe to try, if it doesn't work remove the hint...)
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 23:50:34 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
