Re: single session I/O bandwidth
Date: Wed, 13 Mar 2013 15:17:45 +0200
Message-ID: <CAMHX9JJf_RO34ybGt7X_h6c6Ac0jU2P3S=Uznv+-8_1JUEDQew_at_mail.gmail.com>
what's the wait profile for this single session? 90% on CPU or 90% waiting? (run snapper :)
if it's a buffered scan (db file scattered reads) then you will do an IO operation, wait, process on CPU, do another IO operation, wait again etc... so you'll be wasting a lot of time on waiting for the synchronous IO operations to complete. So it's about the latency. Easily measurable.
If you see direct path reads, then Oracle can do prefetching via asynchronous IOs (if async IO is enabled of course). This way you won't wait for the IO latency almost at all if the underlying IO subsystem can deliver data fast enough. This is when you could see your process being 99% on CPU and 1% waiting on direct path read if the IO subsystem is fast enough and there's no other action (like sorting etc) going on in your exec plan.
Btw, if you actually are CPU-bound, then how much CPU you use per MB depends also on how many rows you actually process from these blocks and what you do with these rows. A COUNT(*) is much faster than SUM(SQRT(column)) or AVG(sysdate - order_date) for example. The difference of CPU cycles needed for a simple operation becomes significant when you process millions of rows...
-- *Tanel Poder* Enkitec (The Exadata Experts) Training <http://blog.tanelpoder.com/seminar/> | Troubleshooting<http://blog.tanelpoder.com/> | Exadata<http://www.amazon.com/Expert-Oracle-Exadata-Apress/dp/1430233923> | Voicee App <http://voic.ee/> On Wed, Mar 13, 2013 at 12:48 AM, Ls Cheng <exriscer_at_gmail.com> wrote:Received on Wed Mar 13 2013 - 14:17:45 CET
> Hi
> I did a few tests with different hardware today:
>
> 1. Sun M9000 (from 2008) and EMC VNX array, full table scan got around
> 70MB/sec
> 2. HP BL460 (from 2010) with Intel Xeon x5550 and 3PAR array, full table
> scan got 25MB/sec
> 3. IBM P750(from 2012) and IBM XIV array, full table scan got 180MB/sec
>
> So it does look like the CPU is the limiting factor because with modern
> CPU, in this case P7 a single thread was able to read 180MB/sec
>
> Thanks all
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-l