Home » RDBMS Server » Performance Tuning » SQL query needs tuning
| SQL query needs tuning [message #248745] |
Sun, 01 July 2007 08:21  |
kpremsagar
Messages: 26
Registered: June 2007
|
Junior Member |
|
|
Hi,
We have a query that needs tuning. It joins on 9 tables and returns 539000 rows. It takes almost 1 hr to run (based on the dates given in where clause. 2 days range takes 1 hr. 10 days range takes much much longer)
I have attached the query, explain plan, the index info on all 9 tables and the no of rows in each table.
I see from explain plan that it has 2 main joins at the top most level, a hash and a nest. The hash join looks fine, but the nest is the culprit here.
Drilling into it, I see that the nested join portion has RA_CUST_TRX_LINE_GL_DIST_ALL as the driving table. This is the table with most no of records. The primary key for the result set comes from this table.
Is there anything I can do to tune this query? Will changing the driving table give better performance? How do I achieve it? (I am trying with a few hints here.. havent used them previously though)
Pls do let me know your suggestions.
Regards
prem
-
Attachment: SR_SUPRA.zip
(Size: 9.96KB, Downloaded 1633 times)
|
|
|
|
| Re: SQL query needs tuning [message #248776 is a reply to message #248745] |
Sun, 01 July 2007 22:16  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I'd like to help, but I don't open potentially virus-prone documents.
Paste all of the content into the forum enclosed in [code]...[/code] tags.
Based on what you've told us, this link might be of some help.
Ross Leishman
|
|
|
|
| Re: SQL query needs tuning [message #248793 is a reply to message #248776] |
Mon, 02 July 2007 00:53 |
kpremsagar
Messages: 26
Registered: June 2007
|
Junior Member |
|
|
Here is the explain plan for the query
SQL Cost No of Rows CPU_COST ACCESS_PREDICATES FILTER_PREDICATES TIME
1 SELECT STATEMENT 40198 58 565875981 483
2 CONCATENATION
3 HASH JOIN 2182 25 46719145 "RA_CUST_TRX_LINE_GL_DIST_ALL"."SET_OF_BOOKS_ID"="GL_SETS_OF_BOOKS"."SET_OF_BOOKS_ID" 27
4 TABLE ACCESSFULL GL_SETS_OF_BOOKS(TABLE) 3 21 32896 1
5 NESTED LOOPS 2179 25 43189071 27
6 NESTED LOOPS 2153 25 42959285 26
7 NESTED LOOPS 2093 60 42389394 26
8 NESTED LOOPS 1566 142 38194128 19
9 NESTED LOOPS 1467 24 37209814 18
10 NESTED LOOPS 1443 24 36989220 18
11 HASH JOIN 1419 24 36772705 "RA_CUSTOMER_TRX_ALL"."ORG_ID"="RA_CUST_TRX_TYPES_ALL"."ORG_ID" AND "RA_CUSTOMER_TRX_ALL"."CUST_TRX_TYPE_ID"="RA_CUST_TRX_TYPES_ALL"."CUST_TRX_TYPE_ID" 18
12 HASH JOIN 221 14 9522099 "FND_LOOKUP_VALUES"."LOOKUP_CODE"="RA_CUST_TRX_TYPES_ALL"."TYPE" 3
13 TABLE ACCESSFULL RA_CUST_TRX_TYPES_ALL(TABLE) 210 15 5937744 "RA_CUST_TRX_TYPES_ALL"."ORG_ID"=625 3
14 TABLE ACCESSBY INDEX ROWID FND_LOOKUP_VALUES(TABLE) 11 24 88176 1
15 INDEXRANGE SCAN FND_LOOKUP_VALUES_U1(INDEX (UNIQUE)) 3 24 26164 "FND_LOOKUP_VALUES"."LOOKUP_TYPE"='TRX TYPES' 1
16 TABLE ACCESSBY INDEX ROWID RA_CUSTOMER_TRX_ALL(TABLE) 1197 239 23733078 "RA_CUSTOMER_TRX_ALL"."ORG_ID"=625 AND NVL("RA_CUSTOMER_TRX_ALL"."INTERFACE_HEADER_CONTEXT",'XX')<>'OFTC CONV' AND "RA_CUSTOMER_TRX_ALL"."COMPLETE_FLAG"='Y' 15
17 INDEXRANGE SCAN RA_CUSTOMER_TRX_N14(INDEX) 23 5468 1289793 "RA_CUSTOMER_TRX_ALL"."LAST_UPDATE_DATE">TO_DATE('2007-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "RA_CUSTOMER_TRX_ALL"."LAST_UPDATE_DATE"<=TO_DATE('2007-01-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') 1
18 INDEXUNIQUE SCAN HZ_CUST_ACCOUNTS_U1(INDEX (UNIQUE)) 1 1 9021 "RA_CUSTOMER_TRX_ALL"."BILL_TO_CUSTOMER_ID"="HZ_CUST_ACCOUNTS"."CUST_ACCOUNT_ID" 1
19 TABLE ACCESSBY INDEX ROWID FND_USER(TABLE) 1 1 9191 1
20 INDEXUNIQUE SCAN FND_USER_U1(INDEX (UNIQUE)) 0 1 1900 "RA_CUSTOMER_TRX_ALL"."CREATED_BY"="FND_USER"."USER_ID" 1
21 TABLE ACCESSBY INDEX ROWID RA_CUSTOMER_TRX_LINES_ALL(TABLE) 6 6 62963 NVL("RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_CONTEXT",'XX')<>'OFTC CONVERSION' AND NVL("RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_CONTEXT",'XX')<>'ION TRACK CONV' 1
22 INDEXRANGE SCAN GE_RA_CUSTOMER_TRX_LINES_ALL_N(INDEX) 2 6 16493 "RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_ID"="RA_CUSTOMER_TRX_ALL"."CUSTOMER_TRX_ID" 1
23 TABLE ACCESSBY INDEX ROWID RA_CUST_TRX_LINE_GL_DIST_ALL(TABLE) 5 1 41290 "RA_CUST_TRX_LINE_GL_DIST_ALL"."ACCOUNT_CLASS"='FREIGHT' OR "RA_CUST_TRX_LINE_GL_DIST_ALL"."ACCOUNT_CLASS"='REV' 1
24 INDEXRANGE SCAN RA_CUST_TRX_LINE_GL_DIST_N1(INDEX) 3 3 23094 ############################################################################################################################################################################################################################################################### "RA_CUST_TRX_LINE_GL_DIST_ALL"."CUSTOMER_TRX_LINE_ID" IS NOT NULL 1
25 TABLE ACCESSBY INDEX ROWID GL_CODE_COMBINATIONS(TABLE) 1 1 9498 "GL_CODE_COMBINATIONS"."SEGMENT3"<'5' AND "GL_CODE_COMBINATIONS"."SEGMENT3">'4' AND "GL_CODE_COMBINATIONS"."SEGMENT1"<>'513' AND "GL_CODE_COMBINATIONS"."SEGMENT1"<>'511' 1
26 INDEXUNIQUE SCAN GL_CODE_COMBINATIONS_U1(INDEX (UNIQUE)) 0 1 1900 "RA_CUST_TRX_LINE_GL_DIST_ALL"."CODE_COMBINATION_ID"="GL_CODE_COMBINATIONS"."CODE_COMBINATION_ID" 1
27 TABLE ACCESSBY INDEX ROWID FND_USER(TABLE) 1 1 9191 1
28 INDEXUNIQUE SCAN FND_USER_U1(INDEX (UNIQUE)) 0 1 1900 "RA_CUSTOMER_TRX_LINES_ALL"."CREATED_BY"="FND_USER_1"."USER_ID" 1
29 NESTED LOOPS 38016 33 519156837 457
30 NESTED LOOPS 37983 33 518853519 456
31 NESTED LOOPS 37950 33 518550202 456
32 HASH JOIN 37917 33 518252494 "FND_LOOKUP_VALUES"."LOOKUP_CODE"="RA_CUST_TRX_TYPES_ALL"."TYPE" 456
33 TABLE ACCESSBY INDEX ROWID FND_LOOKUP_VALUES(TABLE) 11 24 88176 1
34 INDEXRANGE SCAN FND_LOOKUP_VALUES_U1(INDEX (UNIQUE)) 3 24 26164 "FND_LOOKUP_VALUES"."LOOKUP_TYPE"='TRX TYPES' 1
35 HASH JOIN 37906 34 514665790 "RA_CUSTOMER_TRX_ALL"."ORG_ID"="RA_CUST_TRX_TYPES_ALL"."ORG_ID" AND "RA_CUSTOMER_TRX_ALL"."CUST_TRX_TYPE_ID"="RA_CUST_TRX_TYPES_ALL"."CUST_TRX_TYPE_ID" 455
36 TABLE ACCESSFULL RA_CUST_TRX_TYPES_ALL(TABLE) 210 15 5937744 "RA_CUST_TRX_TYPES_ALL"."ORG_ID"=625 3
37 NESTED LOOPS 37695 469 505187367 453
38 NESTED LOOPS 25761 5959 393496110 310
39 HASH JOIN 13827 5959 281934230 "RA_CUST_TRX_LINE_GL_DIST_ALL"."CODE_COMBINATION_ID"="GL_CODE_COMBINATIONS"."CODE_COMBINATION_ID" 166
40 TABLE ACCESSFULL GL_CODE_COMBINATIONS(TABLE) 401 4259 77127356 "GL_CODE_COMBINATIONS"."SEGMENT3"<'5' AND "GL_CODE_COMBINATIONS"."SEGMENT3">'4' AND "GL_CODE_COMBINATIONS"."SEGMENT1"<>'513' AND "GL_CODE_COMBINATIONS"."SEGMENT1"<>'511' 5
41 HASH JOIN 13426 7139 199962595 "RA_CUST_TRX_LINE_GL_DIST_ALL"."SET_OF_BOOKS_ID"="GL_SETS_OF_BOOKS"."SET_OF_BOOKS_ID" 162
42 TABLE ACCESSFULL GL_SETS_OF_BOOKS(TABLE) 3 21 32896 1
43 TABLE ACCESSBY INDEX ROWID RA_CUST_TRX_LINE_GL_DIST_ALL(TABLE) 13422 7139 195721121 ############################################################################################################################################################################################################################################################### 162
44 INDEXRANGE SCAN ROI_CUST_TRX_LINE_DIST_ALL_N1(INDEX) 362 81488 18973397 "RA_CUST_TRX_LINE_GL_DIST_ALL"."LAST_UPDATE_DATE">TO_DATE('2007-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "RA_CUST_TRX_LINE_GL_DIST_ALL"."LAST_UPDATE_DATE"<=TO_DATE('2007-01-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss') 5
45 TABLE ACCESSBY INDEX ROWID RA_CUSTOMER_TRX_LINES_ALL(TABLE) 2 1 18722 NVL("RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_CONTEXT",'XX')<>'OFTC CONVERSION' AND NVL("RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_CONTEXT",'XX')<>'ION TRACK CONV' 1
46 INDEXUNIQUE SCAN RA_CUSTOMER_TRX_LINES_U1(INDEX (UNIQUE)) 1 1 9021 "RA_CUST_TRX_LINE_GL_DIST_ALL"."CUSTOMER_TRX_LINE_ID"="RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_LINE_ID" 1
47 TABLE ACCESSBY INDEX ROWID RA_CUSTOMER_TRX_ALL(TABLE) 2 1 18743 ############################################################################################################################################################################################################################################################### 1
48 INDEXUNIQUE SCAN RA_CUSTOMER_TRX_U1(INDEX (UNIQUE)) 1 1 9021 "RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_ID"="RA_CUSTOMER_TRX_ALL"."CUSTOMER_TRX_ID" 1
49 INDEXUNIQUE SCAN HZ_CUST_ACCOUNTS_U1(INDEX (UNIQUE)) 1 1 9021 "RA_CUSTOMER_TRX_ALL"."BILL_TO_CUSTOMER_ID"="HZ_CUST_ACCOUNTS"."CUST_ACCOUNT_ID" 1
50 TABLE ACCESSBY INDEX ROWID FND_USER(TABLE) 1 1 9191 1
51 INDEXUNIQUE SCAN FND_USER_U1(INDEX (UNIQUE)) 0 1 1900 "RA_CUSTOMER_TRX_LINES_ALL"."CREATED_BY"="FND_USER_1"."USER_ID" 1
52 TABLE ACCESSBY INDEX ROWID FND_USER(TABLE) 1 1 9191 1
53 INDEXUNIQUE SCAN FND_USER_U1(INDEX (UNIQUE)) 0 1 1900 "RA_CUSTOMER_TRX_ALL"."CREATED_BY"="FND_USER"."USER_ID" 1
|
|
|
|
| Re: SQL query needs tuning [message #248794 is a reply to message #248793] |
Mon, 02 July 2007 00:55 |
kpremsagar
Messages: 26
Registered: June 2007
|
Junior Member |
|
|
Here is the SQL query
SELECT DECODE("RA_CUSTOMER_TRX_ALL"."CUST_TRX_TYPE_ID",
1470,
'SERVICE',
NULL),
DECODE("RA_CUSTOMER_TRX_LINES_ALL"."SALES_ORDER",
NULL,
"RA_CUSTOMER_TRX_ALL"."INTERFACE_HEADER_ATTRIBUTE1",
"RA_CUSTOMER_TRX_LINES_ALL"."SALES_ORDER"),
DECODE("RA_CUSTOMER_TRX_ALL"."CUST_TRX_TYPE_ID", 1470, 0, NULL),
DECODE("RA_CUST_TRX_LINE_GL_DIST_ALL"."ACCOUNT_CLASS",
'REV',
'Sales Revenue',
'Freight'),
DECODE("RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_CONTEXT",
'OKS CONTRACTS',
((((('Contract ' ||
"RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_ATTRIBUTE1") ||
'FROM ') ||
"RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_ATTRIBUTE4") ||
'TO ') ||
"RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_ATTRIBUTE5"),
' '),
nvl("RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_CONTEXT",
'AR MANUAL'),
"RA_CUSTOMER_TRX_ALL"."CREATION_DATE",
"FND_USER_1"."USER_NAME",
"FND_USER"."USER_NAME",
"RA_CUSTOMER_TRX_LINES_ALL"."ACCOUNTING_RULE_DURATION",
"RA_CUST_TRX_LINE_GL_DIST_ALL"."ACCOUNT_CLASS",
"RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_CONTEXT",
"FND_LOOKUP_VALUES"."MEANING",
"RA_CUST_TRX_TYPES_ALL"."NAME",
"RA_CUSTOMER_TRX_LINES_ALL"."UOM_CODE",
"RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE",
"RA_CUST_TRX_LINE_GL_DIST_ALL"."CUST_TRX_LINE_GL_DIST_ID",
"RA_CUSTOMER_TRX_LINES_ALL"."LINE_NUMBER",
"RA_CUSTOMER_TRX_ALL"."TRX_NUMBER",
"RA_CUSTOMER_TRX_ALL"."TRX_DATE",
"RA_CUSTOMER_TRX_LINES_ALL"."SALES_ORDER_LINE",
"RA_CUSTOMER_TRX_ALL"."REASON_CODE",
"RA_CUSTOMER_TRX_ALL"."PURCHASE_ORDER",
"RA_CUSTOMER_TRX_LINES_ALL"."QUANTITY_INVOICED",
"RA_CUSTOMER_TRX_LINES_ALL"."QUANTITY_CREDITED",
"RA_CUSTOMER_TRX_LINES_ALL"."UNIT_STANDARD_PRICE",
"RA_CUSTOMER_TRX_LINES_ALL"."UNIT_SELLING_PRICE",
"RA_CUST_TRX_LINE_GL_DIST_ALL"."AMOUNT",
"RA_CUST_TRX_LINE_GL_DIST_ALL"."ACCTD_AMOUNT",
"GL_SETS_OF_BOOKS"."CURRENCY_CODE",
"RA_CUSTOMER_TRX_ALL"."INVOICE_CURRENCY_CODE",
"RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_ATTRIBUTE6",
"RA_CUSTOMER_TRX_LINES_ALL"."INVENTORY_ITEM_ID",
"RA_CUSTOMER_TRX_LINES_ALL"."WAREHOUSE_ID",
"RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_ATTRIBUTE3",
"RA_CUSTOMER_TRX_LINES_ALL"."DESCRIPTION",
"RA_CUSTOMER_TRX_ALL"."BATCH_SOURCE_ID",
"RA_CUSTOMER_TRX_ALL"."PRIMARY_SALESREP_ID",
"RA_CUSTOMER_TRX_ALL"."ORG_ID",
"RA_CUST_TRX_LINE_GL_DIST_ALL"."CODE_COMBINATION_ID",
"RA_CUSTOMER_TRX_ALL"."BILL_TO_SITE_USE_ID",
"RA_CUSTOMER_TRX_ALL"."SHIP_TO_SITE_USE_ID",
"RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_ATTRIBUTE1",
"RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_ATTRIBUTE2",
"RA_CUSTOMER_TRX_ALL"."SOLD_TO_CUSTOMER_ID"
FROM "AR"."HZ_CUST_ACCOUNTS" "HZ_CUST_ACCOUNTS",
"APPLSYS"."FND_LOOKUP_VALUES" "FND_LOOKUP_VALUES",
"AR"."RA_CUST_TRX_TYPES_ALL" "RA_CUST_TRX_TYPES_ALL",
"APPLSYS"."FND_USER" "FND_USER",
"AR"."RA_CUSTOMER_TRX_ALL" "RA_CUSTOMER_TRX_ALL",
"APPLSYS"."FND_USER" "FND_USER_1",
"AR"."RA_CUSTOMER_TRX_LINES_ALL" "RA_CUSTOMER_TRX_LINES_ALL",
"GL"."GL_SETS_OF_BOOKS" "GL_SETS_OF_BOOKS",
"AR"."RA_CUST_TRX_LINE_GL_DIST_ALL" "RA_CUST_TRX_LINE_GL_DIST_ALL",
"GL"."GL_CODE_COMBINATIONS" "GL_CODE_COMBINATIONS"
|
|
|
|
| Re: SQL query needs tuning [message #248796 is a reply to message #248794] |
Mon, 02 July 2007 00:57 |
kpremsagar
Messages: 26
Registered: June 2007
|
Junior Member |
|
|
Oops! missed the where clause. Here it is!
WHERE ("RA_CUST_TRX_LINE_GL_DIST_ALL"."ACCOUNT_SET_FLAG" = 'N')
AND ("RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE" >= TO_DATE('01012002', 'ddmmyyyy'))
AND ("RA_CUST_TRX_LINE_GL_DIST_ALL"."GL_DATE" <= TO_DATE('01012012', 'ddmmyyyy'))
AND ("RA_CUST_TRX_LINE_GL_DIST_ALL"."ACCOUNT_CLASS" IN ('REV', 'FREIGHT'))
AND ("RA_CUST_TRX_LINE_GL_DIST_ALL"."SET_OF_BOOKS_ID" = "GL_SETS_OF_BOOKS"."SET_OF_BOOKS_ID")
AND ("RA_CUST_TRX_LINE_GL_DIST_ALL"."CUSTOMER_TRX_LINE_ID" = "RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_LINE_ID")
AND ("RA_CUST_TRX_LINE_GL_DIST_ALL"."CODE_COMBINATION_ID" = "GL_CODE_COMBINATIONS"."CODE_COMBINATION_ID")
AND not nvl("RA_CUSTOMER_TRX_LINES_ALL"."INTERFACE_LINE_CONTEXT", 'XX') IN ('OFTC CONVERSION', 'ION TRACK CONV')
AND ("RA_CUSTOMER_TRX_LINES_ALL"."CREATED_BY" = "FND_USER_1"."USER_ID")
AND ("RA_CUSTOMER_TRX_LINES_ALL"."CUSTOMER_TRX_ID" = "RA_CUSTOMER_TRX_ALL"."CUSTOMER_TRX_ID")
AND (nvl("RA_CUSTOMER_TRX_ALL"."INTERFACE_HEADER_CONTEXT", 'XX') <> 'OFTC CONV')
AND ("RA_CUSTOMER_TRX_ALL"."COMPLETE_FLAG" = 'Y')
AND ("RA_CUSTOMER_TRX_ALL"."ORG_ID" = 625)
AND ("RA_CUSTOMER_TRX_ALL"."CREATED_BY" = "FND_USER"."USER_ID")
AND ("RA_CUSTOMER_TRX_ALL"."ORG_ID" = "RA_CUST_TRX_TYPES_ALL"."ORG_ID")
AND ("RA_CUSTOMER_TRX_ALL"."CUST_TRX_TYPE_ID" = "RA_CUST_TRX_TYPES_ALL"."CUST_TRX_TYPE_ID")
AND ("RA_CUSTOMER_TRX_ALL"."BILL_TO_CUSTOMER_ID" = "HZ_CUST_ACCOUNTS"."CUST_ACCOUNT_ID")
AND ("FND_LOOKUP_VALUES"."LOOKUP_CODE" = "RA_CUST_TRX_TYPES_ALL"."TYPE")
AND ("FND_LOOKUP_VALUES"."LOOKUP_TYPE" = 'TRX TYPES')
AND ("GL_CODE_COMBINATIONS"."SEGMENT3" > '4')
AND ("GL_CODE_COMBINATIONS"."SEGMENT3" < '5')
AND ("GL_CODE_COMBINATIONS"."SEGMENT1" <> '513')
AND ("GL_CODE_COMBINATIONS"."SEGMENT1" <> '511')
AND (
(
"RA_CUST_TRX_LINE_GL_DIST_ALL"."LAST_UPDATE_DATE" > TO_DATE('01012007', 'ddmmyyyy')
AND "RA_CUST_TRX_LINE_GL_DIST_ALL"."LAST_UPDATE_DATE" <= TO_DATE('03012007', 'ddmmyyyy')
)
or
(
"RA_CUSTOMER_TRX_ALL"."LAST_UPDATE_DATE" > TO_DATE('01012007', 'ddmmyyyy')
AND "RA_CUSTOMER_TRX_ALL"."LAST_UPDATE_DATE" <= TO_DATE('03012007', 'ddmmyyyy')
)
)
|
|
|
|
| Re: SQL query needs tuning [message #248797 is a reply to message #248796] |
Mon, 02 July 2007 00:58 |
kpremsagar
Messages: 26
Registered: June 2007
|
Junior Member |
|
|
And here is the table row count info.
Table name No of rows
"GL_SETS_OF_BOOKS" 21
"RA_CUST_TRX_TYPES_ALL" 410
"FND_USER" 8368
"FND_USER_1" 8368
"GL_CODE_COMBINATIONS" 154972
"FND_LOOKUP_VALUES" 172691
"HZ_CUST_ACCOUNTS" 1406487
"RA_CUSTOMER_TRX_ALL" 8888095
"RA_CUSTOMER_TRX_LINES_ALL" 28906717
"AR"."RA_CUST_TRX_LINE_GL_DIST_ALL" "RA_CUST_TRX_LINE_GL_DIST_ALL" 134509167
|
|
|
|
|
|
|
|
| Re: SQL query needs tuning [message #249093 is a reply to message #248888] |
Tue, 03 July 2007 06:24 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Actually that plan doesn't look too bad. The OR condition at the end is causing it to be split out into two queries: one driving off RA_CUST_TRX_LINE_GL_DIST_ALL.LAST_UPDATE_DATE and the other driving off RA_CUSTOMER_TRX_ALL.LAST_UPDATE_DATE. If these are big tables and your query is accessing less than 1% of them, then this is a good thing.
For the number of rows you are returning, I would probably like to see more hash joins and fewer Nested Loops, but then you probably don't want to be full scanning some of those bigger tables - so Nested Loops might be unavoidable.
I would check out whether the non-unique Nested Loops are fetching more rows than are required and then filtering on a non-indexed column. Take a look at this guide, especially the section on Nested Loops joins.
The next step in getting help on the forum would be to trace it and post the TK*Prof output here.
Ross Leishman
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 07:58:18 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
