Home » RDBMS Server » Performance Tuning » SQL query needs tuning
SQL query needs tuning [message #248745] Sun, 01 July 2007 08:21 Go to next message
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 385 times)
Re: SQL query needs tuning [message #248776 is a reply to message #248745] Sun, 01 July 2007 22:16 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #248854 is a reply to message #248797] Mon, 02 July 2007 08:13 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Why all the double quotes aroundnevery column and table name? It makes it very difficult to read.
icon8.gif  Re: SQL query needs tuning [message #248888 is a reply to message #248854] Mon, 02 July 2007 10:27 Go to previous messageGo to next message
kpremsagar
Messages: 26
Registered: June 2007
Junior Member
Hi,
Sorry about that! The query was generated by an ETL tool.
Regards
Prem
Re: SQL query needs tuning [message #249093 is a reply to message #248888] Tue, 03 July 2007 06:24 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: SQL query needs tuning [message #249341 is a reply to message #249093] Wed, 04 July 2007 02:23 Go to previous message
kpremsagar
Messages: 26
Registered: June 2007
Junior Member
Hi,
Thanks a ton! That was really helpful.
Will try to get back if I have any clarifications.

Thanks
Prem
Previous Topic: Performance Problem
Next Topic: Perfomance problem due to Distinct clause
Goto Forum:
  


Current Time: Sat Dec 10 01:11:23 CST 2016

Total time taken to generate the page: 0.13534 seconds