Home » RDBMS Server » Performance Tuning » Please help me Tune this query
Please help me Tune this query [message #354174] Thu, 16 October 2008 16:25 Go to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
I am executing the following query in a loop multiple times. This query takes lot of time to complete on each occurance, thus increasing the amount of time my concurrent program to complete. Please help me tune this. I know there is a Full table scan happening on MTL_SYSTEM_ITEMS but dont know how to avoid this. Any help in this regard will be appreciated.

Here symjb_rawdata_headers_all & symjb_rawdata_lines_all are custom tables.

SELECT /*+ INDEX(hca HZ_CUST_ACCOUNTS_U2) PARALLEL(msi,5)*/
                              symom_staging_seq.nextval AS stg_seq_id
                             ,mp.organization_code     --Site
                             ,srla.process_date     --Line Date
                             ,TO_CHAR(TRUNC(srla.process_date),'MON-RR')  AS period    --Period
                             ,hca.cust_account_id AS customer_id   --Customer Id
                             ,hp.party_name as customer_name       --Customer Name
                             ,FND_PROFILE.VALUE('ORG_ID')      --Organization Id
                             ,ltrim(srla.customer,0)                    --Key Identifier 1
                             ,ltrim(srla.lbxno,0)                     --Key Identifier 2
                             ,msi.inventory_item_id            --Inventory Item Id
                             ,msi.segment19            --Inventory Item
                             ,msi.description                  --Inventory_item_description
                             ,msi.primary_uom_code        --primary_uom_code
                             ,srla.col18            --Ordered Quantity
                             ,hca.cust_account_id AS sold_to_org_id    --sold_to_org_id
                             ,mp.organization_id AS ship_from_org_id
                        FROM symjb_rawdata_headers_all srha
                            ,symjb_rawdata_lines_all srla
                            ,mtl_system_items msi
                            ,mtl_parameters mp
                            ,hz_cust_accounts hca
                            ,hz_parties hp
                       WHERE mp.organization_id = srla.siteno
                         AND msi.organization_id = srla.siteno
                         AND hca.party_id = hp.party_id
                         AND hca.status = 'A'
                         AND msi.enabled_flag = 'Y'
                         AND hca.account_number = srha.clientno
                         AND msi.attribute8 = 15006
                         AND srha.ldr_id = srla.ldr_id
                         AND srha.ldr_id = 'REMWLBXJB800501015200820081016161519'
                         AND srla.siteno = 391
                         AND srla.clientno = 80050
                         AND ltrim(srla.customer,0) = 1689751
                         AND ltrim(srla.lbxno,0)    = 71905


Explain Plan:

Plan
SELECT STATEMENT  CHOOSECost: 213  Bytes: 236  Cardinality: 1  								
	15 SEQUENCE VCSPOC.SYMOM_STAGING_SEQ 							
		14 NESTED LOOPS  Cost: 213  Bytes: 236  Cardinality: 1  						
			11 NESTED LOOPS  Cost: 212  Bytes: 213  Cardinality: 1  					
				8 HASH JOIN  Cost: 210  Bytes: 194  Cardinality: 1  				
					6 HASH JOIN  Cost: 208  Bytes: 154  Cardinality: 1  			
						4 NESTED LOOPS  Cost: 3  Bytes: 102  Cardinality: 1  		
							2 TABLE ACCESS BY INDEX ROWID INV.MTL_PARAMETERS Cost: 1  Bytes: 8  Cardinality: 1  	
								1 INDEX UNIQUE SCAN UNIQUE INV.MTL_PARAMETERS_U1 Cardinality: 1  
							3 TABLE ACCESS FULL VCSPOC.SYMJB_RAWDATA_LINES_ALL Cost: 2  Bytes: 94  Cardinality: 1  	
						5 TABLE ACCESS FULL INV.MTL_SYSTEM_ITEMS_B Cost: 205  Bytes: 52  Cardinality: 1  		
					7 TABLE ACCESS FULL VCSPOC.SYMJB_RAWDATA_HEADERS_ALL Cost: 2  Bytes: 40  Cardinality: 1  			
				10 TABLE ACCESS BY INDEX ROWID AR.HZ_CUST_ACCOUNTS Cost: 8  Bytes: 19  Cardinality: 1  				
					9 INDEX FULL SCAN UNIQUE AR.HZ_CUST_ACCOUNTS_U2 Cost: 7  Cardinality: 2  			
			13 TABLE ACCESS BY INDEX ROWID AR.HZ_PARTIES Cost: 1  Bytes: 23  Cardinality: 1  					
				12 INDEX UNIQUE SCAN UNIQUE AR.HZ_PARTIES_U1 Cardinality: 1  				





[Updated on: Thu, 16 October 2008 16:29]

Report message to a moderator

Re: Please help me Tune this query [message #354434 is a reply to message #354174] Sat, 18 October 2008 22:20 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Remove ALL hints.

Remove the LTRIM() functions, they are stopping you from using an index. Find out HOW MANY zeros are used to pad those columns and put them in the constant, e.g.
AND srla.customer = '000001689751'


Make sure that your other WHERE clauses that compare to numbers are really NUMBER data types. If they are VARCHAR2 data types, Oracle will cast the colum to a number and stop index usage.

Make sure all join columns are indexed.
- mp.organization_id
- srla.siteno
- hca.party_id
- hp.party_id
- hca.account_number
- srha.clientno
- srha.ldr_id
- srla.ldr_id

Make sure the selective filters are indexed.
- srla(customer, lbxno)
- srha.ldr_id
- msi.attribute8

Make sure all tables and indexes have fresh statistics gathered with DBMS_STATS.GATHER_TABLE_STATS().

You haven't joined to MSI. Are you just getting a single row from there? If not, you'll be getting a kind of cartesian product.

Ross Leishman
Re: Please help me Tune this query [message #354494 is a reply to message #354174] Sun, 19 October 2008 22:34 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Also, consider not doing the query in a loop. I often see developers coding loops in plsql. Each loop takes X time, for which they do the loop 10,000 times. I tell them to take the query out of the loop and simply join to the table(s) they used to get their input parameters from. They scoof at me. I do it for them. The new query takes X time. They cannot figure out why it takes the same amount of time to do the query for all rows they want as it did for just one. I say, it does not matter how many rows you want, it matters how many rows oracle has to look at to get the rows you want.

consider this:

Quote:
1) you have a table with 1 million rows
2) there are no indexes on the table
3) you want 100 rows
4) you loop through plsql getting one row at a time
5) each row get requires one FTS for a total of 100 FTS
6) I put 100 keys into an IN clause and execute a modified query
7) oracle does one FTS for all 100 rows

So, I say... take the sql out of the loop and join to the tables you got your parameters from so that you don't loop row by row.

Good luck, Kevin

[Updated on: Sun, 19 October 2008 22:35]

Report message to a moderator

Re: Please help me Tune this query [message #354511 is a reply to message #354494] Mon, 20 October 2008 02:10 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yeah, Kevin's right. See this article for more

Ross Leishman
Re: Please help me Tune this query [message #354886 is a reply to message #354174] Tue, 21 October 2008 16:36 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Are you sure that there is an index on symjb_rawdata_lines_all table with ldr_id as it's leading/first column?

If no such index exists - try

CREATE INDEX ... ON  symjb_rawdata_lines_all (
  LDR_ID, CLIENTNO, SITENO ) NOLOGGING COMPUTE STATISTICS...


HTH.
Michael
Previous Topic: can oracle access table in parallel with index?
Next Topic: Performance Query Tuning help
Goto Forum:
  


Current Time: Fri Dec 09 11:27:56 CST 2016

Total time taken to generate the page: 0.10944 seconds