Notes in line
--
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.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)
>
What are the statistics on the select statement that
looks up the rowid.
> 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
>
If you've found the rowid by primary key lookup, how
come that on 35 attempts to read a row, you've missed
six ?
Is it possible that rows are being updated and deleted
at high speed as your application runs, and you have to
do a lot of re-reading of undo blocks in order to build
read-consistent versions of blocks, because you are
running with read only transactions, or serializable transactions
in the application ?
I can't think of a data type that could force a rowid
to be coerced (hence turn the lookup into a tablescan)
- but just check the type of the bind variable (pl/sql
variable ?) that is used to hold the rowid.
> 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'
Received on Wed Mar 24 2004 - 07:44:31 CST
