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