Re: performance issue using select by rowid

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Wed, 24 Mar 2004 13:25:30 GMT
Message-ID: <e%f8c.122801$Wa.24794@news-server.bigpond.net.au>

"malcolm" <malcolmfssmith_at_hotmail.com> wrote in message news:492439c8.0403240357.6ef0ea43_at_posting.google.com...
> Here is an individual statement from a larger tkprof when monitoring a
> client app.
>
> This particular statement is a select by rowid (we look up the rowid
> just before this statement with a primary key)
>
> This database has too little buffer cache (I do realise that!) - but
> even so how can these numbers (for Query) be right?
>
> Or to put it another way how would you go about contriving such a
> set of numbers?
>
> SELECT *
> FROM
> MYTABLE WHERE ROWID = :b1
>
> call count cpu elapsed disk query current rows
> ------- ------ -------- ---------- ---------- ---------- ------
> Parse 5 0.00 0.00 0 0 0 0
> Execute 35 0.00 0.00 0 0 0 0
> Fetch 35 0.27 0.27 4110 4261 48 29
> ------- ------ -------- ---------- --------- ---------- -------
> total 75 0.27 0.27 4110 4261 48 29
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 186 () (recursive depth: 1)
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 0 TABLE ACCESS GOAL: ANALYZED (BY USER ROWID) OF 'MYTABLE'
Malcolm,

What version of Oracle are you using? What is the CREATE TABLE DDL for MYTABLE? When I use a similar approach in Oracle 9.2.0.1 for a heap table, I get the following results:
SELECT *
FROM
 s_opty WHERE rowid = :b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 7 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 14 0.00 0.00 0 7 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 28 0.00 0.00 0 7 0 7

Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 65

Rows Row Source Operation

-------  ---------------------------------------------------
      1  TABLE ACCESS BY USER ROWID S_OPTY

I ran the 10G version of tkprof.

Douglas Hawthorne Received on Wed Mar 24 2004 - 07:25:30 CST