i/o waits by session? 
Author Message
 i/o waits by session?

-----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.

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use < http://www.***.com/ >

iQA/AwUBPXixulVYIIV9CtSHEQIXcACffINB1/pgKB0pPqBB8pMGnMmuyKsAoI1h
/5e6zPX0nuzkl1OhFugdtuh3
=ESjW
-----END PGP SIGNATURE-----



Tue, 22 Feb 2005 21:46:37 GMT
 i/o waits by session?

If you want to see the I/O for a particular application usage.
Have only one session accessing the database, collect file I/O
statistics using v$FILESTAT, run the application for some time and run
V$FILESTAT to collect end statistics.
The difference is the file I/O usage of your application file wise.
regards
Srivenu


Wed, 23 Feb 2005 14:58:30 GMT
 i/o waits by session?


Quote:
> If you want to see the I/O for a particular application usage.
> Have only one session accessing the database, collect file I/O
> statistics using v$FILESTAT, run the application for some time and run
> V$FILESTAT to collect end statistics.
> The difference is the file I/O usage of your application file wise.
> regards
> Srivenu

Unfortunately I can't have the database all to myself. It is a 24x7
production database.


Sat, 26 Feb 2005 20:39:51 GMT
 i/o waits by session?

Quote:

> -----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



Mon, 28 Feb 2005 06:40:36 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Trace SQL in a session with bind and wait events 8i

2. Getting blocking / waiting sessions info.

3. Total wait time per session incurred by SQL locks

4. Getting blocking / waiting sessions info

5. Session is waiting for long....

6. VFP5.0a: flock() not waiting between data sessions ??

7. Users Waiting - What resource are they waiting on

8. q: buffer busy wait(data block wait) when P3=130

9. PL/SQL log sync waits, log file parallel write waits and redo writes

10. Process waiting after issuing OPEN CURSOR, onstat shows a thread in waiting condition await_MC1

11. Wait/No wait option, transaction and ODBC

12. Java programs running on a Windows NT session via a server session


 
Powered by phpBB® Forum Software