Re: performance issue using select by rowid

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. Received on Thu Mar 25 2004 - 09:33:38 CST