 | | | -none- | -none- 2007-10-02 - By qihua wu
Back
Hi, Alberto,
Thanks very much for your message.
Based on the formula you mentioned, if the cpu is always 100%busy for a long
time. Then the time that actually wating to be scheduled will be counted as
"wait for IO". If this is the case, then oracle will mislead us that IO is
too busy with the fact that CPU is too busy.
t0 := get wallclock time [ gettimeofday() system call ]
issue read request to the OS (a system call)
---> the time between the read is completed and oracle process get running
(if cpu is busy, then this time maybe long) <--
t1 := get wallclock time
Am I right?
Thanks,
Qihua
On 10/2/07, Alberto Dell'Era <alberto.dellera@(protected)> wrote:
>
> As far as I know, Oracle doesn't communicate anything to
> the OS, it is the other way around - Oracle reads OS
> instrumentation data from the OS ;). Unless that has changed
> in some recent version, in which case I'd like to be corrected ...
>
> A read from a datafile (a physical read, either sequential or scattered)
> is instrumented this way:
> t0 := get wallclock time [ gettimeofday() system call ]
> issue read request to the OS (a system call)
> t1 := get wallclock time
> wait time := t1 - t0;
>
> As soon as the process issues the read system call, the OS
> scheduler removes it from the CPU and puts it in the "waiting for I/O"
> state. When the I/O is completed (i.e. the requested blocks have
> been copied to the buffers) the process is put in the "runnable"
> state (queued for the CPUs), and later put back on a CPU ("running"
> state).
>
> So "wait time" includes the time spent waiting for the disks
> AND the time spent in the "runnable" state (the latter is far
> from being negligible if your instance has many processes
> contending for the CPU) plus some time to issue gettimeofday()
> etcetera.
>
> Not sure what "CPU I/O wait" is - maybe it is simply "I/O wait",
> the time a process (Oracle or not) spends waiting for the
> disks, so definitely NOT on the CPU ?
> Or maybe the CPU used for copying the buffers and
> other activities performed inside the read system call ?
> Or the two figures added together ?
>
> BTW If you're interested in a detailed discussion, an excellent
> reference is Cary Millsap/Jeff Holt's "Optimizing Oracle Performance",
> where you can find these things explained in a much more
> correct and precise way than mine.
>
> HTH
> Alberto
>
> On 10/1/07, David Taft <oradbt054@(protected)> wrote:
> > Qihua,
> >
> > Others are more knowledgeable than myself on this, but I will give this
> a
> > shot. As I understand it, whenever Oracle is waiting on IO, the
> > instrumentation is such that this time is reported to OS kernel. Hence
> any
> > IO waits in Oracle are also reported at the OS level, but not all IO
> waits
> > at the OS level are necessarily correlated to Oracle IO waits. When an
> > Oracle process is waiting on IO to complete, it voluntarily takes itself
> off
> > the CPU since it can't continue processing until the required IO
> completes.
> > The Unix kernel requires this info from Oracle processes for CPU time
> slice
> > scheduling. If I got any of this wrong, please someone correct
> me. Anyway,
> > you may find the following article worth reading:
> >
> > http://www.quest-pipelines.com/newsletter-v3/0402_C.htm
> > Tuning Oracle Without Cache-Hit Ratios
> >
> > Of particular interest with regard to your question is the section,
> > IDENTIFYING THE OS BOTTLENECKS - PRONG II.
> >
> > You may also want to read the following post that shows a correlation
> > between the OS-level IO waits and those reported in Statspack.
> >
> > http://www.freelists.org/archives/oracle-l/11-2005/msg00411.html
> > High wio on New Hitachi SAN Storage of DB Server
> >
> > Hope this is of some help.
> >
> > Cheers,
> >
> > David
> >
> >
> >
> > On 9/29/07, qihua wu <staywithpin@(protected)> wrote:
> > > the same or at least related to the "CPU I/O wait' at the unix level?
> > >
> >
> >
>
>
> --
> Alberto Dell'Era
> "the more you know, the faster you go"
>
Hi, Alberto,<br><br>Thanks very much for your message.<br><br>Based on the
formula you mentioned, if the cpu is always 100%busy for a long time. Then the
time that actually wating to be scheduled will be counted as "wait for IO
". If this is the case, then oracle will mislead us that IO is too busy
with the fact that CPU is too busy.
<br> t0 := get wallclock time [ gettimeofday() system call ]<br>
issue read request to the OS (a system call)<br> ---> the time
between the read is completed and oracle process get running (if cpu is busy,
then this time maybe long) <--
<br> t1 := get wallclock time<br>Am I right?<br><br>Thanks,<br>Qihua
<br><br><div><span class="gmail_quote">On 10/2/07, <b class="gmail_sendername"
>Alberto Dell'Era</b> <<a href="mailto:alberto.dellera@(protected)">alberto
.dellera@(protected)
</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px
solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">As far
as I know, Oracle doesn't communicate anything to<br>the OS, it is the
other way around - Oracle reads OS
<br>instrumentation data from the OS ;). Unless that has changed<br>in some
recent version, in which case I'd like to be corrected ...<br><br>A read
from a datafile (a physical read, either sequential or scattered)<br>
is instrumented this way:<br> t0 := get wallclock time [
gettimeofday() system call ]<br> issue read request to the OS (a
system call)<br> t1 := get wallclock time<br>wait time := t1 - t0;
<br><br>As soon as the process issues the read system call, the OS
<br>scheduler removes it from the CPU and puts it in the "waiting for I/O
"<br>state. When the I/O is completed (i.e. the requested blocks have<br
>been copied to the buffers) the process is put in the "runnable"
<br>state (queued for the CPUs), and later put back on a CPU ("running
" state).<br><br>So "wait time" includes the time spent waiting
for the disks<br>AND the time spent in the "runnable" state (the
latter is far
<br>from being negligible if your instance has many processes<br>contending for
the CPU) plus some time to issue gettimeofday()<br>etcetera.<br><br>Not sure
what "CPU I/O wait" is - maybe it is simply "I/O wait",
<br>the time a process (Oracle or not) spends waiting for the<br>disks, so
definitely NOT on the CPU ?<br>Or maybe the CPU used for copying the buffers
and<br>other activities performed inside the read system call ?<br>Or the two
figures added together ?
<br><br>BTW If you're interested in a detailed discussion, an excellent<br
>reference is Cary Millsap/Jeff Holt's "Optimizing Oracle Performance
",<br>where you can find these things explained in a much more
<br>correct and precise way than mine.<br><br>HTH<br>Alberto<br><br>On 10/1/07,
David Taft <<a href="mailto:oradbt054@(protected)">oradbt054@(protected)</a>>
wrote:<br>> Qihua,<br>><br>> Others are more knowledgeable than myself
on this, but I will give this a
<br>> shot. As I understand it, whenever Oracle is waiting on IO,
the<br>> instrumentation is such that this time is reported to OS kernel.
Hence any<br>> IO waits in Oracle are also reported at the OS level, but not
all IO waits
<br>> at the OS level are necessarily correlated to Oracle IO waits.
When an<br>> Oracle process is waiting on IO to complete, it
voluntarily takes itself off<br>> the CPU since it can't continue
processing until the required IO completes.
<br>> The Unix kernel requires this info from Oracle processes for CPU time
slice<br>> scheduling. If I got any of this wrong, please someone
correct me. Anyway,<br>> you may find the following article worth
reading:
<br>><br>> <a href="http://www.quest-pipelines.com/newsletter-v3/0402_C
.htm">http://www.quest-pipelines.com/newsletter-v3/0402_C.htm</a><br>> Tuning
Oracle Without Cache-Hit Ratios<br>><br>> Of particular interest with
regard to your question is the section,
<br>> IDENTIFYING THE OS BOTTLENECKS - PRONG II.<br>><br>> You may
also want to read the following post that shows a correlation<br>> between
the OS-level IO waits and those reported in Statspack.<br>><br>>
<a href="http://www.freelists.org/archives/oracle-l/11-2005/msg00411.html">http
://www.freelists.org/archives/oracle-l/11-2005/msg00411.html</a><br>> High
wio on New Hitachi SAN Storage of DB Server<br>><br>> Hope this is of
some help.
<br>><br>> Cheers,<br>><br>> David<br>><br>><br>><br>>
On 9/29/07, qihua wu <<a href="mailto:staywithpin@(protected)">staywithpin
@(protected)</a>> wrote:<br>> > the same or at least related to the "
;CPU I/O wait' at the unix level?
<br>> ><br>><br>><br><br><br>--<br>Alberto Dell'Era<br>"
;the more you know, the faster you go"<br></blockquote></div><br>
|
|
|