Re: Tuning Question......

Miggins wrote:
> Hi,
>
> Have the following query producing the plan shown. This query is being
> executed upwards of 20,000 times each run. The TRANSACTIONS table
> contains 12,000,000 rows and the TRANSACTION_DETAILS table 19,000,000.
> Its taking way too long to run.
>
> Any suggestions on improving the efficiency of the query would be
> greatly appreciated.
>
> The primary key on TRANSACTIONS is on
> TRA_CODE
> TRA_TYPE
>
> The primary key on TRANSACTION_DETAILS is on
> TRAD_TRANS_NO
> TRAD_LINE_NO
>
> select sum(nvl(trad_debit,0) - nvl(trad_credit,0))
> from transactions tra,
> transaction_details trd
> where tra.tra_type = v_type_tab(si)
> and tra.tra_code = v_code_tab(si)
> and 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;
>
> 0 | SELECT STATEMENT |
> 1 | SORT AGGREGATE |
> 2 | TABLE ACCESS BY INDEX ROWID | TRANSACTION_DETAILS
> 3 | NESTED LOOPS |
> 4 | TABLE ACCESS BY INDEX ROWID| TRANSACTIONS
> 5 | INDEX RANGE SCAN | TRA_CODE_TYPE
> 6 | INDEX RANGE SCAN | TRAD_PK
DDL for the tables and sample data would be most helpful.. Additionally:

• What does select count(*) From transactions where origin = '7'; return? A wild guess would be a skewed data distribution but we won't know that until you provide some data to support or refute such a guess.

Without such information any further response would be speculation, even with the explain plan output you've provided. Also, define what you mean by 'Its taking way too long to run'; providing some statistics, including run times, would be helpful by giving contrext to such a remark.

Oh, and you neglected to provide the Oracle Version (all four numbers) and the operating system upon which this installation of Oracle runs, information necessary to properly answer your question.

When you can, and do, provide such information we can help you further. Any attempt at this point would be speculation.

David Fitzjarrell Received on Mon Mar 06 2006 - 14:36:49 CST