Please help me Tune this query

raksrules

raksrules

Elite
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.

Code: 
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
 
As u said the query is makin full table scan on MTL_SYSTEM_ITEMS ...

try putting indexes on some fields that are common in both the tables and including those in the query 'WHERE' clause...

Will definately help...
 
Back
Top