Please help me Tune this query

[raksrules]

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

 

[Bandu]

Please elaborate some more. Loop as in what? Is it a PL/SQL loop or Java/.Net program loop?

Why are you using the hint? Can you avoid using that and let Oracle decide on the best plan for your query? Can you post the said tables definition and indexes on it?