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@xxxxxxxxxxxxxxxxxxxxx>
> 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

Other related posts: