Home » RDBMS Server » Performance Tuning » Please help in tuning (Oracle 10g)
Please help in tuning [message #322213] Thu, 22 May 2008 15:35 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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 #322231 is a reply to message #322226] Thu, 22 May 2008 19:59 Go to previous messageGo to next message
mukundsahare
Messages: 21
Registered: February 2007
Junior Member
Hi Thanks for the reply , but I did'nt got how to subordinate the tables in where clause.Please explain.

Regards
Mukund
Re: Please help in tuning [message #322232 is a reply to message #322213] Thu, 22 May 2008 20:06 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
AND a.receivables_trx_id in (SELECT c.receivables_trx_id from ar_receivables_trx c where c.name NOT IN('133 bad debt'))
Re: Please help in tuning [message #322381 is a reply to message #322232] Fri, 23 May 2008 05:23 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 #323578 is a reply to message #323233] Wed, 28 May 2008 22:32 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Read this article on PL/SQL tuning

Ross Leishman
Re: Please help in tuning [message #323581 is a reply to message #322213] Wed, 28 May 2008 22:43 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Please help if you any suggestions.
It appears you've ignored my previous advice, so I won't waste any more time on this thread.
Re: Please help in tuning [message #323776 is a reply to message #322213] Thu, 29 May 2008 07:36 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
anacedent, can you explain why you think subordination helps performances of queries? I always had problems with this and would like to know what I was doing wrong.

Kevin
Re: Please help in tuning [message #323795 is a reply to message #322213] Thu, 29 May 2008 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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 #323804 is a reply to message #323795] Thu, 29 May 2008 09:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
At the risk of appearing impertinent, I did post a very simple test case where the subordination to almost 50% longer.
Re: Please help in tuning [message #323887 is a reply to message #323804] Thu, 29 May 2008 23:29 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #323892 is a reply to message #322213] Thu, 29 May 2008 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Otherwise, its a case of READY, FIRE, AIM (now where have I heard that before).

Live & Learn; which beats the alternative!
Re: Please help in tuning [message #323997 is a reply to message #323887] Fri, 30 May 2008 04:17 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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');
Re: Please help in tuning [message #325211 is a reply to message #325209] Thu, 05 June 2008 05:21 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the rationale behind this advice?

Regards
Michel
Previous Topic: How to perforamnce this query? Help
Next Topic: Index Monitoring
Goto Forum:
  


Current Time: Fri Dec 09 23:27:10 CST 2016

Total time taken to generate the page: 0.12939 seconds