Re: How to figure out how many IOs a SQL costs ?

Your comment about blocks and I/Os is correct -

If you want the number of multiblock reads and single block reads then you could try looking at snapshots of v$session_event for the session in question - I haven't checked how good the correlation is, but in principle the physical reads (adjusted for direct reads) from v$sesstat should be represented by the 'db file scattered read' and ''db file sequential read' waits. There are also waits for direct reads and writes - which should match the v$sesstat figures IF you can adjust for the multi-block i/o size; but since that can vary from query to query, and depends on operation type, these is likely to be little obvious correlation.

However, trying to get some 'total I/O' count is a bit difficult because DBWR and LGWR write in their own time, which need have very little to do with your session's activity on the system.

    DBWR will write the blocks you have updated only when     there is pressure to clean some blocks.

    LGWR may write some redo that you generate because     another user has committed - conversely, the redo written     when you commit could have been generated largely by     another user.

--
Regards

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

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

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


Bass Chorng wrote in message ...


>

>  I might be wrong here, but isn't v$sess_io and tkprof all showing

>  # of blocks, not # of IOs ? I am interested to know # of IOs, not

>  blocks, because blocks could be a combination of sequential read

>  and scattered read and the # of block/ per IO could be differnt.

>