Re: Surprising parameters for direct path read in 100046 trace

In the merge join, is your join condition an equality, or an range-based join such as:
  t2.col2 between t1.col1 - 1 and t1.col1 + 1

If so, then you may be seeing the physical effects of Oracle having to reposition in the t1 sorted data for repeated appearances of the same t2 value.

For example, the t2 data when sorted reads 1,1,1,2,2,3,3,3,3, .. So (using the above "between" join), Oracle would have to read

  t2.col2 = 1       scan the sort dump from 0 to 2
  t2.col2 = 1       scan the sort dump from 0 to 2
  t2.col2 = 1       scan the sort dump from 0 to 2
  t2.col2 = 2       scan the sort dump from 1 to 3
  t2.col2 = 2       scan the sort dump from 1 to 3
  t2.col2 = 3       scan the sort dump from 2 to 4
  t2.col2 = 3       scan the sort dump from 2 to 4
  t2.col2 = 3       scan the sort dump from 2 to 4
  t2.col2 = 3       scan the sort dump from 2 to 4

For some reason, your sort_multiblock_read_count (which is probably the value that controls the multiblock read of sorted data segments during merge passes***) is one - so every time you re-set the range scan on the second data set, you have to re-read the block.

This is just guesswork, by the way. I know that the reset is needed for non-equality joins but suspect the correctness of my comment about the multiblock read of "merge passes" (marked ***) above as my previous experience of this parameter says that it applies to the reads required for merging within a single single sort that goes onepass to multiplass. I have not checked if it is also relevant in any way to merge joins.

Regards
Jonathan Lewis  

> ------------------------------
>
> Date: Tue, 07 Mar 2006 10:14:35 +0000
> From: Chris Dunscombe <chris_at_thedunscombes.f2s.com>
> Subject: Surprising parameters for direct path read in 100046 trace
>
> Hi,
>
> I've traced a long running piece of SQL and observed
> the following in the trace file (only a small sample):
>
> WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1
> WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641040 p3=1
> WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1
> WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641040 p3=1
> WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1
> WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641040 p3=1
> WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641041 p3=1
> WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641040 p3=1
> WAIT #3: nam='direct path read' ela= 5 p1=202 p2=641041 p3=1
> WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641040 p3=1
> WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1
> WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641040 p3=1
> WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1
> WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641040 p3=1
> WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1
> WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641040 p3=1
> WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1
> WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641040 p3=1
> WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641041 p3=1
> WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641040 p3=1
> WAIT #3: nam='direct path read' ela= 4 p1=202 p2=641041 p3=1
> WAIT #3: nam='direct path read' ela= 3 p1=202 p2=641040 p3=1
> ..................
>

--
http://www.freelists.org/webpage/oracle-l