Re: Performance Question

From: Sybrand Bakker <postmaster_at_sybrandb.demon.nl>
Date: Wed, 12 Jan 2000 22:16:23 +0100
Message-ID: <947711881.2539.0.pluto.d4ee154e@news.demon.nl>

So we have
SELECT DISTINCT A.RT_TYPE

                                , A.TXN_CURRENCY_CD
                                , A.BASE_CURRENCY
                                , A.INVOICE_DT
                                ,  B.RATE_MULT, B.RATE_DIV

 FROM
   EDT_VCHR_WRK A
, RT_DFLT_VW B
WHERE A.BUSINESS_UNIT = :1 AND

      and   A.PROCESS_INSTANCE = :2
     AND A.CUR_RT_SOURCE = 'T'
     AND B.FROM_CUR = A.TXN_CURRENCY_CD
     AND B.TO_CUR = A.BASE_CURRENCY
     AND B.RT_TYPE = A.RT_TYPE
   AND B.EFFDT =
              (SELECT MAX(I.EFFDT)
               FROM RT_DFLT_VW I
               WHERE I.FROM_CUR = B.FROM_CUR
                    AND I.TO_CUR = B.TO_CUR
                   AND I.RT_TYPE = B.RT_TYPE AND I.EFFDT <= A.INVOICE_DT)

from the explain it is clear your problem is in the subquery. This is the explain for the subquery

2152377        SORT (AGGREGATE)


> 2152377         NESTED LOOPS


> 174874176          TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF


>                      'RT_RATE_TBL'


> 174913848           INDEX   GOAL: ANALYZED (RANGE SCAN) OF


>                       'RT_RATE_TBL_IDX01' (NON-UNIQUE)


> 2160396          TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF


>                      'RT_INDEX_TBL'


> 2160396           INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF


>                       'RT_INDEX_TBL' (UNIQUE)

>

Looks like there is some inefficient indexing. Without looking at the definition of that view (which is resolved into the query) I can't provide a more detailed answer.

Hth,
--
Sybrand Bakker, Oracle DBA

CGS <tcgs_at_hotmail.com> wrote in message news:vK4f4.2947$FW.197535_at_cac1.rdr.news.psi.ca...
> Hi All,
>
> I created a SQL trace for a query that was taking hours to run. Can
someone
> please help me to interpret the output? What I do not understand is that
why
> is
> it taking that long to fetch 22 rows? The time taken to execute the SQL
was
> almost negligible.
>
> Any help is appreciated
>
> CGS
> tcgs_at_hotmail.com
>
> SELECT DISTINCT A.RT_TYPE, A.TXN_CURRENCY_CD, A.BASE_CURRENCY,
A.INVOICE_DT,
> B.RATE_MULT, B.RATE_DIV
> FROM
> EDT_VCHR_WRK A, RT_DFLT_VW B WHERE A.BUSINESS_UNIT = :1 AND
> A.PROCESS_INSTANCE = :2 AND A.CUR_RT_SOURCE = 'T' AND B.FROM_CUR =
> A.TXN_CURRENCY_CD AND B.TO_CUR = A.BASE_CURRENCY AND B.RT_TYPE =
A.RT_TYPE
> AND B.EFFDT = (SELECT MAX(I.EFFDT) FROM RT_DFLT_VW I WHERE I.FROM_CUR =
> B.FROM_CUR AND I.TO_CUR = B.TO_CUR AND I.RT_TYPE = B.RT_TYPE AND I.EFFDT
> <=
> A.INVOICE_DT)
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----
--
> ----
> Parse 8 0.07 0.09 0 0 0
> 0
> Execute 8 0.05 0.05 0 0 0
> 0
> Fetch 8 12287.62 12603.65 73 530467835 0
> 22
> ------- ------ -------- ---------- ---------- ---------- ---------- ----
--
> ----
> total 24 12287.74 12603.79 73 530467835 0
> 22
>
> Misses in library cache during parse: 2
> Optimizer goal: ALL_ROWS
> Parsing user id: 22 (TEST)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: ALL_ROWS
> 246 SORT (UNIQUE)
> 246 NESTED LOOPS
> 246 NESTED LOOPS
> 4408 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'RT_RATE_TBL'
> 4409 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RT_RATE_TBL_IDX01'
> (NON-UNIQUE)
> 281178 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'EDT_VCHR_WRK'
> 1088776 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'EDT_VCHR_WRK_IDX01'
> (NON-UNIQUE)
> 2152377 SORT (AGGREGATE)
> 2152377 NESTED LOOPS
> 174874176 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'RT_RATE_TBL'
> 174913848 INDEX GOAL: ANALYZED (RANGE SCAN) OF
> 'RT_RATE_TBL_IDX01' (NON-UNIQUE)
> 2160396 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'RT_INDEX_TBL'
> 2160396 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
> 'RT_INDEX_TBL' (UNIQUE)
> 246 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
> 'RT_INDEX_TBL'
> 246 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'RT_INDEX_TBL'
> (UNIQUE)
>
>
>
>
>
>
Received on Wed Jan 12 2000 - 15:16:23 CST