|Please help me Tune this query [message #354174]
||Thu, 16 October 2008 16:25
Registered: October 2006
Location: Mumbai / Pune, India
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
,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
,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
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
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
Registered: October 2005
Location: Melbourne, Australia
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.
Make sure the selective filters are indexed.
- srla(customer, lbxno)
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.
|Re: Please help me Tune this query [message #354494 is a reply to message #354174]
||Sun, 19 October 2008 22:34
Registered: December 1999
Location: Connecticut USA
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.|
|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