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

Bass Chorng <bchorng_at_yahoo.com> wrote:
>I don't see that Oracle has # of IOs as a statistic, but this seems to
>be a common question. If I am right, disk in tkprof or disk_reads in
>v$sql all refer to # of blocks. I have yet to find a direct way to
>tell # of IOs incurred.
>
>The closet I can think of is, suppose you can control the environment,
>the difference of SUM(PHYRDS+PHYWRTS) in v$filestat before and after a
>SQL run, plus the value of "redo writes" in v$sesstat.
>
>Does this do the job, or I am missing something else ?
>
>Thanks for your input in advance.

The problem is that while reads are quite straightforward to attribute to a single statement, writes are not as several statements could have caused modifications to the same block before DBWn decides to write that buffer to disk. Of course, that buffer might be sufficiently active that it isn't written at all until the next checkpoint.

So, the best you can do for is average IO over a representative period, preferably including more than one checkpoint.

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/