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

bchorng_at_yahoo.com (Bass Chorng) wrote in message news:<bd9a9a76.0210151424.6e388003_at_posting.google.com>...
> 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.

Why not trace the server process? If on Solaris, do this:

$ truss -c -t read,write,pread,pwrite -p 19272

^Csyscall      seconds   calls  errors
read             .00       4
write            .00       3
pread64          .00       4
                ----     ---    ---
sys totals:      .00      11      0
usr time:        .00
elapsed:        7.79

The calls column is what you want. Here the pid 19272 comes from select spid from v$process where addr = (select paddr from v$session where sid = [thesessionid]). ^C is pressed when you're done with your SQL. For other OSes, find the appropriate system call trace command. Usually strace for BSD UNIX. For Windows, it's even easier. Task Manager has this capability (go to View, Set Columns)

Yong Huang Received on Thu Oct 17 2002 - 16:55:06 CDT