Re: Heavy load and database statistics

Yes, the question makes sense, and it is something that often causes confusion, though usually the other way round.

Unfortunately Oracle can do nothing about it - an Oracle process says 'give me block 22,15436' and goes to sleep waiting for the O/S to wake it up and tell it that the read is complete. Oracle has no way of detecting that the wakeup call was partly due to I/O wait and partly due to waiting its turn in the CPU queue.

It is one of the cases where you can only get the true picture by looking at the O/S statistics at the same time. In this case, CPU usage, and run queue length. If these are both high (97+% and >4 per CPU say) then oracle is probably being a little misleading.

On the other hand, it doesn't really matter - you have been alerted that Oracle is performing slowly because it cannot get the desired throughput your next step (or 2) will tell you where the problem lies. --

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Vsevolod Afanassiev wrote in message
<7riar2$65m3_at_xlprod01.westpac.com.au>...
>Hi,
>I found that the sum of statistics "CPU used by this session" (V$SESSTAT)
>and sum of all waits from V$SESSION_EVENT (for a given session)
>are equal to the "clock on the wall" time from this session
>(if the session is at least a few minutes long). This makes sense: server
>process is either consuming CPU cycles or waiting on something like
>IO or message from client program.
>
>Clock on the wall time = CPU time + sum(Time_Waited).
>
>Lets assume that I am running something like SELECT count(*) FROM ... WHERE
>script
>in SQL*Plus so there will be no enqueue waits (no locks), no "SQL*Net
>Message from client" waits, etc,
>and the only type of waits will be IO waits ("db file sequential/scattered
>read').
>
>Now, if I run the same job under heavy load (CPU-bounded system) Clock on
>the wall time
>will definitely increase, and CPU time will be the same.
>So, the IO waits will increase. Isn't it misleading?
>
>In other words, how to separate the "true IO" waits from "true CPU waits"?
>Does this question make sense?
>
>Thanks,
>Sev
>
>
Received on Mon Sep 13 1999 - 04:06:35 CDT