Re: Tuning Question......

From: Jim Smith <jim_at_jimsmith.demon.co.uk>
Date: Tue, 7 Mar 2006 10:52:31 +0000
Message-ID: <E1dEPfXvXWDEFwxr@jimsmith.demon.co.uk>

In message <1141727273.433834.23320_at_u72g2000cwu.googlegroups.com>, Miggins <mtproc_at_yahoo.co.uk> writes
>Version of Oracle is 9.2.0.4 running on Linux.
>
>This query is called as part of a batch process. If i comment out this
>query the process runs in under 10 seconds. Leave it in and it takes
>nearly 45 minutes. Each batch run this query is called around 20,000
>times

Obviously, the batch run isn't doing much other than calling this query. It looks as if the query is returning 1 row per call. Could you rework the query so that is a single large query which is returning more rows but is only called once?

e.g

SELECT tra.type_code,

        tra.tra_code,
        SUM(nvl(trad_debit,   0) -nvl(trad_credit,   0))

FROM transactions tra,
  transaction_details trd
WHERE tra.origin = '7'

 AND tra.trans_no = trd.trad_trans_no
 AND trd.trad_product_code = v_journal_code
 AND trd.trad_start_date <= p_issue_date
 AND trd.trad_period_end_date >= p_issue_date

group by tra.type_code, tra.tra_code;

-- 
Jim Smith
I'm afraid you've mistaken me for someone who gives a damn.

Received on Tue Mar 07 2006 - 04:52:31 CST