Performance Question

From: CGS <tcgs_at_hotmail.com>
Date: Wed, 12 Jan 2000 14:51:39 -0500
Message-ID: <vK4f4.2947$FW.197535@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 - 13:51:39 CST