Re: Db file scattered reads vs. direct path reads
From: Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>
Date: Thu, 28 Feb 2008 23:12:28 +0400
Message-ID: <52a152eb0802281112x11421ec1ga124f2be5c498a1b@mail.gmail.com>
Date: Thu, 28 Feb 2008 23:12:28 +0400
Message-ID: <52a152eb0802281112x11421ec1ga124f2be5c498a1b@mail.gmail.com>
Full table scan does not use async io, available or not.
There are only a few operations in Oracle that use async IO. Some of them are:
- rman operations
- dbwriter operations
Full tables scans and index scans (any type) use normal read io. Those are my observations so far.
When you enable DIRECT reads, your IOs are aligned (there's another hidden parameter that's true by default). This can also be a factor not so much because of the aligned IOs, but this enables your SAN to better guess what you are doing, and pre-fetches (reads ahead) and thus speeds up your full table scans.
In first case you are gething 28.8 Mb/sec. Which quite low, and very
typical for inefficient sequencial IO.
In second case you are getting 105 Mb/sec which is not too bad. But
your 64 block read should take ~ 5ms. You are about 1000 times off in
this sample. Could it be a bad sample?
-- Christo Kutrovsky DBA Team Lead The Pythian Group - www.pythian.com I blog at http://www.pythian.com/blogs/ On Sat, Feb 23, 2008 at 2:09 AM, Josh Collier <Josh.Collier_at_banfield.net> wrote:Received on Thu Feb 28 2008 - 13:12:28 CST
> Vxfs and solaris 10 don't support kernalized async, but they do support a software workaround: each aio call spawns a series of threads that operate on a piece of the io call, these threads use pread(). One would need to buy quick i/o option for vxfs to get the kernalized aio.
> I verified that during the direct path read events that async i/o was operating as expected.
>
> Its during the traditional reads from "db file scattered reads" which correlate to a synchronous read in the truss ouput that performance is slower. I'll check on the fdsync. I have tried it with directio on and off, it makes very little difference.
>
> Thanks for your time!
>
> Josh C.
>
>
>
> -----Original Message-----
> From: dtseiler_at_gmail.com [mailto:dtseiler_at_gmail.com] On Behalf Of Don Seiler
> Sent: Friday, February 22, 2008 1:40 PM
> To: finn.oracledba_at_gmail.com
> Cc: Josh Collier; oracle-l_at_freelists.org
> Subject: Re: Db file scattered reads vs. direct path reads
>
> I do believe that I have async I/O on VxFS (no I don't have ODM).
> From an earlier blog post [0]:
>
> oracle:~/sr $ strace -c -p 12605
> Process 12605 attached - interrupt to quit Process 12605 detached
> % time seconds usecs/call calls errors syscall
> -- ---- ---- --- --- ------
> 80.66 1.142811 298 3831 io_getevents
> 7.26 0.102820 51 2004 io_submit
>
> Don.
>
> [0] http://seilerwerks.wordpress.com/2007/12/19/a-sub-prime-io-primer/
>
>
> On Fri, Feb 22, 2008 at 3:16 PM, Finn Jorgensen <finn.oracledba_at_gmail.com> wrote:
> > As I understand it vxfs does not support async IO. When you say
> > "asynch reads on the new system", I'm thinking you mean direct reads?
> >
> > When not using directio on vxfs and you look at your truss output are
> > there a lot of "fdsync" calls, which takes a long time (like 0.01
> > seconds per call)? If so, that's your culprit. Going to directio can
> > eliminate these, but there's a level of patching on both Solaris 10
> > and veritas together with a kernel setting that pretty much eliminates
> > the need for that setup as the fdsync calls are made so fast they can
> > be ignored. Have you SA's check into paching solaris 10 and veritas to eliminate the slow fdsync calls.
> >
> > Finn
> >
> >
> >
> >
> > On 2/22/08, Josh Collier <Josh.Collier_at_banfield.net> wrote:
> > >
> > >
> > > the old system is on ufs, the new is vxfs.
> > >
> > > I've narrowed it down to the difference in synchronous disk reads.
> > > the
> > async reads on the new system are much faster. the synchronous disk
> > reads are slower.
> > >
> > >
> > >
> > > ________________________________
> > From: Finn Jorgensen [mailto:finn.oracledba_at_gmail.com]
> > > Sent: Friday, February 22, 2008 7:01 AM
> > > To: Josh Collier
> > > Cc: oracle-l_at_freelists.org
> > > Subject: Re: Db file scattered reads vs. direct path reads
> > >
> > >
> > >
> > > What type of filesystem are you on? What differences are there
> > > between the
> > old system and the new apart from the storage array?
> > >
> > > Finn
> > >
> > >
> > > On 2/20/08, Josh Collier <Josh.Collier_at_banfield.net> wrote:
> > > >
> > > >
> > > > Hi,
> > > >
> > > > I've got a conundrum. We've got a new disk system. When I full
> > > > scan a
> > table with the nocache hint the trace files record lots of reads like
> > this
> > > >
> > > > WAIT #1: nam='db file scattered read' ela= 38471 file#=777
> > > > block#=238789
> > blocks=64 obj#=2805234 tim=359927798919
> > > >
> > > > And the query takes 11 minutes for a 19gb table, much slower than
> > > > our
> > old system
> > > >
> > > > When I enforce direct path reads with the following parameter
> > > > :alter session set "_serial_direct_read" = true
> > > >
> > > > The trace files record lots of reads like this
> > > >
> > > > WAIT #1: nam='direct path read' ela= 5 file number=777 first
> > > > dba=238789
> > block cnt=64 obj#=2805234 tim=343398354853
> > > >
> > > > And the query returns in 3 minutes
> > > >
> > > > What would cause the scattered reads to be so much slower than the
> > direct path reads?
> > > >
> > > > I've enabled direct io on the db and verified it via truss.
> > > >
> > > > Version is 10.2.0.1
> > > > Solaris 10
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
>
> --
> Don Seiler
> http://seilerwerks.wordpress.com
> ultimate: http://www.mufc.us
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- Christo Kutrovsky DBA Team Lead The Pythian Group - www.pythian.com I blog at http://www.pythian.com/blogs/ -- http://www.freelists.org/webpage/oracle-l