Re: three reads to get 64 blocks with dfmrc=32
- From: Tim Gorman <tim@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 16 May 2005 20:30:24 -0600
Jaromir,
You've certainly noticed the difference in wait-event names: "db file
scattered read" versus "direct path read"?
The first wait-event indicate an operation that is reading blocks from disk
into buffers in the Buffer Cache in the SGA, the second indicates reading
blocks from disk into "private" process memory in the PGA.
I'm just speculating, but I would guess that there is only room in those
read buffers in each P00x process's PGA for sixty-one 16-Kb buffers (i.e.
976Kb), perhaps? Since the operation is a COUNT(*), we know that all that
data is not being passed back to the "query coordinator" through the TQ$
"tables" in the Large Pool, just the row counts.
It might be interesting to adjust DFMRC to several different values (i.e.
16, 12, 8, 4) and see if the observed pattern of successive "direct path
reads" continue to add up to "61" before each "PX Deq: Execution Msg" which
presumably indicates the posting of an intermediate row count to the TQ$
queues.
If this pattern of behavior does continue to show up, then next I would
consider playing with PGA_AGGREGATE_TARGET (if you have
WORKAREA_SIZE_POLICY=AUTO) to see if increasing or decreasing that value has
an affect on the magic number "61". Of course, activity by other sessions
in the instance could have an impact on this testing, so hopefully you have
a fairly quiet environment to test in.
Sorry, no answers. Just some ideas...
-Tim
on 5/16/05 2:57 PM, jaromir nemec at jaromir@xxxxxxxxxxxx wrote:
> Hi,
>
> Linux, Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
>
> I have a table with 24 extents each has 64 blocks (16K), the dfmrc is set to
> 32.
>
> If I read the table serial, each extend is red with two multiblocks reads of
> 32 and 31 blocks - see extract of tkprof 10046 level 8
>
> select /*+ PARALLEL(x,1) */ count(*) from x;
>
> WAIT #2: nam='db file scattered read' ela= 10281 p1=8 p2=5064 p3=32
> WAIT #2: nam='db file scattered read' ela= 9469 p1=8 p2=5096 p3=29
> WAIT #2: nam='db file scattered read' ela= 44145 p1=8 p2=71174 p3=32
> WAIT #2: nam='db file scattered read' ela= 10020 p1=8 p2=71206 p3=31
> WAIT #2: nam='db file scattered read' ela= 10228 p1=8 p2=71238 p3=32
> WAIT #2: nam='db file scattered read' ela= 19536 p1=8 p2=71270 p3=31
> WAIT #2: nam='db file scattered read' ela= 14996 p1=8 p2=71302 p3=32
>
> If I switch to PX there are 3 read per extend with 32, 29 and 2 block - see
> the extracts of the same event for the two PX slaves:
>
> select /*+ PARALLEL(x,2) */ count(*) from x;
>
> P002
>
> WAIT #1: nam='direct path read' ela= 53 p1=8 p2=5064 p3=32
> WAIT #1: nam='direct path read' ela= 24 p1=8 p2=5096 p3=29
> WAIT #1: nam='PX Deq: Execution Msg' ela= 529 p1=268566527 p2=1 p3=0
> WAIT #1: nam='direct path read' ela= 26 p1=8 p2=71235 p3=2
> WAIT #1: nam='PX Deq: Execution Msg' ela= 3270 p1=268566527 p2=1 p3=0
> WAIT #1: nam='direct path read' ela= 52 p1=8 p2=71238 p3=32
> WAIT #1: nam='direct path read' ela= 12 p1=8 p2=71270 p3=29
> WAIT #1: nam='PX Deq: Execution Msg' ela= 1916 p1=268566527 p2=1 p3=0
> WAIT #1: nam='direct path read' ela= 27 p1=8 p2=71299 p3=2
> WAIT #1: nam='PX Deq: Execution Msg' ela= 7530 p1=268566527 p2=1 p3=0
> WAIT #1: nam='direct path read' ela= 50 p1=8 p2=71302 p3=32
> WAIT #1: nam='direct path read' ela= 14 p1=8 p2=71334 p3=29
>
> P001
>
> WAIT #1: nam='direct path read' ela= 53 p1=8 p2=71174 p3=32
> WAIT #1: nam='direct path read' ela= 7 p1=8 p2=71206 p3=29
> WAIT #1: nam='PX Deq: Execution Msg' ela= 8318 p1=268566527 p2=1 p3=0
> WAIT #1: nam='direct path read' ela= 29 p1=8 p2=71363 p3=2
> WAIT #1: nam='PX Deq: Execution Msg' ela= 43306 p1=268566527 p2=1 p3=0
> WAIT #1: nam='direct path read' ela= 32 p1=8 p2=71427 p3=2
> WAIT #1: nam='PX Deq: Execution Msg' ela= 9092 p1=268566527 p2=1 p3=0
> WAIT #1: nam='direct path read' ela= 27 p1=8 p2=71491 p3=2
> WAIT #1: nam='PX Deq: Execution Msg' ela= 45026 p1=268566527 p2=1 p3=0
> WAIT #1: nam='direct path read' ela= 31 p1=8 p2=71555 p3=2
> WAIT #1: nam='PX Deq: Execution Msg' ela= 9513 p1=268566527 p2=1 p3=0
> WAIT #1: nam='direct path read' ela= 27 p1=8 p2=71619 p3=2
> WAIT #1: nam='PX Deq: Execution Msg' ela= 44602 p1=268566527 p2=1 p3=0
> WAIT #1: nam='direct path read' ela= 31 p1=8 p2=71683 p3=2
--
//www.freelists.org/webpage/oracle-l
Other related posts:
