RE: Db file scattered reads vs. direct path reads

  • From: Josh Collier <Josh.Collier@xxxxxxxxxxxx>
  • To: Finn Jorgensen <finn.oracledba@xxxxxxxxx>
  • Date: Fri, 22 Feb 2008 12:48:20 -0800

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@xxxxxxxxx]
Sent: Friday, February 22, 2008 7:01 AM
To: Josh Collier
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxx<mailto:Josh.Collier@xxxxxxxxxxxx>> 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<http://10.2.0.1/>
Solaris 10






Other related posts: