Re: I/O waits

From: Yong Huang <yong321_at_yahoo.com>
Date: 14 Feb 2003 09:15:30 -0800
Message-ID: <b3cb12d6.0302140915.2a9a5cc9@posting.google.com>

Chuck <chuckh_at_softhome.net> wrote in message news:<Xns931F69D5E6798chuckhsofthomenet_at_130.133.1.4>...
> Platform: Oracle 8.1.7
>
> I have a script that runs every 10 minutes to check the response time of
> a query on one of my databases. At the end I am reporting on what events
> it waited on. I frequently see I/O waits much higher than I expect and
> would like to see which datafiles it's waiting on. Is there a way to
> report which datafiles the query accessed and how much time was spent
> waiting for each one? I know I can query v$session_wait to see what
> files are being accessed at any given moment, but that's not what I
> want. I want to see the total time waited on each datafile but just for
> the one session.

Hi, Chuck,

I noticed you asked a similar question before:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=b3cb12d6.0209111440.e7149b9%40posting.google.com&rnum=3&prev=/groups%3Fas_q%3Dyong%2520truss%26safe%3Dimages%26ie%3DUTF-8%26oe%3DUTF-8%26as_ugroup%3Dcomp.databases.oracle.server%26lr%3D%26as_scoring%3Dd%26hl%3Den

But this time you're asking for the *time* a single session has spent on individual files, instead of number of IOs (or amounts of IOs) on those files. So in my opinion, not only is Oracle unable to give you that, but system call tracing tool such as truss in SVR4 fails too. The only awkward way, *if you're using Solaris*, may be using prex. Follow the instruction in Adrian Cockcroft's Sun Performance Tuning (or Programming Utilities Guide from docs.sun.com) and make sure you use pfilter to focus on that shadow process only. Roughly it goes like this:

# prex -k
  buffer alloc 100m
  enable io
  pfilter add <processid>
  trace io
  ktrace on

After tnfxtract and tnfdump, the result looks like this:

121857.390444 1736.011796 3736 1 0x300 18 pagein vnode: 0x3000d20ab38 offset: 0 size: 8192

121857.400640 0.010196 3736 1 0x300 18 strategy device: 6 block: 2610480 size: 8192 buf: 0x30000758630 flags: 524377 ...

The first two numbers are elapsed time and delta time. You need to find the file from the vnode. I think crash has a function for it... Quite a bit of work.

Remember to disable tracing and deallocate the kernel buffer after you're done.

Yong Huang Received on Fri Feb 14 2003 - 11:15:30 CST