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, Jeremy 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 > > http://fritshoogland.wordpress.com > 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 (10.2.0.4) 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 >> > > > > -- > http://www.ardentperf.com > +1 312-725-9249 > > Jeremy Schneider > Chicago > > > -- http://www.ardentperf.com +1 312-725-9249 Jeremy Schneider Chicago