Re: Performance Question

HI

Well two tools I used are

1. the Oracle Preformance tuning book by Mark Gurry & Peter Corrigan (published by O'Reilly).
2. TOAD (www.toadsoft.com for the freeware version) and use it to to explain plans on the fly. This means I can make chnages to SQL and see the cost differences due to recommendations from 1) without actually running a long running SQL.

Looks like sort is taking the time so I'd concentrate on reducing that with 'exists' to reduce sort time.

Martin

CGS wrote:
>
> 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 Thu Jan 13 2000 - 02:55:20 CST