RE: RE: HELP on performance

Ian,

  I don't think that the 'row count' associated with the execute has any meaning. Looks to me like a unsigned/signed value misrepresentation - tkprof bug. I find much more interesting the fact that the number of executes is identical to the number of fetches. Since you have indicated that you are using PKs everywhere, it means that each SELECT returns a single row, and that you are calling this SELECT a considerable number of times.

It looks to me that you have a cursor which does a

   select company_code, acct_num, fin_trans_id    from blahblah
   where blahblah

that you are looping 613070 times and that each time you are executing the query which worries you. You therefore have a SELECT called by a SELECT - which could probably have been avoided with a join in the first place. This is what I call a reprogrammed nested loop - Oracle knows perfectly how to do this within a single SELECT. Moreover, nested loops are fine for OLTP when you execute a sub SELECT for a relatively small number of rows. 600,000 is not what I call a small number of rows. Chances are that for this type of query hash or merge joins would be more appropriate, and that the optimiser (assuming proper statistics etc.) would probably naturally pick (perhaps with a gentle ALL_ROWS push) the proper execution plan. By executing your SELECT inside a loop driven by another SELECT, you let no chance to the optimiser - it can only work on a single query. Get rid of that loop, and you may be surprised by the improvement in performance.

SF

>----- ------- Original Message ------- -----
>From: "Biddell, Ian" <ian.biddell_at_hp.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Mon, 12 May 2003 03:56:37
>
>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
>
>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: 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).