Oracle-L: HELP on performance

From: Biddell, Ian <ian.biddell_at_hp.com>
Date: Sun, 11 May 2003 18:11:38 -0800
Message-ID: <F001.0059656E.20030511181138@fatcity.com>

Hi all,

I have a batch program that is running a lon time after the initial driving cursor i finished. I was expecting it to run quick once that step was completed as it only has to do some primary key accesses while procesing the main cursor result.....

but when i traced it and got back the tkprof output (below) I couldn't believe the "row" counts i was getting on the Execute phase o the sql selects :-(

Does anyone have any idea why this would be so high if I am reading on unique keys in the select?

SELECT FT.RATE_ID,TO_CHAR(FT.PERIOD_START_DATE,'YYYY-MM-DD'),   TO_CHAR(FT.PERIOD_END_DATE,'YYYY-MM-DD'),RS.REVENUE_TYPE_TCODE INTO:b1,:b2,
  :b3,:b4
FROM
FINANCIAL_TRANSACTION_B FT,RATE_SCHEDULE_B RS WHERE FT.COMPANY_CODE=:b5 AND
  FT.ACCT_NUM=:b6 AND FT.FIN_TRANS_ID=:b7 AND RS.COMPANY_CODE=FT.COMPANY_CODE
  AND RS.RATE_ID=FT.RATE_ID call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse 0 0.00 0.00 0 0 0 0
Execute 613070 177.44 175.13 0 0 0 3766855855
Fetch 613070 418.84 878.44 82220 4904560 0 613070
------- ------ -------- ---------- ---------- ---------- ----------

total 1226140 596.28 1053.57 82220 4904560 0 3767468925

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 19 (SVCAUGRANGEAP)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   NESTED LOOPS
      0    TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 
               'FINANCIAL_TRANSACTION_B'
      0     INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                'FINANCIAL_TRANSACTION_PK' (UNIQUE)
      0    TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'RATE_SCHEDULE_B'
      0     INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'RATE_SCHEDULE_PK' 
                (UNIQUE)
 
************************************************************************

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Biddell, Ian
  INET: ian.biddell_at_hp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Sun May 11 2003 - 21:11:38 CDT