Oracle-L: RE: HELP on performance

From: Biddell, Ian <ian.biddell_at_hp.com>
Date: Mon, 12 May 2003 03:56:37 -0800
Message-ID: <F001.00596801.20030512035637@fatcity.com>

Hi Stephane,

Thanks for replying to my query.
I was wondering what exactly you meant by a reprogrammed nested loop? Why would the calling loop affect the "Rows" count in this particular queries trace output?

Thanks Ian

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 Mon May 12 2003 - 06:56:37 CDT