Re: db file scattered read and readv() system call

From: Greg Rahn <greg_at_structureddata.org>
Date: Fri, 31 Jul 2009 14:06:16 -0700
Message-ID: <a9c093440907311406w4b9705e4s9b171c733c92b2e7_at_mail.gmail.com>

It depends on what platform you are on and what your filesystem is as well as your db settings (async/direct I/O etc)

For example on Linux considering the following from a dbf on a ext3 filesystem:

*with filesystemio_options=none you will see pread(2) calls with strace* pread(11, "\6\242\0\0\21\263_at_\3\367\205\36\0\0\0\2\4\226{\0\0\1\0\0\0\n,\1\0007\205\36\0"..., 57344, 375529472) = 57344
pread(11, "\6\242\0\0H\263_at_\0036\206\36\0\0\0\2\4\210\5\0\0\1\0\0\0\n,\1\0007\205\36\0"..., 65536, 375980032) = 65536
pread(11, "\6\242\0\0\21\264_at_\3;\206\36\0\0\0\2\4\262\214\0\0\1\0\0\0\n,\1\0007\205\36\0"..., 57344, 377626624) = 57344

*with filesystemio_options=setall you will see io_submit(2) and

io_getevents(2) with strace*
io_submit(47270782558208, 1, {{0x2afe15f30fd0, 0, 0, 0, 13}}) = 1
io_getevents(47270782558208, 1, 128, {{0x2afe15f30fd0, 0x2afe15f30fd0,

1032192, 0}}, {600, 0}) = 1

io_submit(47270782558208, 1, {{0x2afe15f30a28, 0, 0, 0, 17}}) = 1
io_submit(47270782558208, 1, {{0x2afe15f30fd0, 0, 0, 0, 18}}) = 1
io_getevents(47270782558208, 1, 128, {{0x2afe15f30fd0, 0x2afe15f30fd0,

1032192, 0}}, {600, 0}) = 1

Simple tools like iostat should be able to be used to collect data about the LUNs in question. I would not necessarily be looking for bottlenecks, just differences, especially service times. I would also advise you to take the Oracle database out of the picture for the initial triage. Use a simple tool like dd(1) or Oracle ORION (
http://www.oracle.com/technology/software/tech/orion/index.html) to just create nondestructive read I/O and use iostat to capture the LUN stats.

On Fri, Jul 31, 2009 at 1:00 PM, Dba DBA<oracledbaquestions_at_gmail.com> wrote:
> Jeff Holt wrote a paper that is on hotsos where he stats that Oracle does
a
> readv() system call. We are having physical IO issues where in one system
on
> the same hardware response time is several times faster than on another
for
> similiar volumes. When we look at the disk activity on SAN we do not see
any
> bottlenecks.
>
> Does anyone have any information about happens when Oracle issues a
readv()
> call to what happens when it gets down to the SAN layer? Is there a paper
> someone would recommend that is readable to a now hardware engineer?
>
> Also, do you know any tools to trace an oracle db file scattered read ->
> readv() -> san while I am running a test? Similiar to a 10046 trace but
take
> it down to the SAN layer?

-- 
Regards,
Greg Rahn
http://structureddata.org

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 31 2009 - 16:06:16 CDT