Re: performance issue using select by rowid

How about going back to the trace file
to see if there is just one FETCH line that introduces the 4,000 block read.

Perhaps the problem is spurious trace data, or perhaps tkprof has picked up a line for another cursor and added it into the wrong summary. (It seems unlikely, but all things are possible).

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland  http://www.index.is/oracleday.php
June  2004      UK - Optimising Oracle Seminar


"malcolm" <malcolmfssmith_at_hotmail.com> wrote in message
news:492439c8.0403250733.9a8f7c7_at_posting.google.com...


> Thanks to you both

>

> can't give the table def but it is less than 200 rows across 10-15

> blocks - nothing odd like clobs, longs or whatever - just numbers,

> dates and smallish varchar2. It does have 8 foreign keys with

> constraints but how could that be the issue?

>

> Stats for the lookup of the rowid

>

> SELECT ROWID

> FROM

>  MYTABLE  WHERE MYTABLEPK = :b1

>

>

> call     count  cpu    elapsed   disk   query   current    rows

> ------- ------  -------- ---------- ---------- ---------- ---------- 

> Parse        5  0.01   0.00      0          0         0       0

> Execute     13  0.00   0.00      0          0         0       0

> Fetch       13  0.01   0.00      0         15         0      13

> ------- ------  -------- ---------- ---------- ---------- ---------

> total       31  0.02   0.00      0         15         0      13

>

> As you pointed out Jonathan my characterization of the behaviour was

> inaccurate. The general pattern is look up the rowid then use it - but

> it can get used more than once. Interesting point that sometimes we

> are not finding the row - must be a bug or two out there since I can't

> at the moment envisage a scenario when that lookup should fail.

>

> There are potentially some updates going on in other sessions - but

> not many and the numbers of query blocks is just so high I wouldnt

> have thought you could contrive those numbers by any means.