| Please help me Tune this query [message #354174] |
Thu, 16 October 2008 16:25  |
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  |
rleishman
Messages: 3728
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 |
 |
Kevin Meade
Messages: 2103
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 #354886 is a reply to message #354174] |
Tue, 21 October 2008 16:36 |
michael_bialik
Messages: 621
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
