Home » RDBMS Server » Performance Tuning » Query Optimization problem...
| Query Optimization problem... [message #201885] |
Tue, 07 November 2006 03:56  |
deepayan
Messages: 51
Registered: December 2005
|
Member |
|
|
Dear all,
When I m running this query its taking a very long time & after that its getting timed out.
SELECT GCC.SEGMENT1,
HR.NAME,
TRX.TRX_DATE,
TRX.INTERFACE_HEADER_ATTRIBUTE1,
TRX.CUSTOMER_TRX_ID,
TRX.BILL_TO_CUSTOMER_ID,
TRX.ORG_ID,
TRX.TRX_NUMBER,
GL_DIST.GL_DATE TRX_GL_DATE,
TRX.INVOICE_CURRENCY_CODE,
NVL(PS.EXCHANGE_RATE, 1) TRX_EXCHANGE_RATE,
CUS.CUSTOMER_NAME,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
GCC.SEGMENT9,
NVL(HCASA.TRANSLATED_CUSTOMER_NAME, PARTY.PARTY_NAME) CUST_NAME,
LOC.ADDRESS1 ADDRESS1,
LOC.ADDRESS2 ADDRESS2,
LOC.ADDRESS3 ADDRESS3,
LOC.ADDRESS4 ADDRESS4,
LOC.CITY CITY,
NVL(LOC.STATE, LOC.PROVINCE) STATE,
LOC.POSTAL_CODE POSTAL_CODE,
TER.TERRITORY_SHORT_NAME COUNTRY
FROM RA_CUSTOMER_TRX_ALL TRX,
RA_CUST_TRX_LINE_GL_DIST_ALL GL_DIST,
HR_OPERATING_UNITS HR,
RA_CUSTOMERS CUS,
AR_PAYMENT_SCHEDULES_ALL PS,
GL_CODE_COMBINATIONS GCC,
RA_CUST_TRX_TYPES_ALL TYPE,
HZ_CUST_SITE_USES_ALL A,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_PARTY_SITES C,
HZ_PARTIES PARTY,
HZ_LOCATIONS LOC,
FND_TERRITORIES_TL TER
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(/*:P_INTERFACE_HEADER_ATTRIBUTE1*/NULL,
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.
-
Attachment: explain.xls
(Size: 18.50KB, Downloaded 1252 times)
|
|
|
|
| Re: Query Optimization problem... [message #203017 is a reply to message #201885] |
Mon, 13 November 2006 10:05  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Hi Deepayan,
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
,gcc.segment1) AND
nvl(777
,gcc.segment1)
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.
Regards,
Sabine
|
|
|
|
| Re: Query Optimization problem... [message #203092 is a reply to message #203017] |
Mon, 13 November 2006 19:50 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
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.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Mon Apr 29 13:11:51 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
