Re: i/o waits by session?

"Chuck" <chuckh_at_softhome.net> wrote in message news:<alabjt$1o7pbd$1_at_ID-85580.news.dfncis.de>...
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> I know by querying V$SESSION_EVENT that a particular SID has
> experienced very high i/o waits (up to 2 minutes per i/o!). Is there
> a way to break down the i/o waits for that SID by file or filesystem?
> V$SESSION_WAIT will only show me the current waits at a given point
> in time. I need to see the i/o by file or filesystem for the entire
> session, or at least over a given period of time for that one
> session.

Hi, Chuck,

You can periodically sample v$session_wait where event like '%read%' or event like '%write%' and record all file# and block# somewhere. I can't think of an easier way for now.

If you don't want to lose any I/O statistics and so sampling is not acceptable, and performance impact due to tracing is not a big concern, then you can trace the shadow process on OS. What OS is it? If Solaris, try
truss -t read,write,pread,pwrite,readv,writev -p [shadow pid] > yourlogfile 2>&1
In another window, find out all files corresponding to the file descriptors for this process
lsof -p [pid] -a -d [fd1,fd2,fd3...]
where fds are the first argument of those read or write calls.

I'll think about this problem a little more.

Yong Huang Received on Wed Sep 11 2002 - 17:40:36 CDT