Please help in tuning [message #322213] |
Thu, 22 May 2008 15:35  |
mukundsahare
Messages: 21 Registered: February 2007
|
Junior Member |
|
|
Hi,
I want to tune the below query
------------------------
SELECT SUM(nvl(b.exchange_rate, 1) *a.amount)
FROM ar_adjustments a,
ra_customer_trx b,
ar_receivables_trx c,
ra_cust_trx_types d,
arc_entity_master aem
WHERE a.customer_trx_id = b.customer_trx_id
AND b.bill_to_customer_id = 47988 --:b3
AND a.receivables_trx_id = c.receivables_trx_id
AND b.cust_trx_type_id = d.cust_trx_type_id
AND SUBSTR(d.name, 1, 3) = aem.flex_value
AND aem.attribute5 IS NULL
AND b.invoice_currency_code = 'USD'
AND c.name NOT IN('133 bad debt')
AND a.amount < 0
AND a.creation_date > '04-APR-2008'
AND a.creation_date < '30-MAY-2008'
AND a.status IN('A'); --------------------------------------
IS there any option for SUBSTR(d.name, 1, 3) = aem.flex_value and aem.attribute5 IS NULL , which I thing is creating problem .There is index on flex_value of aem(arc_entity_master) table
-------------------------------------------
Below is the explain paln for the query
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=53 Card=1 Bytes=
9)
0 SORT (AGGREGATE)
1 TABLE ACCESS (BY INDEX ROWID) OF 'AR_RECEIVABLES_TRX_A
' (TABLE) (Cost=2 Card=1 Bytes=31)
2 NESTED LOOPS (Cost=53 Card=1 Bytes=109)
3 NESTED LOOPS (Cost=51 Card=1 Bytes=78)
4 NESTED LOOPS (Cost=48 Card=1 Bytes=48)
5 HASH JOIN (Cost=18 Card=12 Bytes=252)
6 TABLE ACCESS (FULL) OF 'RA_CUST_TRX_TYPES_AL
(TABLE) (Cost=15 Card=13 Bytes=195)
6 TABLE ACCESS (FULL) OF 'ARC_ENTITY_MASTER' (
BLE) (Cost=3 Card=286 Bytes=1716)
5 TABLE ACCESS (BY INDEX ROWID) OF 'RA_CUSTOMER_
X_ALL' (TABLE) (Cost=3 Card=1 Bytes=27)
9 INDEX (RANGE SCAN) OF 'RA_CUSTOMER_TRX_N11'
NDEX) (Cost=2 Card=2)
4 TABLE ACCESS (BY INDEX ROWID) OF 'AR_ADJUSTMENTS
LL' (TABLE) (Cost=3 Card=1 Bytes=30)
11 INDEX (RANGE SCAN) OF 'AR_ADJUSTMENTS_N2' (IND
) (Cost=2 Card=1)
3 INDEX (RANGE SCAN) OF 'AR_RECEIVABLES_TRX_U1' (IND
(UNIQUE)) (Cost=1 Card=1)
===========================================
Statistics
------------------------------------------------------
0 recursive calls
0 db block gets
46 consistent gets
0 physical reads
0 redo size
231 bytes sent via SQL*Net to client
278 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed Please help
Thanks
Mukund
[Updated on: Fri, 23 May 2008 05:17] by Moderator Report message to a moderator
|
|
|
Re: Please help in tuning [message #322226 is a reply to message #322213] |
Thu, 22 May 2008 17:44   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
The tables below
ar_receivables_trx c,
ra_cust_trx_types d,
arc_entity_master aem
should be eliminated out of the FROM clause
& subordinated into the WHERE clause
since they contribute no data to the SELECT clause.
> AND a.creation_date > '04-APR-2008'
>AND a.creation_date < '30-MAY-2008'
characters between single quote marks are STRINGS; not date data type
When you require a date data type use the TO_DATE() function.
[Updated on: Thu, 22 May 2008 17:46] by Moderator Report message to a moderator
|
|
|
|
|
Re: Please help in tuning [message #322381 is a reply to message #322232] |
Fri, 23 May 2008 05:23   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Are your statistics up to date?
Oracle thinks that RA_CUST_TRX_TYPES_AL contains 13 rows and ARC_ENTITY_MASTER contains 286 rows. Is this right?
If not gather statistics on all tables with DBMS_STATS.GATHER_TABLE_STATS. If it is right,
- how big are the other tables?
- how many rows does the query return?
- how long does it take?
- how long do you think it should take?
Ross Leishman
|
|
|
Re: Please help in tuning [message #323233 is a reply to message #322232] |
Tue, 27 May 2008 18:30   |
mukundsahare
Messages: 21 Registered: February 2007
|
Junior Member |
|
|
Hi,
I was not able to explain properly the problem.The query is used in one of the package .My dba observation is that this query is taking time as you can see the WHERE condition (AND b.bill_to_customer_id = { Customerid }) , this value is coming from cursor and it is execting for number of customer the cursor is fetching ( If cursor fetches 5000 customer so it is executed 500o times) so I want to reducec the time for this exceution.
I hope now I am clear ..
Please help if you any suggestions.
Regards
Mukund
|
|
|
|
|
|
Re: Please help in tuning [message #323795 is a reply to message #322213] |
Thu, 29 May 2008 08:42   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
Subordination helps the optimizer by presenting fewer & better choices.
Unfortunately, it is one of the cases where you can not predict the results.
You have to make the change(s) and test the result(s).
In my experience, in most cases the SQL run faster & has never run slower.
[Updated on: Thu, 29 May 2008 08:56] by Moderator Report message to a moderator
|
|
|
|
Re: Please help in tuning [message #323887 is a reply to message #323804] |
Thu, 29 May 2008 23:29   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Hmmm. I disagree with both of you.
JR, your test case is a good one, but would be better if you had gathered statistics.
Since the introduction of subquery unnesting, the CBO has become quite difficult to encourage an uncorrelated IN subquery to perform a Nested Loops join driving off the sub-query. Sometimes good stats will help, sometimes not. If your test case used this plan, it would have performed similarly to the explicit join.
Subordinating sometimes works for the reasons Ana claims, but typically only where there are 5 or more tables in the join. If there are fewer, CBO is performing the wrong join order because of statistics, not too many possible combinations. In such cases, changing the SQL does not address the underlying problem and is therefore misguided.
There are four pertinent facts to this argument:- The ONLY thing a sub-query can do that an exlpicit join cannot is a semi-join. If the distribution of data is right, this can make a sub-query faster because it avoids reading some rows.
- NESTED sub-queries typically execute AFTER joins. If the join filters rows out, the nested sub-query will execute fewer times resulting in improved performance. This will only help on low-volume queries, so the performance improvement would be minimal and difficult to detect.
- UNNESTED sub-queries are executed as a join. Any performance improvement would only come as a result of a RANDOM change to the join order. Assuming that a BAD performing query has a really bad join-order, the probability is that any other random join order would be an improvement. I wouldn't call this tuning - it's more like fiddling.
- In Driving Nested Loops sub-queries, the optimiser runs the sub-query first, sorts and removes duplicates, then performs a NL join on the outer table. This is usually beneficial in uncorrelated low-volume IN sub-queries (such as JR's example). If it is wrongly chosen for a high-volume sub-query, the SORT will cause a performance degradation. In any case, the CBO should have chosen a semi-join, which would have similar performance to the explicit join.
In summary, if you KNOW that the distribution of data will suit a semi-join, by all means recommend subordinating. Otherwise, its a case of READY, FIRE, AIM (now where have I heard that before).
Ross Leishman
|
|
|
|
Re: Please help in tuning [message #323997 is a reply to message #323887] |
Fri, 30 May 2008 04:17   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Fair point.
I reran the timing excercise with stats, and the results came out much closer - 7351 for the first query, vs 7513 for the subquery.
Live & Learn - better than ALL of the other options.
|
|
|
Re: Please help in tuning [message #325209 is a reply to message #323997] |
Thu, 05 June 2008 05:16   |
star_guan2008
Messages: 4 Registered: June 2008
|
Junior Member |
|
|
hi,
which table is smallest?
try this,add hite to it.
SELECT /*+ use_hash(a b c d aem)*/SUM(nvl(b.exchange_rate, 1) *a.amount)
FROM ar_adjustments a,
ra_customer_trx b,
ar_receivables_trx c,
ra_cust_trx_types d,
arc_entity_master aem
WHERE a.customer_trx_id = b.customer_trx_id
AND b.bill_to_customer_id = 47988 --:b3
AND a.receivables_trx_id = c.receivables_trx_id
AND b.cust_trx_type_id = d.cust_trx_type_id
AND SUBSTR(d.name, 1, 3) = aem.flex_value
AND aem.attribute5 IS NULL
AND b.invoice_currency_code = 'USD'
AND c.name NOT IN('133 bad debt')
AND a.amount < 0
AND a.creation_date > '04-APR-2008'
AND a.creation_date < '30-MAY-2008'
AND a.status IN('A');
|
|
|
|