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 Go to next message
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 #293878 is a reply to message #293863] Tue, 15 January 2008 07:32 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If possible, you might create materialized views and use them instead of tables via database links.
Re: Query Execution time problem [message #293995 is a reply to message #293878] Tue, 15 January 2008 21:59 Go to previous messageGo to next message
mehulmb
Messages: 25
Registered: May 2006
Location: Pune
Junior Member

ACTUALLY OUR CLIENT DOESN'T NEED TO USED MATERIALIZED VIEW IN THE QUERY. IS THERE ANY OTHER SOLUTIONS FOR THE QUERY?
Re: Query Execution time problem [message #294023 is a reply to message #293995] Wed, 16 January 2008 01:39 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you explain your last statement, please? What does it mean that client doesn't need materialized views?
Re: Query Execution time problem [message #294027 is a reply to message #294023] Wed, 16 January 2008 02:06 Go to previous messageGo to next message
mehulmb
Messages: 25
Registered: May 2006
Location: Pune
Junior Member

Actually client told us that don't used materialized view for the dblink....thats y is there any other solution
Re: Query Execution time problem [message #294045 is a reply to message #294027] Wed, 16 January 2008 04:24 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In my opinion, clients should be interested in results, not the way they are achieved. From my own experience, I've noticed that queries that use tables via database links are enormously slower than the same queries that use materialized views. The difference was HUGE!

There may be a way to do what you need and I hope you'll find it. Once you do that, please, share the experience.
Re: Query Execution time problem [message #294137 is a reply to message #294045] Wed, 16 January 2008 10:29 Go to previous message
skooman
Messages: 912
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 Wink).

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...)
Previous Topic: Approve Purchase orders and Requisitions on Blackberry
Next Topic: Need advice from all HRMS Functional consultants
Goto Forum:
  


Current Time: Mon Dec 05 08:37:34 CST 2016

Total time taken to generate the page: 0.05760 seconds