RE: HELP on performance

Ian,

   The problem is not with this query, but with the loop you are calling it in. Looks like a perfect example of reprogrammed nested loop.

SF

>----- ------- Original Message ------- -----
>From: "Biddell, Ian" <ian.biddell_at_hp.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Sun, 11 May 2003 18:51:39
>
>=20
>Hi all,
>=20
>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.....
>=20
>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 :-(
>=20
>Does anyone have any idea why this would be so high
>if I am reading on
>unique keys in the select?
>=20
>SELECT
>
>INTO:b1,:b2,
> :b3,:b4=20
>FROM
> FINANCIAL_TRANSACTION_B FT,RATE_SCHEDULE_B RS
>WHERE =
>FT.COMPANY_CODE=3D:b5
>AND=20
> FT.ACCT_NUM=3D:b6 AND FT.FIN_TRANS_ID=3D:b7 AND
>RS.COMPANY_CODE=3DFT.COMPANY_CODE=20
> AND RS.RATE_ID=3DFT.RATE_ID=20
>=20
>
>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
>=20
>Misses in library cache during parse: 0
>Optimizer goal: CHOOSE
>Parsing user id: 19 (SVCAUGRANGEAP)
>=20
>Rows Execution Plan
>-------
>---------------------------------------------------
>
> 0 SELECT STATEMENT GOAL: CHOOSE
> 0 NESTED LOOPS
> 0 TABLE ACCESS GOAL: ANALYZED (BY ROWID)
>OF=20
> 'FINANCIAL_TRANSACTION_B'
> 0 INDEX GOAL: ANALYZED (UNIQUE SCAN)
>OF=20
> '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'=20
> (UNIQUE)
>=20
>********=20
>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
>***************************************************
>*********************

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.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).