Re: multiblock vs direct path read on 10gR2 aix (related: iostat/awr stat diff)

  • From: Jeremy Schneider <jeremy.schneider@xxxxxxxxxxxxxx>
  • To: Frits Hoogland <frits.hoogland@xxxxxxxxx>
  • Date: Thu, 4 Aug 2011 15:33:33 -0500

Sorry, I don't mean that it switches mid-query...  I mean that queries using
multiblock reads get a certain throughput pretty consistently, and queries
with direct path read get a different throughput pretty consistently.

I was just trying to find the fastest way to get certain rows out of a
table.  Three basic choices... (1) index, (2) FTS w/o parallel, (3) FTS
w/parallel.  I traced everything, and plotted data volume and throughput for
each case.  We're using 16K blocks and the MBRC was 32 (configured and
effective).  I used hints to get different plans; queries were on the same
[large] objects and I alternated enough to convince myself that storage
cache wasn't interfering.

- For the index, volume was small but throughput was a little over 1 MB/s.
Average of "sequential read" waits in 10046 tracefile was around 10ms - not
good, I know.  Of course we're getting 16K per read operation here.
- For FTS w/o parallel, volume was large and throughput was about 32 MB/s.
About what I would expect; average "scattered read" wait was about 15ms but
now of course we're reading 512K per operation.
- For FTS w/parallel, volume was large and throughput surprisingly shot up
to over 100 MB/s - per parallel slave!!  The average for "direct path" read
events in the tracefile was about 3ms!  This was with parallel 4.  I
experimented with increasing the number of threads, but it seemed that I
always got approximately the same total throughput.

I had originally thought that the "scattered read" wait event only included
I/O time.  But now I'm wondering if it includes some SGA maintenance
operations as well - what else could explain such a large discrepancy in
timings from the trace?  Any serious latch waits would show up as separate
waits, wouldn't they?

Most of this testing was done with a 1.1TB partitioned table, with several
partitions over 100MB in size.  The original monster business query ran in 6
hours, after I removed all hints and added “full() parallel(4)” for the two
large tables, the query dropped to 30 minutes.  According to the profiles:
- the old plan spent 5.5 hrs in sequential read [single-block/index-based]
to get 55GB of data (2-3 MB/s)
- the new plan spent 14 minutes in direct path read to get 400GB of data
(500 MB/s)

Hope this clears it up a bit,

On Thu, Aug 4, 2011 at 1:49 PM, Frits Hoogland <frits.hoogland@xxxxxxxxx>wrote:

> what do you mean with 'as soon as a query switches from multiblock to
> direct path'?
> does this mean a table is read with multiblock reads, and this data is put
> in a temporary tablespace, and read?
> or are you referring to multi block reads done via direct path? AFAIK, this
> is only done starting from version 11
> Frits Hoogland
> mailto: frits.hoogland@xxxxxxxxx
> cell: +31-6-53569942
> On Aug 4, 2011, at 4:43 PM, Jeremy Schneider wrote:
> Interesting.  I have a different question related to the same issue of I/O
> on 10gR2 AIX. I wonder if anyone else on this platform can tell me whether
> you've seen anything similar.
> I have observed about a 10x difference in throughput for direct-path reads
> vs multi-block reads of the same size.  In other words, as soon as a query
> switches from multiblock to direct path, it suddenly does I/O literally 10
> times faster.  I expect direct path to be faster (bypassing buffer cache
> overhead and all), but this seems a bit extreme!  I'm curious about others'
> experiences with direct path reads - has anyone else seen this big of a
> speed difference between direct path and multiblock?
> At one point, full tablescans were pulling table data at about 16MB/s with
> direct path and about 180MB/s with direct path.  I haven't started digging
> yet, but does anyone know if these two methods use the same system I/O
> call?  If so, that might indicate something in the database accounting for
> the difference, otherwise it could be a combination of DB and OS.  At the
> storage tier, these tests were reading the exact same blocks.  I alternated
> between them several times to make sure I wasn't just seeing cache
> interference.
> Also, I was calculating these throughput number from the wait events... and
> I didn't see any wait events related to contention.  All the wait time was
> "db scattered read" or "direct path read" - both reading 32 x 16k blocks per
> wait.
> -Jeremy
> On Wed, Aug 3, 2011 at 9:59 AM, joshuasingham <joshuasingham@xxxxxxxxx>wrote:
>> Hi ,
>> I been looking at some ( on AIX 64 bit Awr report File IO Stats
>> and can see that the Av Rd(ms) for some of the files are consistently above
>> 50 to 60 ms over a 15min interval but when I reported this storage guy he
>> mention and sent me the Iostat to prove that the service time in the same
>> timeline is average of 20ms for the disk involve . What can cause the
>> difference between the AWR and iostat readings
>> thanks
> --
> +1 312-725-9249
> Jeremy Schneider
> Chicago

+1 312-725-9249

Jeremy Schneider

Other related posts: