Home » RDBMS Server » Performance Tuning » Query Optimization problem...
|Query Optimization problem... [message #201885]
||Tue, 07 November 2006 03:56
Registered: December 2005
When I m running this query its taking a very long time & after that its getting timed out.
NVL(PS.EXCHANGE_RATE, 1) TRX_EXCHANGE_RATE,
NVL(HCASA.TRANSLATED_CUSTOMER_NAME, PARTY.PARTY_NAME) CUST_NAME,
NVL(LOC.STATE, LOC.PROVINCE) STATE,
FROM RA_CUSTOMER_TRX_ALL TRX,
WHERE HR.SET_OF_BOOKS_ID = /*:P_SOB_ID*/85 AND
HR.SET_OF_BOOKS_ID = TYPE.SET_OF_BOOKS_ID AND
HR.SET_OF_BOOKS_ID = GL_DIST.SET_OF_BOOKS_ID AND
HR.ORGANIZATION_ID = PS.ORG_ID AND
GL_DIST.GL_DATE BETWEEN '01-APR-2000' AND TO_DATE('31-OCT-2006', 'dd-MON-YYYY') AND
GL_DIST.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID AND
GL_DIST.ACCOUNT_CLASS = 'REC' AND
GCC.CODE_COMBINATION_ID = GL_DIST.CODE_COMBINATION_ID AND
GCC.SEGMENT1 BETWEEN NVL(701, GCC.SEGMENT1) AND
NVL(777, GCC.SEGMENT1) AND TRX.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID AND
TRX.COMPLETE_FLAG = 'Y' AND
PS.GL_DATE_CLOSED > TO_DATE('31-OCT-2006', 'dd-MON-YYYY') - 1 AND
PS.CUSTOMER_ID = CUS.CUSTOMER_ID AND
TYPE.CUST_TRX_TYPE_ID = PS.CUST_TRX_TYPE_ID AND
TYPE.ORG_ID = TRX.ORG_ID AND TYPE.POST_TO_GL <> 'N' AND
TYPE.ACCOUNTING_AFFECT_FLAG <> 'N' AND
PS.CUSTOMER_ID = NVL(/*:P_CUSTOMER_ID*/NULL, BILL_TO_CUSTOMER_ID) AND
NVL(INTERFACE_HEADER_ATTRIBUTE1, '~') =
NVL(INTERFACE_HEADER_ATTRIBUTE1, '~')) AND
A.SITE_USE_ID(+) = PS.CUSTOMER_SITE_USE_ID AND
A.CUST_ACCT_SITE_ID = HCASA.CUST_ACCT_SITE_ID(+) AND
HCASA.PARTY_SITE_ID = C.PARTY_SITE_ID(+) AND
C.PARTY_ID = PARTY.PARTY_ID(+) AND
C.LOCATION_ID = LOC.LOCATION_ID(+) AND
LOC.COUNTRY = TER.TERRITORY_CODE(+)
ORDER BY SEGMENT1, NAME, TRX_DATE DESC
I dont have much idea on query optimization & dont know how to analyze explain plan.Please help me out.
The explain plan is attached in the excel file which I have taken out from PL/SQL Developer.
(Size: 18.50KB, Downloaded 284 times)
|Re: Query Optimization problem... [message #203017 is a reply to message #201885]
||Mon, 13 November 2006 10:05
Registered: March 2005
I don't know much about performance (trying to learn...), so let's hope someone else will jump in shortly.
Meanwhile, I did take a look at your statement and plan, out of curiousity. I would start by taking a closer look at 2 things:
1) you use the views hr_operating_units and ra_customers, which both contain more data than you actually need, it seems better to use the base tables (hr_all_organization_units and hz_cust_accounts);
2) the following part:
AND gcc.segment1 BETWEEN nvl(701
seems a bit odd, because your "NVL-ing" two numbers, don't you mean:
and (gcc.segment1 between 701 and 777
OR gcc.segment1 is null)
And if I'm correct, the use of the nvl's will disable the use of the index that is on segment1 - not sure if that good or bad, but the optimizer will hopefully figure that out .
Let's what happens after this!
And if you test the statement: I would use actual values for the two parameters, because they seem to be pretty distinctive, the plan will probably be completely different when you use these, instead of replacing it with NULL like you do in the statement you posted.
|Re: Query Optimization problem... [message #203092 is a reply to message #203017]
||Mon, 13 November 2006 19:50
Registered: October 2005
Location: Melbourne, Australia
How many rows do you expect to be returned?|
You Explain Plan indicates that Oracle expects only a few rows returned. If it is taking a while, then this is probably incorrect.
Add a line to the SQL just above the ORDER BY clause:
How long does it take now?
If it is fast now (say <10 sec), then the SQL is probably going to return millions of rows. Try it with AND ROWNUM <= 10000 and see how long it takes. If it still returns in a reasonable time, then you need to somehow instruct the optimizer that it is going to return a lot of data. You can use the CARDINALITY or USE_HASH hints to achieve this.
If it is still slow (say > 1min), then the SQL is processing millions of rows, but not finding any matching ones. This probably means that it is using the wrong index on one of the tables. Check all of the indexes in your plan that are performing a RANGE SCAN. Make sure that the indexes being used are those on the join columns, not on columns you have been using as filters. Use a NO_INDEX hint to stop it from using a particular index.
Current Time: Fri Oct 21 09:00:19 CDT 2016
Total time taken to generate the page: 0.14642 seconds